Which is the most efficient, or does it matter? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Which is the most efficient, or does it matter?

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2010-12-14 : 11:00:07

I have two tables, Clients and Signatures as below:

Clients
----------
SN char(6)
ID int (PK)
Signature int (FK to Signature.ID)

Signatures
----------
ID int (PK)
Name nvarchar(50)

Using stored procedures, so I suppose this means the query is 'pre-compiled', so then which is more effieient or does it matter at all?

If this were not in a stored procedure, would it matter then?

Query #1:

SELECT s.Name
FROM Clients c
LEFT JOIN Signatures s ON s.ID = c.Signature
WHERE c.SN=@SN AND c.ID=@ID

Query #2:

SELECT s.Name FROM Signatures s WHERE s.ID IN
(SELECT c.Signature FROM Clients c WHERE c.SN=@SN AND c.ID=@ID)

kpg

Kristen
Test

22859 Posts

Posted - 2010-12-14 : 11:55:40

Stored Procedures may be preferred for a variety of reasons (permissions, granular code, etc.) but if a query is Paremeterised its query plan will (usually) be cached

If you query

SELECT Col1, Col2, ... FROM MyTable WHERE Col3 = 'Foo'
and then
SELECT Col1, Col2, ... FROM MyTable WHERE Col3 = 'Bar'

then the likihood is that SQL will make a fresh query plan for the second query as it is not identical to the first.

OTOH if you query
SELECT Col1, Col2, ... FROM MyTable WHERE Col3 = @MyParameter

then SQL will reuse the cached query plan each time as there will be no change each time (it can be important that the query is identical, even including case sensitivity and spaces etc.)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-14 : 13:09:11

Hard to say since you haven't told us anything about your data base. How many rows are in each table? How many rows are affected by the query you posted? Do you have any index on your tables? etc, etc..

The way to know for sure is to run each query and see how it performs. You can use the actual excution plan and/or other SET STATISTICS options (IO, Profile, Time, Xml) to put some numbers to the perfomance of each query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources