StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhen will a FAST_FORWARD cursor have a work table (and is this something to avoid)?
text
Body
copied!<h3>Background</h3> <p>I noticed whilst experimenting with running total queries that sometimes the estimated plan just shows a "Fetch Query" </p> <p><img src="https://i.stack.imgur.com/pL51u.png" alt="Fetch"></p> <p>and the actual plan shows repeated Fetches from the Clustered Index Scan</p> <p><img src="https://i.stack.imgur.com/yvvBQ.jpg" alt="Fetch Scan"></p> <p>on other occasions (e.g when adding a <code>TOP</code> to the query) the estimated plan shows a "Population Query" stage that populates a work table</p> <p><img src="https://i.stack.imgur.com/aARNW.png" alt="Fetch and Populate"></p> <p>With the actual plan showing a clustered index scan to populate the work table then repeated seeks against that work table.</p> <p><img src="https://i.stack.imgur.com/lwzRC.jpg" alt="Seeks"></p> <h3>Question</h3> <ol> <li>What criteria does SQL Server use in choosing one approach over the other?</li> <li>Would I be right in thinking that the first method (without the additional work table population step) is more efficient?</li> </ol> <p>(Bonus question: If anyone could explain why each scan in the first query counts as 2 logical reads that might be quite enlightening too)</p> <h3>Additional Information</h3> <p>I have found <a href="http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx" rel="noreferrer">this article here</a> which explains that <code>FAST_FORWARD</code> cursors can either use a dynamic plan or a static plan. The first query in this case appears to be using a dynamic plan and the second one a static plan.</p> <p>I've also found that if I try </p> <pre><code>SET @C2 = CURSOR DYNAMIC TYPE_WARNING FOR SELECT TOP ... </code></pre> <p>The cursor gets implicitly converted to a <code>keyset</code> cursor so it is clear that the <code>TOP</code> construct is not supported for dynamic cursors, perhaps for the reasons in Ruben's answer - Still looking for a definitive explanation of this.</p> <p>However I have also read that dynamic cursors tend to be <strong>slower</strong> than their static counterparts (<a href="http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html" rel="noreferrer">source 1</a>, <a href="http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx" rel="noreferrer">source 2</a>) which seems surprising to me given that the static variety have to read the source data, copy it, then read the copy rather than just read the source data. <a href="http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx" rel="noreferrer">The article I referenced earlier</a> mentions that dynamic cursors use <code>markers</code>. Can anyone explain what these are? Is it just a RID or the CI key, or something different?</p> <h3>Script</h3> <pre><code>SET STATISTICS IO OFF CREATE TABLE #T ( ord INT IDENTITY PRIMARY KEY, total INT, Filler char(8000)) INSERT INTO #T (total) VALUES (37),(80),(55),(31),(53) DECLARE @running_total INT, @ord INT, @total INT SET @running_total = 0 SET STATISTICS IO ON DECLARE @C1 AS CURSOR; SET @C1 = CURSOR FAST_FORWARD FOR SELECT ord, total FROM #T ORDER BY ord; OPEN @C1; PRINT 'Initial FETCH C1' FETCH NEXT FROM @C1 INTO @ord, @total ; WHILE @@FETCH_STATUS = 0 BEGIN SET @running_total = @running_total + @total PRINT 'FETCH C1' FETCH NEXT FROM @C1 INTO @ord, @total ; END SET @running_total = 0 SET STATISTICS IO ON DECLARE @C2 AS CURSOR; SET @C2 = CURSOR FAST_FORWARD FOR SELECT TOP 5 ord, total FROM #T ORDER BY ord; OPEN @C2; PRINT 'Initial FETCH C2' FETCH NEXT FROM @C2 INTO @ord, @total ; WHILE @@FETCH_STATUS = 0 BEGIN SET @running_total = @running_total + @total PRINT 'FETCH C2' FETCH NEXT FROM @C2 INTO @ord, @total ; END PRINT 'End C2' DROP TABLE #T </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload