How to better do a implicit conversion (cast) for a join?

All we need is an easy explanation of the problem, so here it is.

I have a strange query like this:

SELECT T1.*
FROM Table1 T1
INNER JOIN #TEMP an ON  an.Id = T1.AccNum
                    LEFT OUTER JOIN Table2 T2
                        ON  CAST(T2.Ref_tranID as bigint)= T1.Table1ID
                        AND CAST(T2.Ref_TranLineID as int) = T1.ItmSeq
                        AND T2.PrID = 50
                    where T2.Table2ID IS NULL AND T1.CalDateDate >= '2022-06-15'  and  T1.CalDateDate <= '2022-06-16'

one of the issues is that T1.AccNum is MAX so I can’t use it in a index (this will be fixed this month hehe).

but I think the issue here is these casts.

they are originally varchar. I get some keyLokups that I’m fixing already but, is there a smarter way to deal with those casts?

to be honest we have 2 of these queries (identical) with a union in the middle, but fixing one will fix another.

I tried to populate a temp table with the correct data types but still I got the query running for more than 3 minutes.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can:

  • Fix the data types in the base table, and index them
  • Create computed columns on the base table, and index them
  • Dump some filtered result into a #temp table with the columns converted to the correct data type, and index them
  • Induce batch mode if you’re on Enterprise Edition

There’s no good, or better, way to do this at runtime.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply