How do I group tsql set by a flag bigint column

Question

0.00/5 (No votes)

See more:

Hi,

I'm working on a project where efficiency of the search functionality is critical.

I have several flag columns (like enum flags in c#). Searching on this data is super fast (3 milliseconds round trip) but I've come-a-cropper now I have to perform group counts.

So, I have an item 'A' that contains Red (1), White (8) and blue (64) so the 'Colours' column holds the number 73.

To search I can search for items with red with this

SQL

Declare @colour int
set @colour = 1
Select * from Items where (Colour & @colour) > 0

That works great. Now I have to group it (also super fast)

so if I have 8 items in total, 5 contain red, 3 contain white and 7 contain blue the results would look like:

Colour      Qty
1           5
8           3
64          7

( I don't have to worry about the name )

So: Is there any way I can take the number 73 and bitwise split it into groups?

(Part 2: How do I translate that into Linq to SQL?)

Any advise would be appreciated
Thanks ^_^

What I have tried:

Tried writing the login in Linq but forgot that it would have no translation into TSQL.

I'm thinking about scrapping this column and turning it back into a linking table, but I really don't want to have to redesign this thing for a third time >_<

Posted 25-Jun-18 0:07am

Andy Lanng

Updated 25-Jun-18 0:56am

Add a Solution

2 solutions

Solution 2

SQL

SELECT VAL, COUNT(*)
FROM (
	SELECT 
		COLOR AS COLOR_VAL, 
		COLOR & 1 AS [1],
		COLOR & 2 AS [2],
		COLOR & 4 AS [4],
		COLOR & 8 AS [8],
		COLOR & 16 AS [16],
		COLOR & 32 AS [32],
		COLOR & 64 AS [64],
		COLOR & 128 AS [128]
	FROM (SELECT 73 AS COLOR UNION SELECT 53 UNION SELECT 91) AS COLORS) AS SUMMARY
UNPIVOT  
   (VAL FOR COLOR IN   
      ([1], [2], [4], [8], [16], [32], [64], [128])  
) AS UP
WHERE VAL > 0 
GROUP BY VAL

The bold part is where you actual select for your color values comes in...

Posted 25-Jun-18 0:57am

Kornfeld Eliyahu Peter

Comments

Andy Lanng 26-Jun-18 4:05am

ooh - thing is: the number of options (flags) is anywhere between 10 and 60 (hence bigints). Does this still stand up for performance pivoting so many columns?

Kornfeld Eliyahu Peter 26-Jun-18 4:09am

As long as it is a fixed number of values... You should put it into a SP, so it will be pre-compiled and that will give you good performance...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2018-06-25T00:51:00

Ok - I think I've worked out the best solution:

I tries a view with a cte:

SQL

with cte as (
	select cast(1 as bigint) as flag, 1 pow
	union all
	select POWER(cast(2 as bigint),pow), pow + 1
	from cte
	where flag < POWER(cast(2 as bigint),62)
)
, cte2 as (
	select flag from cte
	union select -9223372036854775808
)

but that was too slow so now I have made it into a static table. I join with a bitwise '&':

SQL

select Flag, Count(*)
From FlagValues fv 
inner join Items i on (fv.Flag & i.Colour)

Much faster ^_^