RE: Better SQL performance with UNION?

["Tyler, Matt" <mattt@xxxxxxxxxxxxxx>]

Reeve,
        The main question is what is the result supposed to look like?  To
me I would think you are trying to get all names grouped first then all
address 1's, etc.  Is this correct?    And do the customer names need to be
ordered?  Why do you select (WHERE clause) with different columns?  Isn't
the data in the union supposed to be common to a customer number?  Are you
looking for holes in the data?

I'm not trying to be picky about your query, just trying to understand.

Thank you,
Matt Tyler
Mattt@wincofoods.com

-----Original Message-----
From: Reeve Fritchman [mailto:reeve@ltl400.com]
Sent: Thursday, September 19, 2002 22:06
To: Midrange-tech-L@Midrange. Com
Subject: Better SQL performance with UNION?

This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I'm trying to figure out how to get better performance using UNION.  The
interactive application uses embedded SQL and does a lookup over multiple
columns in one table.  SQL creates a temporary file and appears to copy the
records into it.

The number of columns will vary from 3,000 to 500,000 (but I have other
applications with much larger record counts and the potential for many more
UNION's).

Here's what I've done so far with STRSQL (where "ABC" is some user-specified
search argument to be substituted in the embedded SQL):

SELECT cmcust,cmname,'Name' FROM arp001 WHERE cmname >= 'ABC'
UNION
SELECT cmcust,cmadr1,'Adr1' FROM arp001 WHERE cmadr1 >= 'ABC'
UNION
SELECT cmcust,cmadr2,'Adr2' FROM arp001 WHERE cmadr2 >= 'ABC'
UNION
SELECT cmcust,cmcity,'City' FROM arp001 WHERE cmcity >= 'ABC'
UNION
SELECT cmcust,cmzip, 'Zip'  FROM arp001 WHERE cmzip  >= 'ABC'
UNION
SELECT cmcust,cmabbr,'Abbr' FROM arp001 WHERE cmabbr >= 'ABC'
                                        and   cmabbr <> cmname
ORDER BY 2,cmcust
OPTIMIZE FOR 20 ROWS


In the real world, I'd use a multi-format logical and response time would be
sub-second.  SQL doesn't understand multi-format logicals and I'd like to do
something along the lines of CREATE INDEX to improve performance.

I'd be grateful for a suggestion or a reference...

Thanks,
Reeve

--

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.