Index help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Index help

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2015-03-18 : 14:13:57

Hi,
We have a table with 2 columns (along with others)
RetryCount
MaxRetryCount

every night, we need a SP to return all rows where RetryCount>=MaxRetryCount

Could anyone suggest the type of index I can create on this to not get clustered scan.
I am trying to get the requester to filter further on the creation date(data only for last 1 day or 2 days), but as yet, it doesn't hold true.:( They want for the entire table.

Thanks
Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 14:29:00

Have you tried a composite index with both columns?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

xpandre
Posting Yak Master

212 Posts

Posted - 2015-03-18 : 14:42:06

Hi Tara,

yes..Tried that, yet it goes for a clustered scan.
Tried giving index hint too..yet it goes for a scan.

Thanks
Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 14:50:57

Pretty sure you can't get away from the scan on this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

xpandre
Posting Yak Master

212 Posts

Posted - 2015-03-18 : 16:25:00

Thanks Tara..
I cited the concern and now I have a filter on date (last 2 days).. :)

Thanks again..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 16:28:27

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-18 : 17:58:32

You could add a computed, persisted column based on RetryCount>=MaxRetryCount and then index that column. If there are a limited number of matching rows, SQL should use that index rather than scanning the whole table. [Unfortunately you can't use a filtered index to limit the index to only those rows that match your search, because you can't filter on a computed column.]


ALTER TABLE table_name
ADD retry_gt_max_retry AS CAST(CASE WHEN RetryCount>=MaxRetryCount THEN 1 ELSE 0 END AS tinyint) PERSISTED;

CREATE INDEX table_name__IX_retry_exceeded ON table_name ( retry_gt_max_retry );

SELECT *
FROM table_name
WHERE retry_gt_max_retry = 1

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 04:20:00

Probably not the answer! but assuming this is a rare-ish event (judging by you not wanting a SCAN) use a trigger to log/act on the case when RetryCount becomes >= MaxRetryCount ?

Or process, each night, only the rows in the table since "last run time" and append any new rows to a new ExceptionsTable. i.e. your query will normally only processes one day's rows from the main table, but then stores a record of matched rows - which will include all previous days.

When rows are dropped from the main table that purge will also need to purge the Exception Table.

If there are a lot of rows where RetryCount>=MaxRetryCount there might be an issue on space, if not and it is a rare event it might be worth it for reduced processing time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources