how to efficiently query a large table with filtering

Question

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.

Answer 1

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.

Answer 2

Not sure but how about creating a filtered index on status column

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 3

if you're using SQL Server 2008 or higher take a look at filtered index

Filtered Index Design Guidelines

which allows you the defined a small index covering only records you're interested in. Especially if you've a asymmetric distribution of the values it can boost your query.

Answer 4

While 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.

Answer 5

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. 

Answer 6

it 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