Post reply

Table Pool/Lazy Pool take 87% of a query

  • November 26, 2007 at 1:29 pm

    #184337

    I am tuning a slow query which join a dozen also tables. On of table is of table scan and then Table Pool/Lazy Pool operation before Nest Loop join with another table (index scan). The Table Pool/Lazy Pool operation is the most expensive one of the query, cost 87%. Now, my questions:

    1) How can I make it better

    2) Why the Table Pool/Lazy Pool is used here. Other joins do not

    3) Why SQL Server us Table Pool/Lazy Pool at all

    Thanks

    George

  • November 26, 2007 at 4:38 pm

    #754332

    I got the answer.

    This Table Pool/Lazy Pool is to do with Nest Loop join. The query we did has a LOOP JOIN hint, which cause the Pool operation. Using HASH JOIN hint ( simply remove the hint, SQL Server pick up HASH JOIN) we did not see the Pool op., and executing time reduces by a fact of 18.

    About the Table Pool/Lazy Pool, it means to copy inopput data to a temp spool table (for example, by a Nestd Loop operation). When rewinding, the spooled data is used instead rescan the table.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply