RE: Better SQL performance with UNION?

Uh, it was > midnight and I was up at 0400 and I did have to fight my way
through two airports.  Yes, 500,000 rows is more like it.

I'm improving a lookup routine that allows searching on customer name,
address, city, or ZIP without requiring the user to specify a specific
"type" (column, and that IS what I mean) of search.  A user entering "4920"
will see "4920 S West Shore Blvd" first and then "49201" (the ZIP code for
Jackson, MI).  It sounds funky but it works very well.

In another application, I have a logical with 28 formats built over a very
busy and reasonable large (2,000,000 records) master file.  DDS works great
but I want to add filters (date, type, etc.).  Yes, I can skip the records
with a couple of lines of code but I think performance will be pretty bad;
I'm hoping to find some SQL magic.

Thanks,
Reeve

-----Original Message-----
From: midrange-l-admin@midrange.com [mailto:midrange-l-admin@midrange.com]On
Behalf Of Vernon Hamberg
Sent: Friday, September 20, 2002 12:49 AM
To: midrange-l@midrange.com
Subject: Re: Better SQL performance with UNION?

Reeve

What is the end result you're looking for? To list things in "address"
order by customer? The ORDER BY you have will not do this, I think - it'll
result in the various address component texts in order, followed by the
customer numbers(?) in order within each element's text - or something like
that.

Could you get the same result (report output?) by retrieving the values in
each row, using a where clause with ANDs between each component? Let the
app format the output.

But you may have a purpose I don't see yet.

About the UNONs, as you know, each is basically a separate query. The WHERE
clauses can be supported by individual indexes. 2 options come to mind -
indexes with both cmcust and the selection field in them. This could result
in an index-only access plan, since the data is all in the index. The
optimizer might not use this, though, because of the literl, or because of
the UNION- I don't remember.

TThe other idea is to create EVIs (encoded vector indexes). These are very
fast for record selection.

Be sure to use parameter markers (host variables) - then the access plan is
not reoptimized (as often, anyway) with different values passed in.

Did you really mean 500,000 columns? Maybe rows? I hope. Maximum row
_length_ is 32,700 such. And you can have that many 1-character columns, at
most.

At 12:05 AM 9/20/02 -0400, you wrote:
>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.