One of my colleagues brought a speed problem to me - he has two queries which
produce the same result but one (the "proper" "modern" SQL) runs slowly, and
the other (deprecated SQL) runs much faster. He was running them in a web
engine, so I ran them at TCL with the EXPLAIN keyword and got an odd result.
Here are the two queries:
02 SELECT * FROM REGISTER, CLAIMS_CLAIMCOSTS,
CLAIMS_COVERCODEFROMPOLICY WHERE R
EGISTER.CLAIM.NUMBER=CLAIMS_CLAIMCOSTS.CLAIM.NUMBER AND
REGISTER.CLAIM.NUMBER=CL
AIMS_COVERCODEFROMPOLICY.CLAIM.NUMBER AND REGISTER.CLIENT.REF='W1323' AND
CLAIMS
_CLAIMCOSTS.CURRENCY.CLAIM.PAID.IN <>'' EXPLAIN
01 SELECT * FROM REGISTER LEFT JOIN CLAIMS_CLAIMCOSTS ON
REGISTER.CLAIM.NUMBER=C
LAIMS_CLAIMCOSTS.CLAIM.NUMBER LEFT JOIN CLAIMS_COVERCODEFROMPOLICY ON
REGISTER.C
LAIM.NUMBER=CLAIMS_COVERCODEFROMPOLICY.CLAIM.NUMBER WHERE
REGISTER.CLIENT.REF='W
1323' AND CLAIMS_CLAIMCOSTS.CURRENCY.CLAIM.PAID.IN <>'' EXPLAIN;
Note that REGISTER and CLAIMS are the same UV file (don't ask why) and
CLAIM.NUMBER is the @ID. When the first query is run, the optimiser says it's
doing a hashed join on the primary key for both subtables. When the second
query is run, it does an outer hashed join for the first subtable, but an
"outer cartesian join using scan of secondary file" on the second. Why? It's
the same query!
Any ideas how to make the modern syntax do the "right thing" and not run like
a snail on tranquilisers?
Cheers,
Wol
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
