SQL Server Performance Forum – Threads Archive
binary time mask compare
hi all, I have stored data in a timemask based on users access patterns over a 24 hour period in 15 minutes bits. So I have a string of 96 characters (representing 15 minutes blocks) with either 1 or 0 (as to if they were on or not). I have a particular session query mask that has an arbitrary access pattern that will like to find out which users have accessed during this time. This is another string of 96 characters with 1 and 0s for the pattern. I would like to select records based on the session query mask from the activity table with the other mask. I have stored the mask as binary. My problem is that I cant compare binary to binaryie
select *
from activity
where TimeMask & @sessionmask > 0 where @session mask is binary I have used the following to compensate: select *
from activity
where substring(TimeMask,1,6) & cast(substring(@sessionmask,1,6) as bigint) > 0
or substring(TimeMask,7,6) & cast(substring(@sessionmask,7,6) as bigint) > 0 The reason I am using this method is for speed. I have a few hundred thousand records and if I was to unpivot the data into records and compare another table I would be talking millions of records of data. Any thoughts on the binary problem would be appreciated or any alternative methods will be considered. thanks
What datatype is the TimeMask column? If (n)char or (n)varchar, why not use an expression with wildcards?
the TimeMask is a binary(12) so is the @sessionmask. What do you mean by expression with wildcards. Can you supply an example. thanks
If you have a CHAR(96) column with only "0" and "1" characters, you could filter with wildcards for irrelevant characters "_" (one character) and "%" (any number of characters): WHERE mycolumn LIKE ‘1%’ — first character must be a "1", remaining ones irrelevant
WHERE mycolumn LIKE ‘_0%’ — first character irrelevant, second character must be a "0", remaining ones irrelevant
WHERE mycolumn LIKE ‘%0’ — last character must be a "0", previous ones irrelevant
WHERE mycolumn LIKE ‘%1_’ — last character can be anything, the one before the last must be a "1", previous ones irrelevant Obviously this can become pretty ugly for 96 characters, and mistakes will be only too easy to make. Another option would be to use the SUBSTRING function, which pinpoints the exact position you”re looking for. WHERE SUBSTRING(mycolumn, 15, 1) = ‘0’ — 15th character must be a ‘0’
WHERE SUBSTRING(mycolumn, 15, 2) = ’01’ — 15th character must be a ‘0’, 16th character a ‘1’ Finally, you can also use LIKE and wildcards for the SUBSTRING expression. **** Another option might be to store the criteria that you’re using in your WHERE clause in a table (the parameters for the SUBSTRING), and to JOIN that lookup table to your facts table on the SUBSTRINGs to apply the filtering.
EDIT: I don’t know whether this function exists in SQL 2005. But if it doesn’t, this is a perfect task for a CLR function. There are two undocumented UDFs that will help a great deal:
fn_replinttobitstring(int)
fn_replbitstringtoint(char(32)) The first converts a signed int to a char(32) string of 1’s and 0’s. The second is the inverse operation. Whether signed or not does not matter for your purpose because all you need is a way to use the bitwise AND "&" operator to compare the data to the mask. So segment your char(96) into three char(32)’s, then convert each to an integer with the function, then use bitwise AND "&" with the similarly converted mask. If the result of the bitwise AND gives back the mask value, then the mask matches the data. Perform the comparison for all three segments to get the final answer. Keith Payne
Technical Marketing Solutions
www.tms-us.com
Actually it’s probably a better idea to use 3 int columns in the database and only convert it to char(96) when you need to display it. Then you can use simple bitwise operators wherever you need to. Keith Payne
Technical Marketing Solutions
www.tms-us.com
Thanks for the info Adriaan. I had originally had this inplace with char strings and doing something similar but the amount of time it took to query was too long hence the conversion to bit masks. Keith I had a look at the UDFs but they are not setup in SQL 2005. I also tried to break the usage times into 4 equal part int fields but again the query was slow. so I have been content with the original solution :
SELECT a.UserID, a.Weight, a.DayMask, a.TimeMask,
dbo.CountSpots(substring(a.TimeMask, 1,6) & @TimeMask16) +
dbo.CountSpots(substring(a.TimeMask, 7,6) & @TimeMask76) as CountSpots
INTO #RequiredData
FROMActivity_Weighta,
Demo_Userh
WHEREa.UserID = h.UserID
ANDh.DemoGUID = @DemoGUID
ANDa.DayMask & @DayMask > 0
AND (substring(a.TimeMask, 1,6) & @TimeMask16 > 0
OR substring(a.TimeMask, 7,6) & @TimeMask76 > 0 )
thanks
]]>