April 24, 2009 at 10:24 am
I have a table, where I'm looking to keep rows representing Users last 3 web pages browsed for a given section of a web site. These relate to the last 3 item pages visited, and will be displayed in a box showing recently reviewed item history. I want to keep only the 3 most recent browse entries and remove the old ones on a rotating basis.
So, for example, when user_id 1, browses his/her his 4th page, the oldest entry drops out, always keeping, at most, 3 records per user.
Each item page browse will trigger a stored procedure call to add a new record into this table. (and could possibly handle this clean up).
I'm looking for suggestions on some efficient ways to handle this.
For now, I'm inserting the records as they come, and on the select stored procedure using set rowcount 3 to limit the browse history in the select stored procedure. Then on a daily basis, using the ROW_NUMBER() and OVER clause to partition the table by user_id ordered by date desc, and then deleting everything with a row number > 3.
Is this the best way to accomplish this task? Is there a simpler or more efficient way I'm not thinking of (possibly removing the 4th records in real time as the new one's inserted)?
Thanks for your thoughts!
April 24, 2009 at 11:33 am
I think the solution you already have is good, though I would change the current select to be select TOP 3 instead of using SET ROWCOUNT. (In the next version of SQL, you won't be able to use SET ROWCOUNT in insert, update or delete statements.)
Removing the records in real-time sounds like it would be a lot of activity for a busy web server. The set-based approach you already have sounds good. Do you have it combined in a CTE with the delete statement?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 24, 2009 at 12:39 pm
No, I don't have it in a CTE, but I will go back and look at refactoring the delete procedure to do it as such, that would be good.
Thanks!
April 24, 2009 at 12:58 pm
Solution you have should work. Prior to 2005 and CTEs, ROWNUMBER(), etc I used a different method for something similar. In your case you could look at creating a row number column on your page history table. When you call your procedure to insert a new record you would do the following steps.
1. Increment existing rownumber column values. Update Set rownumberField = rownumberField + 1 WHere UserID = @userid for example. This will move all current records one stage older.
2. Insert the new record with a value of 1 in the rownumber colum.
3. Finally delete all rows for that @userid where rownumber >=3
Its kind of the same thing you are doing, but running for only a single userid and running at the time of insert (in one sp) vs batch processing it. Not sure what performs better, but if I was writing it today I would probably use the ranking functions.
April 24, 2009 at 2:55 pm
Yeah, that's essentially what I'm doing, except I'm doing the deletes as a set. Well seems like I've got a good solution.
Thank you for your feedback!
April 25, 2009 at 12:08 am
While I'm sure your solution will work fine, it seems like overkill to fire a trigger and delete a row every time a user hits a new page. Why not just index on the date/time and only select the latest three rows from however many rows are in the table? You could purge all of the "excess" rows nightly to keep the table from growing too large.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply