StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I found the problem, here's the script of the slow and fast versions of the stored procedure:</p> <p><strong><code>dbo.ViewOpener__RenamedForCruachan__Slow.PRC</code></strong></p> <pre><code>SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow @SessionGUID uniqueidentifier AS SELECT * FROM Report_Opener_RenamedForCruachan WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO </code></pre> <p><strong><code>dbo.ViewOpener__RenamedForCruachan__Fast.PRC</code></strong></p> <pre><code>SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast @SessionGUID uniqueidentifier AS SELECT * FROM Report_Opener_RenamedForCruachan WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO </code></pre> <p>If you didn't spot the difference, I don't blame you. The difference is not in the stored procedure at all. The difference that turns a fast 0.5 cost query into one that does an eager spool of 6 million rows:</p> <p><strong>Slow:</strong> <code>SET ANSI_NULLS OFF</code></p> <p><strong>Fast:</strong> <code>SET ANSI_NULLS ON</code></p> <hr> <p>This answer also could be made to make sense, since the view does have a join clause that says:</p> <pre><code>(table.column IS NOT NULL) </code></pre> <p>So there is some <code>NULL</code>s involved.</p> <hr> <p>The explanation is further proved by returning to Query Analizer, and running</p> <pre><code>SET ANSI_NULLS OFF </code></pre> <p>.</p> <pre><code>DECLARE @SessionGUID uniqueidentifier SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908' </code></pre> <p>.</p> <pre><code>SELECT * FROM Report_Opener_RenamedForCruachan WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank </code></pre> <p>And the query is slow.</p> <hr> <p>So the problem <strong>isn't</strong> because the query is being run from a stored procedure. The problem is that Enterprise Manager's connection default option is <code>ANSI_NULLS off</code>, rather than <code>ANSI_NULLS on</code>, which is QA's default.</p> <p>Microsoft acknowledges this fact in <a href="http://support.microsoft.com/kb/296769" rel="noreferrer">KB296769</a> (BUG: Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects). The workaround is include the <code>ANSI_NULLS</code> option in the stored procedure dialog:</p> <pre><code>Set ANSI_NULLS ON Go Create Proc spXXXX as .... </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