SQL Server Performance

Poor Performing Query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Righteousman, Feb 29, 2008.

  1. Righteousman New Member

    Hello,
    I've got a fairly simple table:
    CREATE TABLE MyComments
    (
    UserID BIGINT,
    Comment VARCHAR(50),
    CreateTime DATETIME
    )
    Pretty simple table. We've got about 300,000 records in it (with the average user leaving about 25 - 30 comments). The following index is on the table:

    CREATE INDEX MyComments_idx1 ON MyComments(UserID, CreateTime)
    And now I want to find the average time between comments.
    So, here's the query I'm using:WITH Summary AS
    (
    SELECT a.UserID, DATEDIFF(ss, a.TimeCreated, b.TimeCreated) AS Variance
    FROM MyComments a
    LEFT JOIN MyComments b ON (a.UserID = b.UserID AND b.TimeCreated = (SELECT MIN(c.TimeCreated) FROM MyComments c WHERE c.UserID = a.UserID AND c.TimeCreated > a.TimeCreated))
    )

    SELECT AVG(Variance)
    FROM Summary
    The query takes about 45 seconds for 300K records which seems pretty slow.
    Has anyone run into this problem before? Surely there's a faster way to get the data I'm looking for.
    Thanks!
    Ben
  2. ndinakar Member

    Try creating another index on TimeCreated and see if it makes any difference..

Share This Page