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.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: