December 14, 2008 at 3:43 am
Hi,
In TSQL you can perform a bitwise operation on integer data, for example:
[font="Courier New"]print 3 & 7
result=3[/font]
I want to perform bitwise operations on a word of 96 bits (if you want to know why: there are 96 quarters of an hour in one day, and I'm doing some calendar stuff). The biggest integer datatype is bigint, that has 64 bits. Is there a way to solve this?
Thanks,
Raymond
December 15, 2008 at 3:39 am
I don't think this is possible in pure T-SQL. There might be a way to do it with CLR, storing the value in a large decimal field and using an assembly to compare bit masks - I'm afraid I don't know enough about .Net to be able to say whether this would work.
Alternatively, is splitting the day into 2 twelve-hour segments and storing your bitmask across two bigints an option?
December 15, 2008 at 3:50 am
Hi,
Thanks for your reply. Indeed using two bigint's is a solution, but it will make coding a bit more complicated, and of course execution would take more time.
Ray
December 15, 2008 at 6:45 am
In my humble opinion, uses bit calculation in t-sql is the completely wrong way to go at all.
What is your objective? What kind of calculations do you do that makes you think bit math is easier and more maintanable than other set-based solutions?
N 56°04'39.16"
E 12°55'05.25"
December 15, 2008 at 7:06 am
Hi,
I have to grab a number of salesmen's agenda's (lets say 20), that cover 30 - 60 days, and find the spots where I can add a new appointment in either agenda. In other words, you get a view of all available times, regardless which salesman.
By putting one day per salesman into a datatype "megaint" (an imaginary 12 byte datatype), for each day I would only have to "AND" the 20 megaints to find the unavailable places in all agenda's combined.
Afaik, these binary calculations are always faster than any other solution.
December 15, 2008 at 11:04 am
A bigint gives you 16 hours a day to schedule appointments for your salesmen. If that is not enough time, then your company has a serious problem and it's not related to IT.
Granted, if you had 96 slots available, then you could cut this part of your design phase short. But since 64 slots are more than sufficient to handle any real-world specifications, you may actually have to spend a little more time coming up with an intelligent design. Even if your company has shifts so there will be 24-hour coverage, this is just a simple design problem.
As these design shortcuts have a nasty habit of coming back later and biting us in the end, think "opportunity" rather than "inconvenience."
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
December 15, 2008 at 11:37 am
Bigint is the largest data type that you can do bit-wise operations on directly and/or atomically.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 15, 2008 at 11:55 am
R. van Laake (12/15/2008)
I have to grab a number of salesmen's agenda's (lets say 20), that cover 30 - 60 days, and find the spots where I can add a new appointment in either agenda. In other words, you get a view of all available times, regardless which salesman.By putting one day per salesman into a datatype "megaint" (an imaginary 12 byte datatype), for each day I would only have to "AND" the 20 megaints to find the unavailable places in all agenda's combined.
The datatype that you want is BINARY (or VARBINARY), not CHAR. You still cannot use the bit-wise operators on BINARY, but you can cast it back and forth to INT or BIGINT to do the operations then back to BINARY for storage, comparison, concatenation, etc.
Afaik, these binary calculations are always faster than any other solution.
Not hardly. In the 3GL/OO world where microscopic CPU optimizations rule, this is usually true. Not so in our world...
In the world of databases, including SQL Server, CPU is a tertiary performance factor. The real issues are first data access, including IO's and Indexing, and secondly memory management issues. Once you have these issues optimized then you can start to worry about how much better a bit-encoded XOR might be than nested CASE's or a CHARINDEX on character or byte encoded strings. Since bit-encoded data is very resistant to productive indexing (among other things), it frequently does not make the grade. But then again, sometime it is the best way for a particular situation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 15, 2008 at 12:56 pm
Seems to me that your real problem is a design issue.
Supposed you have a table of "scheduled" appointments table with (begin_datetime, end_date, salesman_id, appointment_id)
Whatever is not on that table means that such salesman is "free" for the requested time period.
No need for bitwise operations, no need for storage, no complicated math or searches, you only store actual scheduled stuff.
HTH
* Noel
December 16, 2008 at 1:19 am
This is how you should solve your problem. The suggested algorithm is very flexible and allows you to set any valid value for @SlotSize (1-1440).
DECLARE@Sample TABLE
(
empID INT,
fromTime DATETIME,
toTime DATETIME
)
INSERT@Sample
SELECT1, '2008-11-01 09:20', '2008-11-01 10:05' UNION ALL
SELECT1, '2008-11-01 08:00', '2008-11-01 09:00' UNION ALL
SELECT2, '2008-11-02 16:00', '2008-11-02 17:40' UNION ALL
SELECT2, '2008-11-02 11:00', '2008-11-02 11:15' UNION ALL
SELECT3, '2008-11-01 12:00', '2008-11-01 13:30' UNION ALL
SELECT4, '2008-11-03 08:00', '2008-11-03 09:00' UNION ALL
SELECT4, '2008-11-01 10:00', '2008-11-01 17:00'
DECLARE@SlotSize SMALLINT
SELECT@SlotSize = 30
SELECTt.fromTime,
t.toTime,
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 1 THEN 1 ELSE 0 END) AS [1],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 2 THEN 1 ELSE 0 END) AS [2],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 3 THEN 1 ELSE 0 END) AS [3],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 4 THEN 1 ELSE 0 END) AS [4],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 5 THEN 1 ELSE 0 END) AS [5],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 6 THEN 1 ELSE 0 END) AS [6],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 7 THEN 1 ELSE 0 END) AS [7],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 8 THEN 1 ELSE 0 END) AS [8],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 9 THEN 1 ELSE 0 END) AS [9],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 10 THEN 1 ELSE 0 END) AS [10],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 11 THEN 1 ELSE 0 END) AS [11],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 12 THEN 1 ELSE 0 END) AS [12],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 13 THEN 1 ELSE 0 END) AS [13],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 4 THEN 1 ELSE 0 END) AS [14],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 15 THEN 1 ELSE 0 END) AS [15],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 16 THEN 1 ELSE 0 END) AS [16],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 17 THEN 1 ELSE 0 END) AS [17],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 18 THEN 1 ELSE 0 END) AS [18],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 19 THEN 1 ELSE 0 END) AS [19],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 20 THEN 1 ELSE 0 END) AS [20],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 21 THEN 1 ELSE 0 END) AS [21],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 22 THEN 1 ELSE 0 END) AS [22],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 23 THEN 1 ELSE 0 END) AS [23],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 24 THEN 1 ELSE 0 END) AS [24],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 25 THEN 1 ELSE 0 END) AS [25],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 26 THEN 1 ELSE 0 END) AS [26],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 27 THEN 1 ELSE 0 END) AS [27],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 28 THEN 1 ELSE 0 END) AS [28],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 29 THEN 1 ELSE 0 END) AS [29],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 30 THEN 1 ELSE 0 END) AS [30],
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 31 THEN 1 ELSE 0 END) AS [31]
FROM(
SELECTCONVERT(CHAR(8), DATEADD(MINUTE, @SlotSize * Number, 0), 8) AS fromTime,
CASE
WHEN @SlotSize * Number + @SlotSize >= 1440 THEN '24:00:00'
ELSE CONVERT(CHAR(8), DATEADD(MINUTE, @SlotSize * Number + @SlotSize, 0), 8)
END AS toTime
FROMmaster..spt_values
WHEREType = 'P'
AND Number <= 1440 / @SlotSize
AND Number * @SlotSize < 1440
) AS t
LEFT JOIN@Sample AS s ON s.fromTime >= '2008-11-01'
AND s.fromTime < '2008-12-01'
AND CONVERT(CHAR(8), s.fromTime, 8) < t.toTime
AND CONVERT(CHAR(8), s.toTime, 8) >= t.fromTime
GROUP BYt.fromTime,
t.toTime
ORDER BYt.fromTime
N 56°04'39.16"
E 12°55'05.25"
December 16, 2008 at 1:20 am
Output from above suggestion when @SlotSize is 120 minutes is
fromTimetoTime123456
00:00:0002:00:00000000
02:00:0004:00:00000000
04:00:0006:00:00000000
06:00:0008:00:00000000
08:00:0010:00:00201000
12:00:0014:00:00200000
14:00:0016:00:00100000
16:00:0018:00:00110000
18:00:0020:00:00000000
20:00:0022:00:00000000
22:00:0024:00:00000000
N 56°04'39.16"
E 12°55'05.25"
December 16, 2008 at 2:20 am
Hi there,
Thanks for all your input.
I would like to comment on one reply:
A bigint gives you 16 hours a day to schedule appointments for your salesmen. If that is not enough time, then your company has a serious problem and it's not related to IT.
Quite often I read remarks/opinions like this on forums. I fail to see the usefulness. I always avoid explaining the (complicated) real world issue and simplify the situation drastically, in order to keep the IT question as simple as possible. In this case: I am building a webbased application for customers that use the calender. So it's not my company that has the appointments, but my customers. Some of them work very early (transport sector), others work very late (a club). Also important: the commercial value of my program increases with a 24/7 calendar (even if none of my clients would actually use it).
Not hardly. In the 3GL/OO world where microscopic CPU optimizations rule, this is usually true. Not so in our world...
In the world of databases, including SQL Server, CPU is a tertiary performance factor. The real issues are first data access, including IO's and Indexing, and secondly memory management issues.
I agree that data access is most important... you mention IO's, I am not familiar how to tweak that... do you maybe have a link that discusses this in depth?
Hats of to Peso... I tried the code and it works very well. I must dive into it deeper because at first glance there are some things I don't understand. But first impression is: this is the way to go! Thanks!
December 16, 2008 at 3:11 am
Thanks.
Keep us informed about futher testing and progress.
N 56°04'39.16"
E 12°55'05.25"
December 16, 2008 at 3:15 am
EDITS:
Second DATEPART(DAY, s.FromTime) = 4 should be DATEPART(DAY, s.FromTime) = 14
AND Number <= 1440 / @SlotSize AND Number * @SlotSize < 1440 can be shortened down to AND Number * @SlotSize < 1440
N 56°04'39.16"
E 12°55'05.25"
December 16, 2008 at 3:44 am
Hi Peso,
Got the edits!
I have never used the table master..spt_values before, so I'm a bit in the dark how it all exactly works. I do understand that master..spt_values is used to "get" the rows.
I am working on the code right now, making it work on my actual table called "agenda". So far it works great, a 10^6 thanks again!
Keeping you posted,
Ray
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply