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.
| Author |
Topic |
|
mbosch
Starting Member
6 Posts |
 Posted - 2008-02-01 : 10:44:54
|
| Let's say I have a table of users.Let's imagine there's two fields:username (PK),passwordNow I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to(1) SELECT * FROM [User] WHERE username = 'whatever' AND password='whatever'or(2) SELECT * FROM [User] WHERE username = 'whatever'and then in my code check that the record returned matched the password? |
|
|
shankarnraj
Starting Member
3 Posts |
Posted - 2008-02-01 : 12:17:44
|
| I personally recommend the first approach, because adding just one more filter will not affect the performance of the query. But when you have the check in your code that may have to consume some more cycles :) |
 |
|
|
shankarnraj
Starting Member
3 Posts |
Posted - 2008-02-01 : 12:18:43
|
| Hey one more point,in case if you are going with the first option then make sure you take care of SQL injection :) |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-02-03 : 18:00:53
|
| Describe the problem in the most concice way you can and let the tools available do their job. 1st is the way to go, 2nd is just crap. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-02-03 : 22:19:01
|
| Neither is better. It depends on your specs. Do you just want to say "username/password match not found" or do you want to say "the password you provided doesn't match the username" ? That requirement clearly dictates which technique to use, and that alone determines which is "best".- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
mbosch
Starting Member
6 Posts |
Posted - 2008-02-04 : 09:53:14
|
| Perhaps the better way to phrase is would be, from a pure DB optimization and speed perspective, which is faster?Also, what if we scale this out a little and add more fields...SELECT * FROM [USER]WHERE username='mike' and password='password' and IsActive = 1and IsDeleted = 0 and SubscriptionExpiration > GetDate() etc...Ultimately, the question is: Is it faster to query on the primary key field alone, or to include other fields?I'm not a "DB guy" so I don't understand the inner workings of statistics and precompling queries etc... Thanks everyone! |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-02-04 : 12:11:59
|
| If username is uniquely constrained then the execution plans are the same. If you are concerned about speed then the less info you push down the pipes the better, so name the columns you require data for rather than SELECT *.If you want to learn, read up on indexes. For the above topic this is the main point of reference. Note that the fact you are querying the primary key is actually irrelevant - it is the index that SQL Server uses to enforce the key that speeds up the query. |
|
|
|
|
|
|
|
|
faster query - select by one or multiple fields