Featured Article(s)
SQL Server 2008 Temporary Tables Optimization Tips
In this article, you can find some tips to use SQL Server 2008 temporary tables.
Bit Mapping Rewind
So I’ve been playing with Bit Maps and had a few things to share as a follow up of the editorial of 8/20/2010.
Like most ideas, they aren’t new. Just new application of things we did years ago.
Ed writes:
Just a small cavil – In the last paragraph of the article "And a Little Bit More" of the 8/20/10 SSWUG newsletter, you said "…my intention is to wet your appetite…". I’m sure you hope to whet (sharpen it or give it an edge) our appetite rather than pour cold water over us.
But thanks for the article. Using the bit mask is an old IMS trick to save space but still be searchable back when IMS didn’t do bits. Good idea and very well written.
Well, Ed, I appreciate the editing. Grammar has always been my weakest skill (besides surfing).
So, while playing with the idea I had a customer with a table containing 200+ bit columns that were truly unique. The table only had 140k rows. No matter what I did it ended up performing a table scan because of all the comparisons being performed. I was able to reduce the 200+ columns into four BIGINT columns.
Since the optimizer was performing a table scan I created a clustered index on the few non BIT columns, for which user parameters were supplied, reducing the number of comparisons marginally. Then I converted each of the BIT input Parameters into 4 BIT MAP BIGINT Variables. Short story, my query time dropped from 2+ seconds to 156 ms. Even though it had to perform a bitwise AND on every row that was scanned, and convert the input parameters into 4 comparison variables..
I guess thats enough on bits. I just wanted to whet your appetite 🙂 and encourage you to try them when you have a problem that really needs them. The save space, and can increase query performance when BIT columns are needed.
Keep your comments and critiques coming to btaylor@sswug.org. We’re always open to things you’d like us to cover as well.
Cheers,
Ben