Slow Query - Table Spool - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow Query - Table Spool

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-04-21 : 11:35:31

[code]SELECT
Table1....,
...,
Table2....,
...,
HasAttachment = IsNull((select max(ISNULL(n.HasAttachment, 0))
from Note N
where N.TCRID = Table1.ID), 0)
FROM
Table1 Inner JOIN
Table2 ON ...
WHERE
...[/code]
This is my slow query and I dotted out the boring details.

From the execution plan, nearly all the time is spent on the Column Query involving the Note table, which is large - 2 million rows. Execution Plan says it is using a Table Spool (Lazy Spool) for this. If I understand correct, that means it is evaluating the Select on Note table in its entirety first, then later joining it to the main query?

In this case, only 4 rows are returned, so I think it would be much faster if SQL Server evaluated the main query first and then joined to the Note table later. Is there a way I could force SQL Server to do it that way?

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 11:40:11

Does this make any diffrence?


HasAttachment = IsNull((select max(ISNULL(n.HasAttachment, 0)) 
      from Note N
      where N.TCRID = Table1.ID
            AND n.HasAttachment IS NOT NULL
), 0)

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 11:45:06


SELECT ...
       HasAttachment  = IsNull(MAX_HasAttachment, 0),
       ...
FROM   ...
LEFT OUTER JOIN
(
    SELECT N.TCRID,
           ISNULL(max(n.HasAttachment), 0) AS [MAX_HasAttachment]
    from Note AS N
    where ... Needs duplication of all restrictive parts of main WHERE clause here, to reduce the number of rows ...
          ... (which may also need duplicates of the JOINed tables in the main query ...
    GROUP BY N.TCRID
) AS N

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-04-21 : 11:55:06

quote:

Originally posted by Kristen

Does this make any diffrence?


HasAttachment = IsNull((select max(ISNULL(n.HasAttachment, 0)) 
      from Note N
      where N.TCRID = Table1.ID
            AND n.HasAttachment IS NOT NULL
), 0)

Yes, that worked! Thanks.

And unfunnily enough, this also works:

   ...,
   HasAttachment = IsNull((select max(ISNULL(n.HasAttachment, 0)) 
      from Note N
      where N.TCRID = Table1.ID and N.TCRID > -1), 0)
   ...

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 12:53:29

Same thing I reckon: The STATS on the Index for NOT NULL and >-1 probably find exactly the same statistical data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources