And a Little Bit More

Webcast: Get Your Implementation Organized

Service Accounts – Web Applications – Application Pools – File Locations – Database Names… when it comes to SharePoint environments, if you don’t plan ahead, your environment can quickly become a disorganized mess of IIS, SQL & SharePoint components. Suddenly you don’t know what web application maps to what application pool in IIS, or even worse, you don’t know what service accounts perform what functions! This session will cover strategies and provide recommendations on ways that SharePoint administrators can reign in their farm – and make sure that no matter who is managing it, everyone is on the same page. Organization is key – and this session will help get you one step closer to organization nirvana.

Presented by: Christopher Regan

> Register Now
> Live date: 8/25/2010 at 12:00 Pacific

And a Little Bit More

Ok, its been a few days and you may have forgotten I’ve been playing with bits. I mentioned a while back that SQL Server will combine a series of bits into a single byte if a table has more than one bit column. That’s neat for storage.

So, I’ve been playing with a data warehouse that has a lot of binary facts resultingin a lot of bit columns. Something you might understand would be purchasing a car with 30 options. Do you want the leather seats, electric windows, custom wheels, extended stereo, electic doors, and so on.

Technically you could call these options and normalize them into a separate table, storing only the options that were chosen. Then pivot the data back when you want to see all the options.

However, in a data warehouse or data mart these same facts may be expressed as a series of bit columns. This can make it easier when you are searching for records accross these different options. But it can slow things down for performance…sometimes bit columns don’t even index well because they have only two values.

Also, it becomes difficult to write queries when you want to pick some options and not others for your search.

One interesting technique I have come accross is to combine a series of facts formerly in separate bit column into a single column with a large data type such as a BIGINT, and let each bit in that column represent a different fact. SQL Server has done that for years for things like properties.

I can combine multiple facts into a single bitmask column. I can also combine multiple filter options into a single value for comparison using the same bitmasking methodology. A comparison of the BitMask filter with the BitMask Column using a AND TSQL operator results in comparing all bit options. Using a BIGINT data type you can compare up to 63 different attributes in a single CPU cycle on a 64 bit processor. That’s a lot of logic for a single CPU cycle.

Here is an example…

So a vehicle that has all of these options converted into a bitmask would look like 1111 and would have the integer value of 15 (1 + 2 + 4 + 8).

If I wanted to look for all cars that had electric windows I could create a test bitmask of 0100 with an integer value of 4 (0 + 0 + 4 + 0).

To find all vehicles I would perform a comparison of all vehicle bitmasks with a test value of 4. All the vehicles that returned 4 meet my comparison criteria. A BitWise AND of 4 and 15 = 4 (4 & 15 = 4).

If I wanted to test for all vehicles with Custom Wheels and Cruise Control I would do a BitWise AND comparison of 9 and the vehicle options and return those records where the result was 9.

You can do an OR comparison using a BitWise AND as well. If you wanted to find all cars that had Custom Wheels (1) or Electric Windows (4) you could BitWise AND 5 with all vehicle bitmasks.

Any vehicle that had bit 1 (Custom Wheels) set and not bit 3 would return a 1.
Those with bit 3 (Electric Windows) set but not 1 would return 4.
Any vehicle that had both bits set would return 5.
Any vehicle that had neither of these bits set would return 0.

So, to perform an logical OR using a BitWise AND comparision the formula would be 5 & 15 > 0.

The more bits you have the more effective this strategy becomes. Remeber, the BitWise AND must perform a table or index scan, and most often won’t use indexes effectively using a bitwise column by itself. But when you are including a lot of bit columns in a filter, using this method in conjunction with other filtering columns can perform rather nicely.

I know this is really quick…my intention is to wet your appetite for more research and encourage interaction. Send me you questions and insights to btaylor@sswug.org.

Cheers,

Ben

Featured Article(s)
Oracle and RAC: Grid computing overview
In this introductory article we shall discuss some of the key concepts associated with Grid computing and the enabling technologies that are being introduced by Oracle