This particular machine is running v5r4 and then I get this to process quickly, then I want to change the SQL to an UPDATE. I don't think that v5r4 can do an SQL update on a join.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Friday, August 14, 2015 12:09 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: AW: SQL: how to speed up join tiny file to giant file

First I didn't follow the whole thread ...
The following SQL ran for an hour - if I could force it to read the
fileFIX first, it should only take seconds.

Any ideas?

Thanks in advance!

select * from filePROD
where exists
( select docid
from fileFIX
where filePROD.docid = fileFIX.docid

What happens if you rewrite your query as follows:

Select fileProd.*
From Filefix join fileProd on fileProd.DocId = fileFix. DocId

... and double check that there are Logical files (or even better SQL
indexes or Key Constraints) on both tables with the docid as key.
... also only select the columns you really need and not all of them.

If could be that the query optimizer is forced to handle the tables in that
way: FILEPROD first, read it and join the second table whatever is found.
With the join in the from clause the optimizer can change the sequence.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

If the query ran that long, seems FILEPROD is getting read sequentially.?
Again, the debug messages would assist. Do the data type and lengths
actually match across the columns and the access paths?

Not that there should be any negative consequence, but given the subquery
requires no data, selecting a literal versus a column is best; the optimizer
should understand that, and implement appropriately, but no reason to give
any false requirements.

A clause asking to OPTIMIZE FOR 10 ROWS should encourage the use of an
index over sequential access. As well might an ORDER BY filePROD.docid such
that the matching Sort Sequence between the request and the access path
could recognize the indexed data selected would already be in the required
order for the result set.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].