January 11, 2013 at 3:19 am
I have a sceario where I have 2 tables
Table 1
APPLID
PRFLID
...
Table2
PRFLID
fld1
fld2
fld3
I wnat to write and SP
Create Procedure GetTable2InfoFromAPPL (@ApplID int)
I am thinking on 2 wyas of implementing the code
Way1:
Declare @PRFLID int
Select @PRFLID = PRFLID from Table1 where APPLID = @ApplID
Select * from Table2 where PRFLID = @PRFLID
Way2:
Select Table2.* from Table2
INNER JOIN Table1 on Table2.PRFLID = Table1.PRFLID
where Table1.APPLID = @APPLID
Which of the 2 ways is much better and will give better performance?
January 11, 2013 at 3:54 am
As a general rule Way 2 would most likely perform better and is also easier for people to understand down the line.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 11, 2013 at 4:02 am
I agree with Jason. But don't take our word for it. Try them both, and compare the execution plans.
John
January 11, 2013 at 4:40 am
It really does depend on the indexes and statistics on the table, but most of the time, option 2 is the right way to go. And when it isn't the right way to go, I'd reexamine the indexes and statistics in the system to figure out why it's not working correctly. This is especially true in the event that you get more than one record from the first table. That would make option 1 not work at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply