October 20, 2009 at 9:32 am
So I’m having some issues optimizing a password retention procedure for an application that I am working on. The number of passwords stored in history is a variable setting for this app. When the administrator changes the setting, the procedure checks the PasswordHistory table and removes any “extra” passwords already stored. IE. if the setting was set to 4 and then it was changed to 3, for any user that had 4 passwords the oldest needed to be deleted. The current procedure does this by means of a whole crap load ‘o loops which take forever to run. I’d like to convert this to a set based operation to speed it up. This needs to work in SQL Server 2000/2k5 and 2k8.
Here’s some sample data.
Use TempDB --Do this sometplace safe...
--CREATE Test Data
CREATE TABLE #PasswordHistory(
[ID] [int] IDENTITY(1,1) Primary Key NOT NULL,
[UserName] [varchar](16) NOT NULL,
[Password] [varchar](20) NOT NULL,
[PasswordDate] [datetime] NOT NULL,
)
SET IDENTITY_INSERT [#PasswordHistory] ON
INSERT INTO #PasswordHistory ([ID], username, password, passwordDate)
SELECT 4957,'user1','u1pw1','2008-10-27 08:53:47.000' UNION ALL
SELECT 5107,'user1','u1pw2','2008-12-15 10:07:00.000' UNION ALL
SELECT 5217,'user1','u1pw3','2009-01-26 12:37:48.000' UNION ALL
SELECT 5412,'user1','u1pw4','2009-03-31 11:55:45.000' UNION ALL
SELECT 5441,'user2','u2pw1','2009-04-13 09:27:12.000' UNION ALL
SELECT 5545,'user1','u1pw5','2009-05-19 08:36:00.000' UNION ALL
SELECT 5585,'user2','u2pw2','2009-06-03 09:42:57.000' UNION ALL
SELECT 5586,'user2','u2pw3','2009-08-06 14:48:18.000'
--Show the table
SELECT *
FROM [#PasswordHistory]
ORDER BY [UserName], [PasswordDate] desc
--Clean up
DROP TABLE [#PasswordHistory]
What I’d like to see left in the table is everything except ID 5107 and 4957.
I think I should be able to do this with some in-line views and select top, but I’m having a mental roadblock this morning. I was hoping I'd have an aha moment while setting up the sample data and such, but no luck. Any thoughts or ideas to nudge me in the right direction would be very much appreciated.
Thanks in advance.
-Luke.
October 20, 2009 at 9:49 am
EDIT: And then I saw the section this was posted in. Sorry.
I think this will get you want you are looking for. If not then maybe it will help get to the right solution.
Use TempDB --Do this sometplace safe...
--CREATE Test Data
CREATE TABLE #PasswordHistory(
[ID] [int] IDENTITY(1,1) Primary Key NOT NULL,
[UserName] [varchar](16) NOT NULL,
[Password] [varchar](20) NOT NULL,
[PasswordDate] [datetime] NOT NULL,
)
SET IDENTITY_INSERT [#PasswordHistory] ON
INSERT INTO #PasswordHistory ([ID], username, password, passwordDate)
SELECT 4957,'user1','u1pw1','2008-10-27 08:53:47.000' UNION ALL
SELECT 5107,'user1','u1pw2','2008-12-15 10:07:00.000' UNION ALL
SELECT 5217,'user1','u1pw3','2009-01-26 12:37:48.000' UNION ALL
SELECT 5412,'user1','u1pw4','2009-03-31 11:55:45.000' UNION ALL
SELECT 5441,'user2','u2pw1','2009-04-13 09:27:12.000' UNION ALL
SELECT 5545,'user1','u1pw5','2009-05-19 08:36:00.000' UNION ALL
SELECT 5585,'user2','u2pw2','2009-06-03 09:42:57.000' UNION ALL
SELECT 5586,'user2','u2pw3','2009-08-06 14:48:18.000'
--Show the table
;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY username ORDER BY passwordDate desc) AS rowNum
FROM [#PasswordHistory]
)
SELECT * FROM CTE
WHERE rowNum <= 3
--Clean up
DROP TABLE [#PasswordHistory]
October 20, 2009 at 9:54 am
How about this, I don't think it has any features above 2000.
edit: to allow for a variable to be passed
Use TempDB --Do this sometplace safe...
--CREATE Test Data
CREATE TABLE #PasswordHistory(
[ID] [int] IDENTITY(1,1) Primary Key NOT NULL,
[UserName] [varchar](16) NOT NULL,
[Password] [varchar](20) NOT NULL,
[PasswordDate] [datetime] NOT NULL,
)
SET IDENTITY_INSERT [#PasswordHistory] ON
INSERT INTO #PasswordHistory ([ID], username, password, passwordDate)
SELECT 4957,'user1','u1pw1','2008-10-27 08:53:47.000' UNION ALL
SELECT 5107,'user1','u1pw2','2008-12-15 10:07:00.000' UNION ALL
SELECT 5217,'user1','u1pw3','2009-01-26 12:37:48.000' UNION ALL
SELECT 5412,'user1','u1pw4','2009-03-31 11:55:45.000' UNION ALL
SELECT 5441,'user2','u2pw1','2009-04-13 09:27:12.000' UNION ALL
SELECT 5545,'user1','u1pw5','2009-05-19 08:36:00.000' UNION ALL
SELECT 5585,'user2','u2pw2','2009-06-03 09:42:57.000' UNION ALL
SELECT 5586,'user2','u2pw3','2009-08-06 14:48:18.000'
DECLARE @test-2 INT
SET @test-2 = 4
--Show the table
EXEC ('SELECT * FROM #PasswordHistory PH
WHERE id IN (SELECT TOP '+@test+' id FROM #PasswordHistory PH2
WHERE PH.UserName = PH2.UserName
ORDER BY PH2.UserName, PH2.PasswordDate desc)
ORDER BY PH.UserName, PH.PasswordDate')
--Clean up
DROP TABLE [#PasswordHistory]
October 20, 2009 at 2:11 pm
Matt thanks for the quick reply, sorry I just got back to working on this. For some reason the thread wasn't emailing updates to me. Yes that code gets me very close and I should be able to take it from here, just have to rewrite the selects to deletes changing the ins to not ins and all should be well. It's already running about 1000 times faster than the old method.
Thanks a bunch!
-Luke.
October 20, 2009 at 5:17 pm
I can suggest an alternative method depending on how that one performs on your real table. Let us know how it goes once you get it all re-wired.
October 20, 2009 at 6:54 pm
I'll let you know when I get back into the office tomorrow, but from the preliminary testing I did this afternoon it seems to work fairly well. It's a pretty small table ~1000 rows or so currently (250-300 users * the number of passwords), so slight performance improvements aren't terribly crucial, anything that comes back in under a second or so will work and be a huge improvement over the previous process which took a number of minutes. The devs did a db call to get all of the distinct usernames, then looped over that recordset looking bringing back all of the user's records one username at a time. If they had more than the variable's number of stored passwords, those to be deleted were written out to an array which he looped through again to create a dynamic delete statement which he finally executed.
Just getting it from about 300 database calls down to 1 has been a huge improvement. But like I said I need to do a bit more testing tomorrow and I'll let you know how it comes out.
Thanks Seth.
-Luke.
October 21, 2009 at 12:03 pm
Well I finally found time to get back to this... I rewired the query and it performs quite well against my real data. Like I said earlier this is for a pretty small table, but the process executes in 60 ms or so. A significant improvement over what I started with.
Just for my own betterment, what's your alternative method Seth?
Thanks again guys,
-Luke.
October 21, 2009 at 2:11 pm
It looked to me like it would have to rescan the table for each row in the table for that WHERE clause. I may have misread the execution plan, or it could just be that it still works fine because you only have 1000 rows and they all fit into memory; masking the repeated scans. If I didn't misread the plan, then it is probably fine for now, but you've basically created a time bomb. One day in the future(when your table has grown) your 60ms sp might suddently start taking 3 minutes and leave you running around frantically looking for what changed.
I'd have suggested either a quirky update, a select into a temp table with ID values, or maybe 1 or 2 other things depending on a lot of factors that I didn't delve into about your setup.
October 22, 2009 at 5:52 pm
How many times users change passwords?
I bet - not quite often.
Create a table "RecentPasswords".
Create a procedure "UpdateRecentPasswords".
Procedure to be called every time user's got a new password saved (from trigger or from saving password procedure). It should add new password to RecentPasswords and delete "expired" passwords for the user(s).
Because you're not gonna save more than 1 password at a time there's not gonna be any performance issue.
And your SELECT will be straight forward - simple and lightening fast.
_____________
Code for TallyGenerator
October 22, 2009 at 7:20 pm
Garadin (10/21/2009)
It looked to me like it would have to rescan the table for each row in the table for that WHERE clause. I may have misread the execution plan, or it could just be that it still works fine because you only have 1000 rows and they all fit into memory; masking the repeated scans. If I didn't misread the plan, then it is probably fine for now, but you've basically created a time bomb. One day in the future(when your table has grown) your 60ms sp might suddently start taking 3 minutes and leave you running around frantically looking for what changed.I'd have suggested either a quirky update, a select into a temp table with ID values, or maybe 1 or 2 other things depending on a lot of factors that I didn't delve into about your setup.
Seth, first sorry I was in the process of typing a reply to this yesterday when I had a server go down that I had to take care of... Anyhow, yes there are 2 scans in the execution plan, but this will be so seldom used that there really should be no chance of a time bomb down the road, this is for 2 reasons... 1) this is a maintenance script that only fires when an admin changes the number of passwords stored for the application. This would typically be done once when it's set up, and the number most often times increased in the future and not decreased, however this needs to be available in case the number is ever decreased, no matter how seldom that may occur. 2) There will never be more than 200 active users of this application, actually it's much more likely there will be approx 100-150 users as we keep getting downsized. I use the number 300 to account for people who came and left before they could accrue the number of passwords to be retained, basically those who didn't make it past 180 days.
I was playing around with the idea of selecting either the good records into a temp table and then doing the delete based on that, but it didn't seem to give me the simplicity that I was looking for from this. I guess I was just somehow stuck on the idea that this really should be able to be done with 1 statement. I was just having some issues getting my syntax together. Not enough coffee, sleep etc...
Sergiy, thanks for the thoughts. Yes the users don't change their passwords all that often, I think 60 days with prompting that starts some time before that... and we're only keeping the previous 4 passwords now. This procedure is more for deleting the extra stored password if we ever revert back to keeping less than 4 passwords, but I kinda doubt that will ever happen. much more likely they number of passwords stored will increase instead of decrease.
Again thanks for your thoughts and code. It was very helpful.
-Luke.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply