How's this WHERE clause? Effect performance much? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How's this WHERE clause? Effect performance much?

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-08-09 : 13:03:00

Hi,

I am performing a simple SELECT statement, with the where clause being on a primary key.
Say my table looks like:

Article
- articleID
- websiteID

So my query right now looks like:

SELECT *
FROM Articles
WHERE articleID = @articleID

I am gettting the articleID from the URL: www.example.com/?articleID=2342

I don't want someone trying to modify the articleID and attempt to access someone elses article, so I check the row's websiteID on the server side.

If I just add the check to the WHERE clause, will it effect the queries performance?

i.e.
SELECT *
FROM Article
WHERE articleID = @articleID AND websiteID = @websiteID

Thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 13:11:34

Nope. if you had proper indexes on the columns it will perform better

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-09 : 14:10:25

And if you don't use SELECT *

Specify your columns and select only the ones you need.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-09 : 14:11:57

quote:
Originally posted by visakh16

If you had proper indexes on the columns it will perform better

Since the articleID is the pk, and hence there's max one row I doubt an index on WebsiteID will make that much difference.

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 14:50:48

Could be a difference between index seek and index scan?

E 12°55'05.25"
N 56°04'39.16"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-09 : 15:53:24

Shouldn't be. I would expect a seek on the pk (assume it's the cluster) then a filter on the websiteID. Since there's an equality match on the pk there's no reason for an index scan since only 1 row can be returned.

--
Gail Shaw
SQL Server MVP

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-08-09 : 17:08:50

Thanks for the replies.

1. I need all the columns, hence the *

So it looks like it will not make a difference since I am already referncing the PK

My query:

SELECT *
FROM Articles (NOLOCK)
WHERE articleID = @articleID AND websiteID = @websiteID

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-10 : 03:43:07

Even if you need all the columns, it is good practice to explicitly reference them. That way the proc won't break or do something unexpected when additional columns are added.

Oh, and don't use nolock unless you have a very good reason and you know exactly what it does. It's way overused and should not be put on every query without thinking.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources