JOIN performance problem - resolved but need to understand how ..
Posted on 2011-03-03
Original problem query :
SELECT a.gbco, a.gbmcu,
SUM(- a.AP12_sales + a.AP11_sales_advance + a.AP12_sales_arrears) AS [Net Sales],
a.gbco + N'_' + a.Departure AS [Sort Code],
SUM(b.AP1_sales + b.AP2_sales) AS Expr1, a.Departure, a.OCT_PREFIX
FROM SalesFigures1 a LEFT OUTER JOIN
SalesFigures2 b ON LTRIM(a.gbmcu) = LTRIM(b.gbmcu)WHERE (a.Departure = N'2010.12')
GROUP BY a.gbco, a.gbmcu, a.gbco + N'_' + a.Departure, a.Departure, a.OCT_PREFIX
HAVING (SUM(- a.AP12_sales + a.AP11_sales_advance + a.AP12_sales_arrears) <> 0)
AND (SUM(a.AP12_Purchases + a.AP12_PO) = 0)
AND (SUM(b.AP1_Purchases + b.AP2_Purchases + b.AP2_PO) = 0)
SalesFigures1 has 3,857,691 rows
SalesFigures2 has 1,122,659 rows
Customer reported that original query was just sitting forever without returning any rows. I
didn't have any access to this customer's box and so couldn't check query execution plan or run Profiler etc.
Established with customer that No index on Departure and no index on gbmcu, and my initial step was to advise the customer to add an index on Departure and an index on gbmcu - but this customer would have to involve their DBA resources for this, and they weren't immediately available.
Given that, I wondered if using LTRIM in the JOIN might be an issue, so suggested
that customer removed it - doing that had an immediate effect and query ran and returned
expected results in around 40 seconds.
I'm just trying to understand how the action of JUST removing the LTRIM in the JOIN on this
query had such an immediate and significant impact. Any suggestions/explanations ?