Inner Join Efficiency

B

bizzydint

Well-known member
Joined
Apr 8, 2003
Messages
75
Ive got a SQL query with 2 inner joins, one of which involves a large table. Essentially its a search tool for info in our DB.

So the query is along the lines of:
SELECT stuff, things, wotsits
FROM tbl_One
INNER JOIN tbl_Two ON tbl_One.stuff_ID = tbl_Two.stuff_ID
INNER JOIN tbl_Three ON tbl_One.things_ID = tbl_Three.things_ID
WHERE
stuff_ID > 100 AND things_ID < 5000
AND
...some CONTAINS criteria...


(hope that makes sense)


Question is: are the "where" parts done before the joins? Or will the joins be involving the whole table(s) and then filtered down?

Reason is: the query is taking too long! But only when certain criteria are in place, which makes me think its got something to do with the "where" parts....


I know im probably being too vague (but Im ill!) so hope you can help ;-)
 
The joins you have shouldnt be causing any slow downs. Just be sure that you have foreign key constraints in place so the query optimizer can take advantage of them.

If you could tell us what search criterias are taking so long and which arent wed be able to help a little more. Im assuming the slow down is in your WHERE or your CONTAINS. The WHERE could be causing serious slow down if "stuff_id" and "things_id" are not indexed.

Other then that.. I cant really suggest anything unless more details are given. The SELECT query you pasted is so simple that it shouldnt be causing slow down unless youre dealing with 10 million or more records.

Youll have to post your table schemas along with a sample of a complete SELECT query if you want any real efficiency tips.
 
If you could tell us what search criterias are taking so long and which arent wed be able to help a little more. Im assuming the slow down is in your WHERE or your CONTAINS. The WHERE could be causing serious slow down if "stuff_id" and "things_id" are not indexed.
Click to expand...

Everythings indexed etc. It was just a random question out of curiosity really. Im sure theres something wrong in the SP cos it seems a bit odd that certain searches work and others dont, but I havent had the time to break it down myself yet. Im sure ill get back to you with some detail if i get stuck - but im pretty sure i can work it out ;-)

Just wanted to know if the JOIN takes place before the WHERE??

wyrd - are you stalking me?? ;-)
 
not sure if i should be scared now - but thanks for the answer.

Things going wrong atm so i havent had a chance to fix the SP in question. But Ill know where to come when I screw that one up...
 
Back
Top