Dealing with very large bitmasks

re: Dealing with very large bitmasks

Jason — Thu, 29 Nov 2007 03:29:01 GMT

I appreciate your binary examples. One of the things that perplexes me as a previous application developer turned sql developer is that all of your examples would be better handled on the client side. Some of your code...especially the use of a numbers table...is extremely innovative. But, in a total solution I don't see why I would use the database to handle any of them. Don't mean to be harsh...but i could see a carpenter build a nice space shuttle with wood... I just don't see it getting far.

re: Dealing with very large bitmasks

Adam Machanic — Tue, 04 Dec 2007 15:30:36 GMT

Jason:

As I mention several times in the post, I do not recommend actually using this technique, for several reasons. That said, it is certainly not hard to think of scenarios where one might want to do these kinds of things in the database. Think encapsulation. If -- for some very, very good reason -- you need to use bitmasks in the database, but perhaps don't need to expose the bitmasks to the application, you can and should do this work in the database in order to keep the application and database as loosely coupled as possible.

I am all for handling tasks wherever they are best suited, but I'm an even bigger fan of highly modular designs. So if I can keep the application ignorant of some hack I've worked up in the database, all the better...

Correction on bitmask handling

Adam Machanic — Sun, 25 Jan 2009 19:46:07 GMT

In the article on handling bitmasks I posted the other day, I made a fatal error in the splitBitmask

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

Bitmask Handling, part 3: Logical operators

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

It's been longer than I hoped since my last installment on bitmask / big number handling . Life caught

re: Dealing with very large bitmasks

Derek — Thu, 17 Jun 2010 19:25:24 GMT

Just FYI Adam, I love this article. I've been here hundreds of times so attribute a lot of traffic to me!

re: Dealing with very large bitmasks

Adam Machanic — Sat, 19 Jun 2010 11:36:02 GMT

Glad you enjoyed it. You know you can simply print it, frame it, and hang it on the wall, right? :-)

re: Dealing with very large bitmasks

David — Fri, 23 Dec 2011 22:08:40 GMT

We do use large bitmasks in our database - and until now, we've handled them via SQL CLR, making all processing rather fast. However, with SQL Azure, CLR is no longer an option, at least for now. I tried re-implementing some of our bitmask user-defined functions in SQL basing myself on your wonderful articles and the bitmask reconstruction operation seems to be much slower than its SQL CLR counterpart. Do you think the bad performance is an intrinsic property of the bitmask reconstruction, or is there room for optimization?

re: Dealing with very large bitmasks

Adam Machanic — Sat, 24 Dec 2011 03:19:32 GMT

Hi David,

It seems natural that a CLR approach would work much better--it's a very CPU-heavy operation, and CLR functions almost always deliver higher performance in those cases. Is it so much slower that it's unbearable?

I originally wrote all of this stuff 7 or 8 years ago, so I'm not going to be able to give you a great answer about improvements without doing some testing. But taking a quick look at it, I'm thinking that yes, there is some room for optimization -- perhaps by using some of the XQuery methods that were added in SQL Server 2005. Are those available in Azure? I've luckily managed to avoid having to use it, to date!

--Adam

re: Dealing with very large bitmasks

David — Wed, 04 Jan 2012 00:10:56 GMT

The performance degradation is significant even for the binary mask creation. Consider the following:

select Data, fnCreateBinaryMark(40) from SomeTable

Which would create a binary mask of 40 bytes for each row of the table.

For a table containing ~4000 rows, the SQL CLR version runs in less than a second while the purely SQL version takes 6 seconds.

Since the binary mask creation is simply the concatenation if table variable values and since the re-construction of the varbinary from a table already seems to be a costly operation, I have a feeling I would not be able to build performant bit operation functions, since it's the mandatory final step.

I do believe XQuery is available on Azure, but not sure how it can help - afaik, bit operations are not part of XPath/XQuery.