Re: SQL CTE of a UNION kills performance

On 8/28/2018 1:26 PM, Charles Wilt wrote:

I suspect that the DB is trying to materialize all the rows before
returning with the CTE...

Whereas w/o the CTE, you're seeing the results of *FIRSTIO as an
optimization goal...

Makes sense.

Try changing your goal to *ALLIO and see if the query becomes as slow as
the CTE.

That'd be QAQQINI, I guess, but it's a good diagnostic.

Using your hint about *FIRSTIO, I stuffed the CTE into a separate VIEW,
pulled it out of the problematic VIEW and boom - it's much, much faster.

Thank you for your insight!
--buck

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD