January 23, 2009 at 7:19 am
We have a very simple stored procedure to return the TOP 100 rows from the largest table in a database. (the most recent 100 sorted by entered/creation date) Psuedo code for the sproc is:
Single parameter passed to the sproc:
@ClientID Uniqueidentifier
SELECT TOP 100 Col1, Col2, Col3
FROM Table
WHERE ColIndex=@ClientID
ORDER By ColOrder Desc
ColIndex is a guid and ColOrder is datetime, both are indexed.
I have also tested using SET ROWCOUNT 100 with same results.
The execution plan shows Index Seek on ColIndex and then Bookmark Lookup for the Order By, going through all rows that match ColIndex (which can be tens of thousands).
Using another index to restrict the initial resultset, like limiting DateRange via
[...AND ColOrder>@SomeDate]
isn't practical as the number of records per ClientID can vary from zero to 200k+, so I cannot use an arbitrary date and assume there will be 100 records within that date range.
The only method I have found to get reasonable execution on this stored procedure is to add WITH RECOMPILE. Using this reduces execution time by 75%. While I understand what/why the execution plan is constructed this way, is there a better way to do this?
No records are ever deleted from the table. Inserts are a fraction of a percentage per week and all indices are rebuilt once per week.
January 23, 2009 at 8:47 am
Have you checked the Stats for the Table?
-Roy
January 23, 2009 at 9:18 am
Stats for the Date index:
Jan 17 2009 3:01AM 7214977214971551.8559814E-612.0
For the ClientID:
Jan 17 2009 3:01AM 7214977214972001.2539848E-420.0
Auto Create and Auto Update statistics are checked for the db. (Auto shrink / close are NOT checked). Other than what is in BOL, I'm afraid I don't know what that would tell me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply