Reducing Temp Table Scans when joining two temp tables using OR conditions

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.

https://www.brentozar.com/pastetheplan/?id=H16jNYvXK

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!

Testing

This query runs in ~1.2 seconds, so I’m not sure how much improvement you’re expecting. Looking at things like scans and costs can often be misleading and fruitless when it comes to improving the overall timing of the query.

I’m cutting out some of the columns in your query just to highlight the changes more easily.

At the top, it may be worth going back to the SELECT INTO #... method, or adding a tablock hint to the select to allow for a parallel plan. You may not get one, and it may not make a huge difference since your query plan shows EstimatedAvailableDegreeOfParallelism="2".

This may be a limiting factor overall.

INSERT INTO 
    #temp WITH(TABLOCK)
select 
   IPS.ACCT as ACCT
  /*SNIP*/
from 
  #SmallerRange IPS
  join #LargerRange V on V.VID = IPS.VID and V.IorO = IPS.IorO and V.OtherLocationID = IPS.OtherLocationID 
WHERE  
 (
   IPS.CDate <> V.CDate  or 
   IPS.ACCT != V.ACCT or 
   IPS.Unit != V.Unit or 
   IPS.ARRVLTIME <> V.ARRVLTIME or 
   IPS.LocationID != V.LocationID
   )
OPTION(HASH JOIN);

Since the query time jumps up from ~443ms in the two child branches to ~1.2ms at the merge join, we can infer that ~743ms is spent at the merge join. Anton is correct that it’s likely from it being a many to many merge join.

To avoid that, we can add a hash join hint, which doesn’t have a many-to-many type.

It may also be instructive to try a OPTION(FORCE ORDER); hint as well.

To your point about indexing temp tables, there are a lot of factors to consider that are not covered in Brent’s post.

• Indexing Temp Tables
• When Should You Index Temp Tables?

The reads and scans are part of it reading the inner sorted row source. I had a little play replicating this and it looks like the important things to note are that the two temp tables are of similar size and you are comparing a string (which is weird, you might expect the opposite to be true as you’d expect a string to be larger and more expensive to involve in sorts). You almost certainly want to get this to use a hash join using your equality conditions.

I replicated your tables using the Stackoverflow database sampling the Comments table.

If we do:

select top 40809 * 
into #LargerRange
from dbo.comments

select top 38193 * 
into #SmallerRange
from #LargerRange

To generate the data sets, and use

select sr.id
from  #SmallerRange sr
join  #LargerRange  lr
  on  sr.PostId = lr.PostId
 and  sr.UserId = lr.UserId
where sr.score <> lr.score 
or    sr.id    <> lr.id    
or    sr.text  <> lr.text

As the basis of your main query. You get the merge join plan that you see.

If we remove the comparison of the string column, we get this hash join plan.

If we change the comparison so that it is converting the string to a number (using a hashing function):

select sr.id
from  #SmallerRange sr
join  #LargerRange  lr
  on  sr.PostId = lr.PostId
 and  sr.UserId = lr.UserId
where sr.score <> lr.score 
or    sr.id    <> lr.id    
or    checksum(sr.text)  <> checksum(lr.text)

We also get a hash join plan.

If we change it so that instead of a bunch of ORs, we use one case when statement (which is difficult for the query planner to pull apart):

select sr.id 
from  #SmallerRange sr
join  #LargerRange  lr
  on  sr.PostId = lr.PostId
 and  sr.UserId = lr.UserId
where case when sr.score <> lr.score 
             or sr.id    <> lr.id 
             or lr.text  <> lr.text 
           then 1 
           else 0 
     end = 1 

You also get a hash join plan

The case when option would be my preference.

There appears to be a many-to-many merge join. Without knowing more about the data in these temp tables, this may or may not be expected. If that’s not expected and if rows in each temp table can be uniquely identified by (VID, Ioro, OtherLocationID), you can try to add a clustered, unique index on both tables on those columns. Then, SQL Server might understand that there is a one-to-one relationship between the two temp tables and avoid a many-to-many merge.