November 5, 2008 at 7:26 pm
Guess that's where the term "It Depends" comes from... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2008 at 7:43 pm
hehe.....but what happens when you make it less balanced....
SET STATISTICS TIME ON
SELECT RowNum,SomeInt,SomeLetters2,SomeCSV,SomeMoney,SomeDate,SomeHex12
FROM dbo.JBMTest
WHERE SomeInt IN (2,4,6,8,10)
SELECT RowNum,SomeInt,SomeLetters2,SomeCSV,SomeMoney,SomeDate,SomeHex12
FROM dbo.JBMTest
WHERE SomeInt NOT IN (1,3,5,7,9,
11,13,15,17,19,
21,23,25,27,29,
31,33,35,37,39,
41,43,45,47,49,
51,53,55,57,59,
61,63,65,67,69,
71,73,75,77,79,
81,83,85,87,89,
91,93,95,97,99,
12,14,16,18,20,
22,24,26,28,30,
32,34,36,38,40,
42,44,46,48,50,
52,54,56,58,60,
62,64,66,68,70,
72,74,76,78,80,
82,84,86,88,90,
92,94,96,98,100)
SET STATISTICS TIME OFF
SET STATISTICS TIME ON
SELECT RowNum,SomeInt,SomeLetters2,SomeCSV,SomeMoney,SomeDate,SomeHex12
FROM dbo.JBMTest
WHERE SomeInt not IN (2,4,6,8,10)
SELECT RowNum,SomeInt,SomeLetters2,SomeCSV,SomeMoney,SomeDate,SomeHex12
FROM dbo.JBMTest
WHERE SomeInt IN (1,3,5,7,9,
11,13,15,17,19,
21,23,25,27,29,
31,33,35,37,39,
41,43,45,47,49,
51,53,55,57,59,
61,63,65,67,69,
71,73,75,77,79,
81,83,85,87,89,
91,93,95,97,99,
12,14,16,18,20,
22,24,26,28,30,
32,34,36,38,40,
42,44,46,48,50,
52,54,56,58,60,
62,64,66,68,70,
72,74,76,78,80,
82,84,86,88,90,
92,94,96,98,100)
SET STATISTICS TIME OFF
So - it can be a bit of a crap chute. Still - it looks to me that IN seems to be a LITTLE faster (on average) than NOT IN. Meaning - IN can be a little slower, but it can also be a LOT faster, and NOT IN.
I get a feeling not all moving parts have been accounted for....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2008 at 7:44 pm
Jeff Moden (11/5/2008)
Guess that's where the term "It Depends" comes from... :hehe:
Agreed!!!!!!!!!!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2008 at 3:53 am
So, what happens if you create a table of the employee types with an extra column to mark the ones you want, ie columns EmpType int and InQuery bit, populate the employee type values, then update the table to set the second column to 1:
UPDATE T1 set InQuery = 1 WHERE EmpType IN (list)
Then run the query with
select field_list from Employees e join T1 on e.EmpType = T1.EmpType
where T1.InQuery = 1
Would that improve performance?
November 6, 2008 at 8:31 am
If you account for the extra time to perform that update, I suspect there's a net loss in performance, but as I don't quite have a server just yet to test on (sometime in the next week, hopefully, and possibly as soon as this weekend), I can't be sure.
Steve
(aka smunson)
:):):)
Ross McMicken (11/6/2008)
So, what happens if you create a table of the employee types with an extra column to mark the ones you want, ie columns EmpType int and InQuery bit, populate the employee type values, then update the table to set the second column to 1:UPDATE T1 set InQuery = 1 WHERE EmpType IN (list)
Then run the query with
select field_list from Employees e join T1 on e.EmpType = T1.EmpType
where T1.InQuery = 1
Would that improve performance?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply