Boss had a query joining a TranGLC table to our Invoiced table. The transaction table is set up in a way there the key fields are varchar fields labeled as Key1, Key2, etc. with a qualifier for each record in terms of what table the row joins with. So TranGLC.RelatedTo = 'InvcDtl', Key1 and Key2 are Invoice Number and Line Number (stored as Ints in other tables). RelatedTo could be one of many things.
The query itself ran in about 9 seconds, but when the bossman put in a 'Where OrderNumber = XXXXXX' which should return fewer records, it took 48 seconds to run. Odd. He brought it to me and looking at the execution plan, I started playing and found that TranGLC was implicitly converting Key1 and Key2 to an int to match up with the Invoice Table.
I tried Explicitly converting InvoiceNumber and Line to varchar fields and BAM! Ran in less than a second. ex.
Inner Join InvoiceDtl on TGLC.Key1 = convert(varchar(25), InvoiceDtl.InvoiceNum) and.....
The execution plan is different, but I'm afraid this is where my knowledge of such things stalls into guesswork. I'm curious as to why this is and how it affects performance so drastically. My theory is that since the TranGLC table holds SO many more records (many of which could not be converted to integers anyways) that the Implicit Conversion tries (and fails in a lot of cases) to convert the whole Key columns of TranGLC first, then join int to int Vs taking significantly fewer fields, with no problems converting to varchars, and then joining varchar to varchar.
Anyone have a detailed explanation? Query for context:
select
[TranGLC].[GLAccount] as [TranGLC_GLAccount],
[InvcDtl].[InvoiceNum] as [InvcDtl_InvoiceNum],
[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
(SUM(TranGLC.CreditAmount)) as [Calculated_GROSS],
(SUM(TranGLC.DebitAmount)) as [Calculated_DISCOUNT],
(SUM(TranGLC.CreditAmount)-SUM(TranGLC.DebitAmount)) as [Calculated_NET]
from Erp.TranGLC as TranGLC
inner join Erp.InvcDtl as InvcDtl on
TranGLC.Company = InvcDtl.Company
and TranGLC.Key1 = convert(varchar(25),InvcDtl.InvoiceNum)
and TranGLC.Key2 = convert(varchar(4),Invcdtl.InvoiceLine)
and TranGLC.RelatedToFile = 'InvcDtl'
where (TranGLC.TranDate >= '1-1-2016' and TranGLC.TranDate <= convert(date, getdate()) and InvcDtl.OrderNum = 123456)
group by [TranGLC].[GLAccount],
[InvcDtl].[InvoiceNum],
[InvcDtl].[OrderNum]
