RE: Better SQL performance with UNION?

["Reeve Fritchman" <reeve@xxxxxxxxxx>]

The finder file would be built by the trigger program as master records are
added/changed/deleted, and you have to worry about keeping the finder file
synchronized with commitment control.

-----Original Message-----
From: midrange-l-admin@midrange.com [mailto:midrange-l-admin@midrange.com]On
Behalf Of Vernon Hamberg
Sent: Saturday, September 21, 2002 10:30 AM
To: midrange-l@midrange.com
Subject: RE: Better SQL performance with UNION?

Reeve, the surprise for me is that the optimizer did not use index-only
access for all the SELECTs - seems that would help.

The bit about EVIs makes sense - EVIs over columns with high cardinality
could have as many entries to process as a regular index.

Trigger idea soulds neat, too. Index over the finder file gets you there
almost instantly, not after multiple seconds of building the temp result
file - I mean, do you build the finder file now or later?

Anyway, glad to have had a chance to test out some things.

Vern

At 08:26 AM 9/21/02 -0400, you wrote:
>Vern, thanks for taking a kick at the can; I'm not surprised at your
>results.  I tried CREATE INDEX on each of the columns and (with STRDBG) and
>the Optimizer indicated it used the index.  It's time for a real test, I
>guess...
>
>I thought about EVI's but I thought I read (in the "Query Performance and
>Optimization" manual?) they're better on columns with a relatively small
>number of values.
>
>In thinking about an alternative solution, I've come up using with a
trigger
>on the PF to write cross-reference entries to a finder file.  But it's so
>"System/36"-like it makes me gag.  It could be ITER...
>
>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.