CODEWITHSUNDEEP
c#sqlsql-serverperformance
Ranjit Singh
Ranjit Singh

Reputation: 3735

Alternative of Bitwise operation in sql query to achieve index seek

Let suppose there is a table having two fields "id" and "value". I have created a non-clustured index on "value" field There is a parameter "@valueToCompare".

For example : 
MyTable
Id(Int) Value(int)
1       9
2       11
3       13
4       7
5       8
6       20

@valueToCompare = 27

Now I want to write a query which will give me the resultset which satisfy "value & @valueToCompare = value" condition Hence my query will is

select * from MyTable where value & @valueToCompare = value

which will give the result after calculating

9 & 27   =9
11 & 27  =11
13 & 27  =9
7 & 27   =3
8 & 27   =8
20 & 27  =16

Now the problem with this query is that the optimizer will do index scan rather than Index seek, hence less efficient.

So wanted to know is there a way to write the query to achieve index seek.

NOTE : I Will use the result set in c# code

Upvotes: 1

Views: 1563

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

Since the current value of @valueToCompare participates in an expression with one of the columns, there is no way to convert this to an index seek. However, you could potentially get a much faster lookup if you are willing to split the individual flags into their own columns.

To represent ints from 0 to int.MaxValue you would need 31 bit columns - say, v0 through v30, inclusuve. You can make an index on them, and then split the search number into individual bits, and run your query. Instead of value & @valueToCompare = value you would write a combination of checks corresponding to @valueToCompare's binary value.

For example, if @valueToCompare is 910 or 10012, the query would look like this:

SELECT ...
WHERE v0=1 AND v3=1

v0 is the column containing bit zero; v3 is the column containing bit three. These are the two bits set in the binary representation of nine.

Rather than managing the columns manually, you could let SQL Server manage them for you.

create table bitfields (
id int,
value int,
v0 as value & 1 persisted,
v1 as value & 2 persisted,
v2 as value & 4 persisted,
v3 as value & 8 persisted,
v4 as value & 16 persisted,
v5 as value & 32 persisted,
v6 as value & 64 persisted,
v7 as value & 128 persisted
-- ...and so on
)
create index bitfields_idx on bitfields (v0,v1,v2,v3,v4,v5,v6,v7)

Now you can search based on bitfied values that have been separated out for you. The query for the value & 9 = 9 on this table would look like this:

SELECT ...
WHERE v0=1 AND v3=8

The value to which you compare vN is 2N, so for v0 it's 1, for v1 it's 2, for v2 it's 4, for v3 it's 8, and so on.

Upvotes: 0

dean
dean

Reputation: 10098

Index seek may or may not be more efficient than scan. Scaning a narrow covering index will probably be faster than seek&lookup, except for the highest selective queries.

A single bit column can only be 0 or 1; it's selectivity is 50% on average, so probably not a very good candidate for indexing. You can create an index, but taking into account very high cost of lookups it may or may not be used by the optimizer.

Combining it into a bitfield might be better solution, even more if you know your data and order the bits cleverly, from higher selectivity to lower. To illustrate the idea:

select * from MyTable where value < @valueToCompare and value & @valueToCompare = value

Upvotes: 1

T McKeown
T McKeown

Reputation: 12857

SQL Server will not use indexes to perform bitwise operations. You are out of luck, however you could maybe start by first creating a smaller resultset via a CTE or derived table and then perform the operation on a smaller set.

Upvotes: 0

Related Questions