More recent rows by user

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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]

  • 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]

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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 Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply