Re: Better SQL performance with UNION?

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

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