"Lazy Spool" problem
-
A simple query with a left join sues to run quickly on SQL Server, but only with one parameter changed, it takes almost a minute. The query is something like:
SELECT Table1.*, Table2.*
FROM Table1 Left JOIN Table2 ON Table1.ID=Table2.Table1ID
WHERE (Table1.Col1=38 OR Table1.Col1=-1) AND Table1.Col2=0That produces around 280 lines of output in a second. When we change
Col1=38toCol1=114, it produces some 320 lines of output, in almost a minute! We looked at the execution plan, and in the second case we found an extra "Lazy Spool" not present in the first query, which was estimate to cost a percent. When I looked at that "Lazy Spool", I found a striking difference between estimated and actual values: Actual Number of Rows:75124744 Estimated Number of Rows: 708724 That is more than a hundred times as much as estimated! Actual Rebinds was 1, and Actual Rewinds 105! Can you guess what went wrong here? -
A simple query with a left join sues to run quickly on SQL Server, but only with one parameter changed, it takes almost a minute. The query is something like:
SELECT Table1.*, Table2.*
FROM Table1 Left JOIN Table2 ON Table1.ID=Table2.Table1ID
WHERE (Table1.Col1=38 OR Table1.Col1=-1) AND Table1.Col2=0That produces around 280 lines of output in a second. When we change
Col1=38toCol1=114, it produces some 320 lines of output, in almost a minute! We looked at the execution plan, and in the second case we found an extra "Lazy Spool" not present in the first query, which was estimate to cost a percent. When I looked at that "Lazy Spool", I found a striking difference between estimated and actual values: Actual Number of Rows:75124744 Estimated Number of Rows: 708724 That is more than a hundred times as much as estimated! Actual Rebinds was 1, and Actual Rewinds 105! Can you guess what went wrong here?Bernhard Hiller wrote:
Can you guess what went wrong here?
Is that your question or is this a quiz?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
A simple query with a left join sues to run quickly on SQL Server, but only with one parameter changed, it takes almost a minute. The query is something like:
SELECT Table1.*, Table2.*
FROM Table1 Left JOIN Table2 ON Table1.ID=Table2.Table1ID
WHERE (Table1.Col1=38 OR Table1.Col1=-1) AND Table1.Col2=0That produces around 280 lines of output in a second. When we change
Col1=38toCol1=114, it produces some 320 lines of output, in almost a minute! We looked at the execution plan, and in the second case we found an extra "Lazy Spool" not present in the first query, which was estimate to cost a percent. When I looked at that "Lazy Spool", I found a striking difference between estimated and actual values: Actual Number of Rows:75124744 Estimated Number of Rows: 708724 That is more than a hundred times as much as estimated! Actual Rebinds was 1, and Actual Rewinds 105! Can you guess what went wrong here?Bernhard Hiller wrote:
Can you guess what went wrong here?
Old statistics?
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Bernhard Hiller wrote:
Can you guess what went wrong here?
Is that your question or is this a quiz?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
mark merrens wrote:
Is ... this a quiz?
Does it look like that? Perhaps it is. But you did not win the prize of my 5.
-
Bernhard Hiller wrote:
Can you guess what went wrong here?
Old statistics?
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
Thanks for this idea. The statistics did not bring improvement. Eventually an index on column Table1ID of Table2 brought the solution.