This query accepts a User-Defined Table Type variable and then joins that with a table. If the input is a single record, I want the join to be LIKE '%'+input+'%', otherwise it should be =input. This is essentially what I have now, but it's quite slow for larger inputs (testing around 500 takes around 5min).

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )
SELECT b.* FROM @inputUDTT a
INNER JOIN Tabl b ON (@inputCount>1 AND b.name = a.val)
    OR (@inputCount<2 AND b.name like '%' + a.val+ '%')

I've also tried having the SELECT COUNT(*) directly in the join and formatting the condition as a CASE WHEN switch, but neither helped. I know I could alter this into a dynamic query, but that seems excessive for something that seems like it should be fixable in a simpler method.

  • Did you already try OPTION (RECOMPILE)? – Scott Hodgin Apr 13 '17 at 15:15
  • 1
    @ScottHodgin It always feels like cheating, but it always works. I should stop forgetting that exists. if this answer is to be believed, I suppose it's because the variable being set? – David Starkey Apr 13 '17 at 15:24

I'd start by breaking this out to get a better idea of which part is slow. This should clarify the execution plan overall.

From there you can start sorting out other techniques, like temp tables, recompile hints, indexes, etc.

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )

FROM @inputUDTT a
ON (@inputCount > 1 AND b.name = a.val)

UNION ALL       

FROM @inputUDTT a
ON (@inputCount < 2 AND b.name like '%' + a.val+ '%')

For example, in the first query where you have an equality predicate, the optimizer can choose different join types (though it may only ever choose nested loops because of the low row estimates for a table variable. In the second query, the only join type available with no equality predicate is nested loops.

Hope this helps!

| improve this answer | |

Community wiki answer:

You could break this into two separate queries, e.g.:

IF @inputCount = 1
    <run LIKE query> 
    <run equals query>

Or you could try OPTION (RECOMPILE). One problem is that table variables don't have statistics and that causes the optimizer grief - using OPTION(RECOMPILE) can frequently solve this.

Another typical work-around is to materialize the table variable into a local temporary table (which does support statistics) and use that instead.

An example with query plan (nested loop join changing to a hash join) can be found in Improving SQL Server performance when using table variables by Matteo Lorini.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.