SQL Server Performance

Max x posts per user

Discussion in 'T-SQL Performance Tuning for Developers' started by brimba, Oct 16, 2006.

  1. brimba New Member


    I have a table that looks like this.

    - UserId
    - VisitorUserId
    - VisitDate

    This table holds the last visits to a user. But we only allow the user to access his x last visists so therefore we only want to store the x lasts visits as well. No need to take up space that we never use.

    What is the best way to delete the oldest posts over x grouped by UserId?

    One discussion could be about a good query, and another one about how to execute it. Should it be executed in each INSERT (if count(*) > x). Or maybe a daily night job? Maybe a job executed every minute?

  2. Adriaan New Member

    Use a query. You're not giving us the PK, so I'm assuming it's called XID:

    SELECT t1.* FROM table t1
    WHERE t1.XID
    IN (SELECT TOP 5 t2.XID FROM table t2
    WHERE t2.UserId = t1.UserId ORDER BY VisitDate DESC)

Share This Page