I'm working on a complicated query that I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables which are structured exactly the same; the difference is that one table is a subset of the other (one was created using a larger date range). These tables are created by querying ~6 physical tables in a CTE, filtering down, etc. The part of the query I'm struggling with here is when we join the two tables on three fields, and then in the where clause, we further compare 5 columns in the tables using inequality operators and OR conditions. This query seems to be the costliest in the whole batch by ~200,000 logical reads and 30,000+ table scans. See the paste the plan link below for the execution plan for this exact part of the query as well as the DML statement.
As you can see, we're doing table scans on the temp tables and then doing a merge join. The plan looks OK enough except that the merge join's row estimate is WAY too high [est: 38335 vs actual: 209].
I have indeed attempted to create indexes for the temp tables, partly out of desperation. It didn't seem to help in this case. The indexes I have tested were nonclustered indexes using the three fields in the join condition. This just changed the execution plan to use RID lookups in the heaps and did nothing to change the estimate or reduce the number of scans/reads. I have also tried a nonclustered index on the fields used in the WHERE clause, but due to a couple of the fields being varchar(max) fields (poor schema design choice that is before my time and something I've been told to just deal with), I can't use these in an index. I have tried casting them down but some index inserts are failing because they're too many bytes. Not only that, but my understanding is that creating indexes on temp tables are in many cases not really super useful (https://www.brentozar.com/archive/2021/08/you-probably-shouldnt-index-your-temp-tables/).
I have also, again out of desperation, tried creating clustered indexes on the two tables with the join fields as the PK. This did indeed drastically increase the amount of execution time. This was somewhat expected but I figured why not give it a try.
I have also tried breaking this out into 5 queries with union alls. Unfortunately this leaves me with duplicate rows, which we can't have, increases the work by a not insignificant amount, and unions just take too long.
What makes this worse is that this part of the query has a union behind it with another query that's extremely similar with even worse where clause conditions, so figuring this out is somewhat crucial here.
Why is it exactly that I'm getting so many reads and scans and how can I mitigate that in this scenario? I appreciate your time! If I have left out some crucial information, please let me know and I'll do my best to provide what I can. Thanks!