SQL Server Performance

Long Running Simple Query

Discussion in 'Performance Tuning for DBAs' started by danny123, Jul 24, 2008.

  1. danny123 New Member

    Hi there,
    In one of my stored procedure there is a simple query which is taking around 30 seconds to run. The table have 6592978 records in it.
    And there are 2 indeces on the table on Clustered index on the Identity Column of the table and other on the FormID. Identity column is other than CustID column.
    SELECT TOP 1 dtmDateAdded
    FROM tblXXX WITH(NOLOCK)
    WHERE CustID = 123 AND FormID = 123
    ORDER BY dtmDateAdded DESC
    Please advice.
    Thanks!
  2. Balasundaram New Member

    Is there any index available on custid column?
  3. TommCatt New Member

    There are a couple of things I would recommend. By default, the PK takes up the one allowed clustered index. In your case, this would appear to be an Identity field that is not even used in this query. I would hazard a guess, based on experience, that the PK field is only used in queries that return a single row rather than queries, such as this one, that return multi-row result sets. If so, you may want to redefine your PK to be nonclustered and then create a clustered covering index. However, the clustered index should be the index that benefits the type of query that most impacts performance. That may or may not be this particular query.
    In any event, a covering index, clustered or nonclustered, would benefit you here.
    Create (non)Clustered Index IX_TableXXX_Cover1 ON dbo.TableXXX(
    CustID Asc,
    FormID Asc,
    dtmDateAdded Desc
    );
    Note the datetime field is defined DESCENDING, just as your ORDER BY clause. If this doesn't return a result in 2 seconds or less (average!), let me know.
  4. danny123 New Member

    Bala - Yes non clustered index is there on CustID
    Tom - Let me built this index and i will let you know how it goes.
    Thanks guys for prompt responses.
  5. satya Moderator

    Whats your observation on execution plan of this query?
  6. danny123 New Member

    Hi there,
    I created the index on the table as suggested by Tom and updated the statistics. That did the trick. Now its running in less then1 second.
    Thanks everyone !!

Share This Page