Simple way to speed up the query

We recently had an issue with a stored procedure that was doing a join on multiple tables. It was running extremely slow. Here’s an example of what it did.

Tables:
tblOne (TestId, SomeText)
tblTwo (TestId, OtherText)

SELECT:
DECLARE @TestId
select @TestId = 1

select a.TestId, b.OtherTxt
from tblOne a, tblTwo b
where a.TestId = @TestId
and a.TestId = b.TestId

Running the stored procedure like this passing in a TestId will work but it will also not filter the rows it selects on tblTwo just because of the join statement (a.TestId = b.TestId). You have to add "and b.TestId = @TestId". This will limit the number of rows scanned in tblTwo and speed up your stored procedure.

This entry was posted on September 26, 2006, 2:01 pm and is filed under Sql Server. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.