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
Brown)
"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.
midrange.com
