Correction on bitmask handling

Bitmask Handling, part 2: Bitmask reconstitution

Adam Machanic — Sun, 25 Jan 2009 19:47:50 GMT

Posting the first part of my series on bitmasks (yes, this is now officially a series) taught me a lot

re: Correction on bitmask handling

Eric — Sun, 05 Feb 2012 10:06:37 GMT

I read your article and it really came in handy, as this is unfortunately precisely what I needed to do for a project of mine. I have two questions though. I deal with large sets of data where I have to query against, and the change from using a long and the & power method versus dealing with a function to determine which bit is set in the varbinary has been a big hit performance-wise. Do you have any ideas for this?

Also, do you deal with C#? It appears when I would read or write the varbinary to C# it got it backwards, like your original suggestion on how to read the bits in the varbinary.

Thanks for your help!

re: Correction on bitmask handling

Adam Machanic — Sun, 05 Feb 2012 17:58:37 GMT

Hi Eric,

Yes, this is much, much, much slower than using bigint and bitwise methods. Which makes perfect sense, since those operations can often be handled in a single processor operation, whereas with my expanded methods you'll wind up with many, many, many operations (hundreds? thousands? depends on how big your bitmask is and other issues).

Making things much worse is the fact that this is all being done in SQL -- which is very slow with these kinds of things. My suggestion to you would be to utilize SQLCLR, but it sounds like you're already attempting that.

I'm not sure what you mean about getting the values backward -- that's never happened to be before (I don't think). But if it's happening that way consistently, can't you simply reverse the logic?

--Adam