Databases
StackOverflow2013
Postsdbo
POSelect top 4 with order by, but only if actually required?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect top 4 with order by, but only if actually required?
    primarykey
    data
    text
    <p>I have part of a stored proc that is called thousands and thousands of times and as a result takes up the bulk of the whole thing. Having run it through execution plan it looks like the TOP 4 and Order By part is taking up a lot of that. The order by uses a function that although streamlined, will still be being used a fair bit.</p> <p>This is an odd situation in that for 99.5% of the data there will be 4 or less results returned anyway, it's only for the 0.5% of times that we need the TOP 4. This is a requirement of the data algorithm so eliminating the TOP 4 entirely is not an option.</p> <p>So lets say my syntax is</p> <pre><code>SELECT SomeField * SomeOtherField as MainField, SomeOtherField FROM ( SELECT TOP 4 SomeField, 1/dbo.[Myfunction](Param1, Param2, 34892) as SomeOtherField FROM #MytempTable WHERE Param1 &gt; @NextMargin1 AND Param1 &lt; @NextMargin1End AND Param2 &gt; @NextMargin2 AND Param2 &lt; @NextMargin2End ORDER BY dbo.[MyFunction](Param1, Param2, 34892) ) d </code></pre> <p>Is there a way I can tell SQL server to do the order by if and only if there are more than 4 results returned after the where takes place? I don't need the order otherwise. Perhaps a table variable and count of the table in an if?</p> <p>--- Update based on Davids Answer to try to work out why it was slower:</p> <p>I did a check and can confirm that 96.5% of times there are 4 or less results so it's not a case of more data than expected.</p> <p>Here is the execution plan for the insert into the @FunctionResults <img src="https://i.stack.imgur.com/uZM47.png" alt="Execution Plan 1"></p> <p>And the breakdowns of the Insert and spool: <img src="https://i.stack.imgur.com/uZfvT.png" alt="Breakdowns"></p> <p>And then the execution plan for the selection of the top4 and orderby: <img src="https://i.stack.imgur.com/AkM5J.png" alt="Execution Plan2"></p> <p>Please let me know if any further information or breakdowns are required, the size of #Mytemptable could typically be 28000 rows and it has index</p> <pre><code>CREATE INDEX MyIndex on #MyTempTable (Param1, Param2) INCLUDE ([SomeField]) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKnightsy
    1. USKnightsy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSelect top 4 with order by, but only if actually required?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSelect top 4 with order by, but only if actually required?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO. . I find it hard to believe that sorting four or fewer result rows would result in the performance problem. I suspect that the overall query is the issue, particularly the inequalities in the `where` clause (which make using indexes difficult) and the call to `dbo.MyFunction()`.
      singulars
      1. POSelect top 4 with order by, but only if actually required?
      1. USGordon Linoff
    2. COThe sorting uses dbo.Myfunction, removing the orderby removes about 80% of the function calls, so it's not the sorting itself but the sortings reliance on the function. If we can minimise the sorting to be only in the 0.5% of cases requiring I will get wins on that side of things
      singulars
      1. POSelect top 4 with order by, but only if actually required?
      1. USKnightsy
    3. COTry using `order by SomeOtherField desc`, assuming the values are positive. This might save the extra calls when sorting.
      singulars
      1. POSelect top 4 with order by, but only if actually required?
      1. USGordon Linoff
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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