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!

Is there any index available on custid column?

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.

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.

Whats your observation on execution plan of this query?

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 !!