Execution Plan with bad row estimate

I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right direction? For example, can I use any kind of query hints / options to improve performance here?

Currently it runs in about 40 minutes and returns about 30,000 records.

Note: I'm also interested if there's new features in 2019 that apply here that don't apply in 2008 R2 (I know one big difference between the two is the Cardinality Estimator).

  • 4
    the problem isn't the row estimates, the problem is that the only way that query can be resolved as written is with nested loops evaluating the predicate on almost every permutation of rows from both tables. (near cross join). For each row in T1 it needs to evaluate the LIKE against every row in T2 with a different id. So if the temp table has 31871 rows as shown and id is unique the number of comparisons required is 31871 * 31870 = 1,015,728,770 – Martin Smith Oct 19 '19 at 7:34
  • Is it really true that t2.itemid may be anywhere in t1.itemid? If you can narrow that down more precisely, it will help your query. even better if you can somehow make itemid conform to a certain format when you build the temp table. THen you can remove the LIKE predicate and go for straight equality. While you are at it, put an index on that column. Maybe you can post a few samples of what you have and what you need to match? – user1443098 Oct 19 '19 at 14:28

This query, as written, must compare every pair of rows, and the cost estimate is reasonably accurate.

On a supported version of SQL Server you might get a parallel plan, or there is an undocumented query hint that will force a parallel plan.

select t1.itemid, t2.itemid
from #temp T1  
inner join #temp as t2
  on t1.itemid like '%' + t2.itemid + '%'
  and t2.itemid <> t1.itemid
| improve this answer | |

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.