Friday, April 13, 2012 8:34 AM
I have a large table with records of tasks coming in. If 1 task is done, the row will be updated with a column "finish" to "Y". The periodical query will execute every minute to see whether there is new task or state with "N" as not finished tasks.
I just would like to query more efficiently instead of scan whole table. for example, is it the best way to solve it by setup a combined non-cluster key with TaskID and Finish column? or any other better solutions?
Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
Friday, April 13, 2012 8:48 AM
Use an additional queue table (store the task id only). It maybe automatically filled by a trigger on your task table, e.g. when a new unfinished task is inserted add the task id in that queue. When the task table is updated then add or remove the id from the queue table. Also use a delete trigger for house keeping.
btw, finish as a flag is pretty useless. Flags in databases per se, but this is a different story. I would use three date/time columns for that: TaskCreated, TaskStarted, TaskFinished.
Friday, April 13, 2012 8:52 AMNot sure but how about creating a filtered index on status column
Thanks and regards, Rishabh , Microsoft Community Contributor
- Edited by Rishabh K Friday, April 13, 2012 8:57 AM
Friday, April 13, 2012 8:56 AM
if you're using SQL Server 2008 or higher take a look at filtered index
- Marked As Answer by Dannol Liu - MSFTMicrosoft Employee Wednesday, April 25, 2012 1:31 AM
Friday, April 13, 2012 9:15 AMWhile this can be an adhoc-solution, this is imho a conceptual problem. I'd prefer a clean model here. This includes a real job table where a row indicates one job to execute.
Friday, April 13, 2012 9:17 AM
In addition to previous answers:
The index could be not just filtered by [Finish] column, but also covered one. So, consider to add all necessary columns, like [TaskID] to avoid unnecessary lookups to underlying table.
Friday, April 13, 2012 9:17 AMit is best if you create a new table with unfinished task ID's only. Once your task will completed you only need to delete a record from this table. Once new task comes in you need to insert it in 2 tables orignal table and this new UnfinishedTask table. if you need to return records that are not finished join this table in your query and you will get your answer. Enforce FK index and you will get index seeks.
Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com