RE: Better SQL performance with UNION?

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

Cool idea. "Columnizes" all the pertinent info, you might say, eh?

Try setting up indexes as I suggested in the earlier post - it might help
in the record selection in each union. There's nothing that'll help the
sort - that's where a multiformat logical has the advantage - the access
path is already established. I don't think the optimizer is smart enough to
handle this well enough, even with the optimize clause. I mean, the whole
result set has to be built before the sort takes place.

A couple other weird things to try -

1. Put an OPTIMIZE clause on each component SELECT statement - I could not
find that this is restricted.
    - or -
2. Considering that, for sorting, the entire result set needs to be set up
(unless the previous idea helps), maybe optimize for a large result set -
it might force a more efficient access method.

3. Try changing the QRYDEGREE attribute of the job, to either *OPTIMIZE or
*MAX (use CHGQRYA command)

Good luck

Vern

At 10:31 PM 9/20/02 -0400, you wrote:
> 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