Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise 10

Status
Not open for further replies.
Nice article. Thanks.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
That's why I gave you a star. It's easy to find interesting articles. It's not easy to write one!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Very interesting article :).. I was also wondering about the name...

Sunil
 
Interesting technique.

It will only work for lookup tables with less than 64 items in as this is the number of bytes in a bigint. But lists this small are quite common so it should still be useful.
 
Good stuff Denis.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
I just clicked on the link to se what this was.

After I read it, I saw the Title "Denis Gobo".
I susuppected that maybee this was SQLDenis from tek-tips.
Went back to read the response and of course it was SQL Denis.
Good article.

George
 
Interesting. Thank you
 
A * for you Denis.

[monkey][snake] <.
 
Taking off on what one commenter said and switching to a bitwise and, I wonder if this performs any better for you, Denis:

Code: 
SELECT COUNT(*)
FROM BitwiseCustomerMusicChoice 
WHERE (
   SELECT id FROM BitwiseMusicChoice
   WHERE ChoiceDescription = 'Classical'
) & MusicChoiceID <> 0
Unless I'm being hasty and getting columns confused, I think this should work. Comparing to zero could possibly give better performance than comparing to MusicChoiceID.

The commenter said that doing the select as a pre-query helps, so I did it right in the query instead of declaring a variable.
 
here is what I get

No difference,same reads


Code: 
SELECT COUNT(*)
FROM BitwiseCustomerMusicChoice 
WHERE (
   SELECT id FROM BitwiseMusicChoice
   WHERE ChoiceDescription = 'Classical'
) & MusicChoiceID <> 0


SELECT COUNT(*)
FROM BitwiseCustomerMusicChoice cmc
JOIN BitwiseMusicChoice bmc ON bmc.ID | MusicChoiceID =MusicChoiceID 
WHERE bmc.ChoiceDescription ='Classical'

Code: 
Table 'BitwiseCustomerMusicChoice'. Scan count 1, logical reads 42, physical reads 0, read-ahead reads 0.
Table 'BitwiseMusicChoice'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

(1 row(s) affected)

Table 'BitwiseMusicChoice'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'BitwiseCustomerMusicChoice'. Scan count 1, logical reads 42, physical reads 0, read-ahead reads 0.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions
 
Denis,

In my experience I have found that reads only indicates the data access portion of query cost: it doesn't reflect CPU time.

In a query that had very complex operations performed but selected few rows (I was doing some really funky datetime manipulation) the execution plan and reads seemed to be superior.

But after much analysis and finally observing the CPU cost, I altered my query to do more work with I/O and less with calculation, and total query time went down significantly. Even though the execution plans had something like 25% 75%, the second query executed in maybe half the time.

I'm sure that in a multiuser OLTP environment the majority of performance cost is in I/O, but there are times when the CPU gets so saturated that the I/O part is just sitting there doing squat, and trading some over is important.

I think you're right that in this case it makes little difference performance-wise because the difference in CPU time is minuscule. I'll admit that personal style preference makes me choose the & and <> 0 method (I think it's clearer).


An example, not really related here, but perhaps interesting to people:

Code: 
SELECT orderid, dbo.MyBigOldComplicatedValuationFunction(orderid) FROM Blah WHERE ReadyToProcess=1

SELECT
   orderid, Sum(Value)
FROM
   Blah B
   INNER JOIN Orders O ON B.orderid = O.orderid
   INNER JOIN This ...
   INNER JOIN That ...
GROUP BY
   orderid
The second query is going to show up with a much bigger execution cost, but a significant portion of the work of the first query is hidden inside the function, which could be CPU intensive or even I/O intensive. The second query may perform many times better.
 
So in terms of optimization, if many of these queries were being submitted at once, a 22% CPU usage improvement could be significant?

Probably not in this case, but maybe the principle of it could help someone some day.
 
Its' getting to read discussions like this that makes me enjoy Tek-Tips so much :)

Ignorance of certain subjects is a great part of wisdom
 
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
952
pjw001
pjw001
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
619
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
How te filter by date ?
Replies
9
Views
565
dhookom
dhookom
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
583
bharons
bharons
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
877
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top