Is this an effective and efficient SQL Query or is there a better way?
We have a passwords table which references a user table. Records never get deleted from the password table so if a user changes their password, a new entry with a more r开发者_如何学JAVAecent Created
date gets inserted.
The hash of the password is salted with various things, most importantly the created date of the actual record.
In a stored procedure, I'm retrieving variables so I can do a hash for comparison. I really just want to store the most recent password hash for a user along with the record's created date:
DECLARE @ExistingPassword as varchar(200)
DECLARE @LastChanged as DateTime
SELECT Top 1
@ExistingPassword = p.PasswordHash,
@LastChanged = p.Created,
FROM Password as p
WHERE p.UserId = @UserId
ORDER BY p.Created DESC
Is this a reasonably efficient way of getting the most recent password hash and its created date? Is there a better way to do it?
This is efficient, but I would make sure there is an index on:
userId and created
for performance reasons
I think that is the fastest way to do it. I ran the below code to test your way and the only other way I could think of and your way was a little faster. I would also like to mention in a table with 1,000,000 rows I was getting response times fast enough that SSMS was not even giving me a run time, just 00:00:00. I did see one thing that might help you. If you can, put a non-clustered index on the Created column and make it sort in Descending order. Putting it in Descending order will make big difference because it will stop you from doing a table scan for the newest Created DateTime. That really could make a HUGE difference.
--Put Rows in TestTable, 3 Rows ID, User_ID, CreatedTime
DECLARE @Count INT
SET @Count = 1
WHILE @Count <= 1000000
BEGIN
INSERT INTO TestTable
VALUES (@Count%3+1,DATEADD(HH,@Count,getdate()))
SET @Count = @Count + 1
END
--Select Your way
SELECT TOP 1
User_ID
FROM TestTable
WHERE User_ID = 3
ORDER BY CreatedTime DESC
--Select my way
SELECT
User_ID
FROM TestTable
WHERE User_ID = 3
and CreatedTime = (SELECT MAX(CreatedTime) FROM TestTable WHERE User_ID = 3)
By my personal experience, you should work with a period, like StartDate
and EndDate
. So, you can to use a WHERE getdate() BETWEEN StartDate and EndDate
to search for your current password.
Every time you add a record just with a created date for subsequent most update retrieval, baby jesus cries.
精彩评论