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
ORDER BY [UserName], [PasswordDate] desc
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.
To help us help you read this[/url]For better help with performance problems please read this[/url]