SQL based bit operations

Archive
1

Based on my exposure to bit operation in .NETMF, I am optimizing some business apps based on our MS SQL database (2005 currently, soon to be 2012).

I want to use an 8-bit value to hold 3 distinct values. Part of the issue is that I will likely not know the value before hand.

(right to left)
1 - 4 = int value that will get replaced (0 - 15)
5 = a bit flag (0 or 1)
6 - 8 = a counter (0 - 7)

For the most part, setting the bit is not part of this issue. So it’s replacing the first 4 and incrementing last 3 bits.

For example - starting value (1, false, 6):
0010 0110

I want to change this to (2, false, 5):
0100 0101

The issues I am running into is that I can’t shift in T-SQL, it only supports or, and and xor. Also, to optimize to process, I want to be able to adjust the 2 separate values in single SQL query - so I am doing a single scan & log entry.

If it’s not possible to do it in one operation, then 2 updates would be fine – one for the first 4 (while preserving the last 3) and then last 3 (while preserving the first 4).

2

You can shift by using multiply and divide. Shifting left 1 bit is the same as multiplying by 2, while shift right is the same as dividing by 2.

Now that I am at a decent keyboard here is a decent answer:

An SQL Server function to build your value might look like this


CREATE FUNCTION BuildBitValue 
(
  @ value tinyint,
  @ flag bit,
  @ counter tinyint	
)
RETURNS int
AS
BEGIN
  RETURN (@ value & 0x0f) | (@ flag * 16) | ((@ counter & 0x07) * 32)
END
GO

And you can call it like this


SELECT dbo.BuildBitValue(5, 1, 2);
3

What’s the end goal here? Micro-optimizations such as these are pretty much NEVER suitable for non-embedded applications. If you’re attempting to save a few bytes of disk space, consider that you’re trading off clarity, potential for errors, difficulty in working with the data, CPU time to encode and decode the values, and the inability to do a straight update of a column (what would’ve been a straight update is now a read-modify-write, taking out a lock on the row during that process).

Remember the two rules of optimization:
[ol]Don’t optimize.
(For experts only) Don’t optimize yet.[/ol]

In this case, unless you’re dealing with trillions of rows, where the disk savings would actually be measurable, I wouldn’t call this an optimization, I’d call it an obfuscation.

4

Thanks!! So then shift right in SQL parlance would then be: Value / Power(2, places)

(thinking through this while typing it out; so pardon the stream of conscience post style :slight_smile: )

I can now increment the last 3 bits like:

value = ((value / Power(2,5)) + 1) * Power(2,5)

and then to simultaneously also replace the first 4 bits:

value = ((value / Power(2,5)) + 1) * Power(2,5) + newValue

The result == Woohoo!! :smiley:

5

I agree with these guys… This will be a fun exercise but I would never do this in a production app. People that have to use this data WILL kill you.

6

It’s not about storage space.

We current are storing these values separately in a combination of various int and char(25) fields (as well as char values we don’t really need for this operation). Our apps do most of the comparisons for business logic based on the string values on the client side. So if I can move most of the ops to the server and do bit level verses string (length, substring, etc) ops, I think I can greatly minimize our current bottleneck.

I am also saving the data in “human readable” format as well for reporting & front-end display, but this in background thread with a queue.

7

Moving logic into the database engine is nearly always a losing proposition. Move your CPU intensive logic out to the web servers, where it’s trivially easy to scale horizontally (meaning, it’s very easy to add more web servers and load balance among them). If your database server is overworked, it’s very difficult and expensive to scale them (because, in general, you have to scale vertically by buying a bigger and more expensive server, and you lose any investment in the previous hardware).

Having been where you are now (including packing multiple logical values into a single column using bitwise operations), I can tell you, this road does not lead where you want it to.

8

I can’t go into details, but its not a web based application. It’s an n-tier app.

We had apps written by former employees that stored dozens of values in single fields and that was nearly unmaintainable and unreadable. The app was quick, but nobody wanted to touch it.

(edit)

Of course where possible I am going to do A/B testing with the current and proposed methods.

@ taylorza - my final solution wouldn’t have had kept the power() in there - that was just me thinking out loud.

Thanks for all the responses.

9

While this has been extremely helpful (and will be used I’m sure some time in the future), I went a different route. I realized that I wasn’t really married to the current architecture. The existing structure wasn’t relational, so I changed things around quite a bit and have gotten great perf gains as well as maintainability & readablity.

Thanks again for all of the help!

10

SQL server 2012 has the .net CLR built in. What's New in CLR Integration - SQL Server | Microsoft Learn which is pretty good for operations like this.

11

@ Dr9 - .NET CLR has been available in SQL Server since SQL Server 2005. But beware to test performance, transitioning between the SQL engine and .NET incurs considerable overhead. Used at the right time this is an incredible technology, but make sure you know when it works and when it hurts.

12

Native stored procedures are another option, but the integration is significantly more difficult.

13

Now you are bringing back memories :slight_smile: I have not written an extended stored procedure since the late 90’s… Note that MS has stated that extended stored procedure feature will be removed from SQL Server so use at your own risk…

14

If I understood our MS rep, there is a change on where the CLR is hosted and supposedly it is less of an issue.

All hearsay, though. We use Oracle DB pretty much exclusively here.