January 27, 2009 at 7:27 am
Greetings all. I am working with an extremely poorly written view, and I know that because I wrote it, and I am trying to improve its' performance. I ran it with the execution plan turned on, and the biggest thing was a step called 'Row Count Spool/Lazy Spool', which accounts for 71% of cost. Now, it says it 'stores the data from the input into a temporary table in order to optimize rewinds.' And I have no idea what that means. My only clue is that this step is directly after a nasty case statement, which accounts for 28% of cost. I am thinking maybe it has something to do with the case statement? The BOL description seems to imply it has something to do with my NOT IN statements in the WHERE clause. So, in laymen's terms, can anyone explain what it is doing? Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 27, 2009 at 7:36 am
Ok, I'm fairly certain its the NOT IN clause that is the hog. Mainly because it shows the code in the hover box that pops up in the execution plan, which, of course, I realized about 5 seconds after I posted the question.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 29, 2009 at 10:07 am
Aren't left joins and NOT EXIST statements more efficient than NOT IN statements? I don't know if I made that up or read it somewhere. I usually use a query like this:
I believe that this can cause the optimizer to search for a match for each record in myTable
-- option 1 NOT IN
select foo, bar, baz from myTable where baz not in
(select baz from myOtherTable)
-- Option 2 NOT EXISTS
select foo, bar, baz from myTable A where not exists
(select baz from myOtherTable B where A.baz = B.baz)
-- Option 3 LEFT JOIN
-- I prefer this one
select foo, bar, baz from myTable A left join myOtherTable B
ON A.baz = B.baz where B.baz IS NULL
January 29, 2009 at 10:18 am
souLTower (1/29/2009)
Aren't left joins and NOT EXIST statements more efficient than NOT IN statements? I don't know if I made that up or read it somewhere. I usually use a query like this:I believe that this can cause the optimizer to search for a match for each record in myTable
-- option 1 NOT IN
select foo, bar, baz from myTable where baz not in
(select baz from myOtherTable)
-- Option 2 NOT EXISTS
select foo, bar, baz from myTable A where not exists
(select baz from myOtherTable B where A.baz = B.baz)
-- Option 3 LEFT JOIN
-- I prefer this one
select foo, bar, baz from myTable A left join myOtherTable B
ON A.baz = B.baz where B.baz IS NULL
Definitely option 3! Both 1 and 2 the inner query relates to the outer query, which means usually bad performance but at least less readability.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 29, 2009 at 12:54 pm
Actually NOT EXISTS typically performs not worse than LEFT JOIN and in some cases it beats LEFT JOIN quite significantly.
That's why I use NOT EXISTS as a standard.
_____________
Code for TallyGenerator
January 29, 2009 at 6:00 pm
Thanks for the tip guys, I'll have to do some experimenting when I get back to work.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 29, 2009 at 10:25 pm
r.hensbergen (1/29/2009)
souLTower (1/29/2009)
Aren't left joins and NOT EXIST statements more efficient than NOT IN statements? I don't know if I made that up or read it somewhere. I usually use a query like this:I believe that this can cause the optimizer to search for a match for each record in myTable
-- option 1 NOT IN
select foo, bar, baz from myTable where baz not in
(select baz from myOtherTable)
-- Option 2 NOT EXISTS
select foo, bar, baz from myTable A where not exists
(select baz from myOtherTable B where A.baz = B.baz)
-- Option 3 LEFT JOIN
-- I prefer this one
select foo, bar, baz from myTable A left join myOtherTable B
ON A.baz = B.baz where B.baz IS NULL
Definitely option 3! Both 1 and 2 the inner query relates to the outer query, which means usually bad performance but at least less readability.
As Sergiy mentioned, despite how they look, both the in and exists will usually tie and occasionally beat the left join. Usually the difference is pretty minimal. There was actually a lengthy thread about this a month or two ago that definitely surprised me. That said, I still personally use left joins out the wazoo over exists because they're easier for me to read. I do use IN/NOT IN sometimes though.
January 30, 2009 at 7:07 am
For what it's worth:
-- Table A rows - 2412928
-- Table B rows - 2286023
-- The query was to search for records in table where a key value did not
-- exist in table B
Method 1 - Not In
SQL Server parse and compile time:
CPU time = 18 ms, elapsed time = 18 ms.
SQL Server Execution Times:
CPU time = 14291 ms, elapsed time = 26819 ms.
Profiler
CPU - 14431 Reads - 45348 Writes - 0 Duration - 26700
Method 2 - Not Exists
SQL Server parse and compile time:
CPU time = 20 ms, elapsed time = 52 ms.
SQL Server Execution Times:
CPU time = 14711 ms, elapsed time = 28897 ms.
Profiler
CPU - 14480 Reads - 45344 Writes - 0 Duration - 29253
Method 3 - Left Join
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 15 ms.
SQL Server Execution Times:
CPU time = 16113 ms, elapsed time = 28675 ms.
Profiler
CPU - 16054 Reads - 45344 Writes - 0 Duration - 26040
January 31, 2009 at 4:18 am
26 seconds for work out 2 mil rows???
There must be no indexes, no keys...
_____________
Code for TallyGenerator
February 2, 2009 at 5:25 am
You are correct. It's a worst case scenario. 🙂
February 2, 2009 at 12:26 pm
If use such scenarios why pay so big money for SQL Server licenses?
Flat files will work as good.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply