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

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

Updated April 26, 2015 21:02 PM

Updated August 04, 2017 00:06 AM

Updated November 12, 2018 13:06 PM

Updated January 10, 2018 11:06 AM