Thanks. But I don't understand ... In the second query I've asked for a LEFT JOIN both times. What's the difference between a LEFT JOIN and a LEFT OUTER JOIN? And why when I ask it twice for LEFT JOINs does it do a left join once, and a left outer join once?
While I think I understand your comment about "a left outer join must capture rows that may not be in the right table" I don't see how any such rows could exist - the fact that we're matching @ID to @ID guarantees that, surely? Unfortunately, I don't think creating an index is an option Sorry for troubling you for more detail, but my SQL isn't superb, and I don't see why it should treat the two joins any differently - they're both joining tables on a "key matches key" basis. Cheers, Wol -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod Sent: 18 July 2008 02:00 To: [email protected] Subject: Re: [U2] Strange SQL query optimisation ... They are not the same query. The second one specifies LEFT OUTER JOIN. The first does not. A left outer join must capture rows that may not be in the right table. An index on @ID in each of the tables may help. > ----- Original Message ----- > From: "Anthony Youngman" <[EMAIL PROTECTED]> > To: "[email protected]" <[email protected]> > Subject: [U2] Strange SQL query optimisation ... > Date: Thu, 17 Jul 2008 14:34:32 +0100 > > > 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/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
