CODEWITHSUNDEEP
sql-serveroptimizationhint
not a name
not a name

Reputation: 33

Is there a optimizer hint in SQL Server for the number of rows?

Is there a optimizer hint in SQL Server for the number of rows? In our application, we often have the scenario that we need to restrict a query to a large set of ids. If we do this via an in-clause, we get big performance issues from a set of about 1000. To solve this, we use the STRING_SPLIT function, which usually works very well.

select * 
from (complex query) as foo 
join  string_split(@ids, ';') as ids 
  on foo.id = ids.value

The parameter @ids is passed by our application.

But for certain queries, we get performance issues again, because the optimizer always estimates the value of 50 for the number of rows, which this function returns. Therefore, we first write the return values of the STRING_SPLITT function into a temp table and use this in the query. For this, the optimizer can estimate the number of rows well.

select value as id into #ids
from string_split(@ids, ';')

select * 
from (complex query) as foo 
join  #ids as ids 
  on foo.id = ids.value

However, when creating the query in our application, we know exactly the number of rows. Is there any way to pass this to the optimizer as a hint? Then we could save all the overhead with the temp table.

I found a way to get the optimizer to estimate less than 50 rows (with TOP), but none to increase the estimation.

Upvotes: 0

Views: 477

Answers (1)

TomTom
TomTom

Reputation: 62157

No. Well, nopish.

You can tell it to optimize for fast first data (i.e. use a slower query, if it returns the first data faster) and tell it how many rows you WANT (TOP), but that is it.

Your problem looks like bad programming, seriously. Not that it is obvious - SQL Server is extremely fickly here. That said, the overhead is extremely non existent unless you run a million executions per minute OR your hardware is extremely badly sized.

  • You can use a table VARIABLE to hold the data, with an index. No need for a temporary table. It is technically quite similar but has different semantical meaning (like variables have a scope different form the life cycle of a temporary table). An index on the variable, with execution post factum...

You also may want then to use OPTION RECOMPILE - because your other problem is that SQL Server reuses query plans. The query plan here may depend on the size of the id table - so a hint would not work if different sizes lead to different outcomes and the same plan is reused anyway.

I would actually assume the query plan assumption of 50 rows to b a good compromise, would use a table variable and - well - option recompile if needed.

Upvotes: 0

Related Questions