0

I have a table-valued function that returns four rows:

SELECT * 
FROM dbo.GetStuff('0D182B8B-7A80-4D45-8900-23FA01FCFE5A')
ORDER BY TurboEncabulatorID DESC

and this returns quickly (< 1s):

TurboEncabulatorID  Trunion  Casing  StatorSlots
------------------  -------  ------  -----------
4                   G        Red     19
3                   F        Pink    24
2                   H        Maroon  17
1                   G        Purple  32

But i only want the "last" row (i.e. the row with the highest TurboEncabulatorID). So i add the TOP:

SELECT TOP 1 * 
FROM dbo.GetStuff('0D182B8B-7A80-4D45-8900-23FA01FCFE5A')
ORDER BY TurboEncabulatorID DESC

This query takes ~40s to run, with a huge amount of I/O, and a much worse query plan.

Obviously this is an issue with the optimizer - but how can i work around it?

  • i've updated all statistics
  • i've rebuilt all indexes

Bonus Reading

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
  • There are a LOT OF reasons that the optimization could be off. You might need to update the statistics on the table (because optimizer uses them), or you might want to create an index on the table – Sparrow Nov 23 '17 at 21:46
  • If the account running this query will be sysadmin you can add OPTION (QUERYTRACEON 4138) though if the account isn't sysadmin that would fail unless applied with a plan guide. this turns off the "row goal" costing that is likely causing the problem. – Martin Smith Nov 23 '17 at 21:52
  • Could you test whether param sniffing is the problem? https://stackoverflow.com/questions/20699393/sql-server-parameter-sniffing – bic Nov 23 '17 at 22:50
  • @bic What's the syntax for adding `OPTION (OPTIMIZE FOR (@MelleableTrunionGUID UNKNOWN) )` ? – Ian Boyd Nov 24 '17 at 15:42

1 Answers1

0

The workaround i came up with, which obviously isn't an answer, is to try to confuse the optimizer:

  • select top 1 rows
  • from top 100 percent of rows
  • from table valued function

In other words:

WITH optimizerWorkaround AS
(
   SELECT TOP 1 PERCENT *
   FROM dbo.GetStuff('0D182B8B-7A80-4D45-8900-23FA01FCFE5A')
   ORDER BY TurboEncabulatorID DESC
)
SELECT TOP 1 *
FROM optimizerWorkaround 
ORDER BY TurboEncabulatorID DESC

This returns quickly as if i had no TOP in the first place.

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
  • @MartinSmith If my query *happens* to return 200 rows, then `TOP 1 PERCENT` would return two rows. This *example* happens to only return four rows; in the general case that is not true. – Ian Boyd Nov 23 '17 at 21:49
  • Ah yes, I thought you were looking for top 4. – Martin Smith Nov 23 '17 at 21:51