Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

by J.D.   Last Updated October 18, 2019 22:06 PM - source

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).

Related Questions

Sql Server 2019 Query Hint Placement confusion

Updated July 07, 2020 14:06 PM

How to speed up looped INSERT INTO statements?

Updated November 12, 2018 13:06 PM