CAST Issue on join Creating index scan - poor performance

• JohnDBA

  SSC Eights!

  Points: 812

  More actions

  November 13, 2013 at 2:12 pm

  #301099

  I am having an issue with a query causing Index scan and poor performance:

  Here is an example of what I am doing:

  SELECT orderid

  FROM table1 o inner join table2 t

  On CAST(o.invoiceid as char(10) = t.invoicedid

  Where o.invoiceid = '123456'

  How can I make the join searchable and or how to eliminate table scan?

  Thank you so much!

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 13, 2013 at 2:57 pm

  #1666222

  What's the reason for the CAST conversion?

  It seems like o.invoiceid is already a character data type. What exact data type are the two columns referenced in the join predicate (o.invoiceid and t.invoicedid)? What does the actual execution plan look like?

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]