11

I have a table with about 1 million to 5 million records. Small part of that records have one of bit column set to 'TRUE'. Need to fast find that records. I think that index can speed up searching on this column, but i afraid about INSERTs. Hence my question.

Database is working like sort of data warehouse, so there is many SELECTs and small (up to 10-20 per day) but quite large INSERTs (up 200 thousand records at once). I'm afraid about longer time of that imports to database.

Improve this question
4
  • 5
    What version of SQL Server? If 2008+ sounds like a filtered index could be what you need.
    – Martin Smith
    Feb 14, 2014 at 12:59
  • SQL Server 2005
    – marioosh
    Feb 14, 2014 at 13:01
  • 1
    You could split the table (add a new table with only one column, the PK of the table, which would be populated with only those rows that the bit column is true - in the end you could even remove the bit column.) An indexed view would work, as well in 2005, with the lack of partial indexes.
    – ypercubeᵀᴹ
    Feb 14, 2014 at 14:47
  • be care full with Indexed View, as you mentioned you have 10-20 large insert per day, maintenance of Indexed view might surpass the benefit of performance gain. i don't think any "out of the box feature" of SQL 2005 you can utilize to improve your situation. but if you list out the current table structure and existing index we may find some alternate design.
    – Anup Shah
    Feb 14, 2014 at 15:11

2 Answers 2

Reset to default
8

An index on a bit for 1 million records is useless. The optimizer will never ever use it, you'll just pay for maintaining it. A much better alternative is to add this bit as the leftmost key on the clustered index.

But I will make a blind shot in the dark and guess that what you have is a queue pattern: records are dropped in the table with the bit set to 'TRUE' (ie. 'needsprocessing = true') and then a background process looks for these records, does some processing, and updates the bit to FALSE. This is a omnipresent pattern, also affectionately know as the 'performance disaster recipe pattern'. I would recommend dropping the records in to the table and dropping a notification (could be as simple as the newly inserted record ID), at the same time, into a queue. See Using Tables as Queues.

Improve this answer
6
  • 1
    I am not seeing any good point in putting the bit column on left most side as we do not know other filter columns with high Cardinalality user may have. so far i have seen BIT column is the last choice in clustered index. but yes, +1 for the nice reference of "Using Table as a Queues".
    – Anup Shah
    Feb 14, 2014 at 15:21
  • 2
    Actually I ran a test and yes it will use the index. Create a table (Id identity, myBit bit) add 100 rows where the bit is 0 and 2000000 where the bit is 1. Make sure statistics are updated (if needed) and run a query on myBit = 0 and the index will be used.
    – Kenneth Fisher
    Feb 14, 2014 at 15:47
  • @KennethFisher except that in the typical high velocity pattern of insert TRUE/update to FALSE immediately the stats will always be outdated. If you prefer to play Russian roulette with the optimizer rather than do a clear design, you'll get what you deserve...
    – Remus Rusanu
    Feb 15, 2014 at 9:27
  • "will never ever use it" that statement holds for 99% of the cases, but we don't know what case the OP is in. I have successfully indexed on bit. Use cases exist.
    – usr
    Feb 15, 2014 at 22:28
  • question -- is the answer here wrong, specifically > "When you index a bit field (or some narrow range), you only reduce the working set by the number of rows matching that value. If you have a small number of rows matching it would reduce your working set a lot. For a large number of rows with 50/50 distribution, it might buy you very little performance gain vs. keeping the index up to date." In which case, an index on a bit that matches 1% of the records would obviate the need to scan 99% of 1 million for a significant boost?
    – drzaus
    Jun 5, 2015 at 14:52
2

As @MartinSmith said if you ever upgrade to SQL 2008 then a filtered index would be the perfect solution. However in the mean time as a general case ANY added index will increase your load time. Small indexes less so than large ones.

One thing I would look at is if you have an existing index that can be modified. Assuming your existing queries are using a given index then adding the bit column to the end of that index should have minimal effect on inserts and the positive effect you are looking on your queries.

Next thing to look at is "Do I have a lot of indexes already?" There is no hard and fast rule as to what "a lot" is but I usually go by a rule of 10 indexes is the limit unless I REALLY need a new one.

Last thought, test it out on a test instance. Set up a table with a few million rows, run your load on it, add your index then run your load again and see if you notice an significant increase in load time.

Only you can really decide what "significant" is. I have machines where adding 5 minutes to the load time is "significant" and others where I could safely see a couple of hour increase.

EDIT:

Another option is to partition your table. You may have to use a partitioned view if you are not using Enterprise edition but even so it should help. You put your bit 0s in one partition and your bit 1s in another. Assuming you are only inserting one version or the other then you may even speed up your inserts.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.