Solved

JOIN performance problem - resolved but need to understand how ..

Posted on 2011-03-03
5
279 Views
Last Modified: 2012-05-11
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 ?
0
Comment
Question by:raymurphy
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 35026257
using LTRIM() would make that any index on the columns could not be used, so your suggestion was perfect at that regards.

however, the LTRIM() was there for a reason, presumably, so you have to check with the customer about the why.
reason shall be:
either or both of the fields are CHAR (and not varchar), with different sizes eventually.
 
0
 

Author Comment

by:raymurphy
ID: 35026545
Thanks for the prompt reply,  angelIII .... The gbmcu field is varchar(12) on both tables, and contains values such as '  AA7F013552' (i.e. two leading spaces).

Before I got the customer to amend their problem query (i.e. removing the original LTRIM), I got them to run the following queries as a precautionary check :

SELECT     a.gbmcu AS gbmcuSF1, b.gbmcu AS gbmcuSF2                   
FROM       SalesFigures1 a JOIN SalesFigures2 b
ON LTRIM(a.gbmcu) = LTRIM(b.gbmcu)                                     
SELECT     a.gbmcu AS gbmcuSF1, b.gbmcu AS gbmcuSF2                   
FROM       SalesFigures1 a JOIN SalesFigures2 b
ON (a.gbmcu) = (b.gbmcu)

Both queries returned the same number of rows (669220), which reassured me that I would be OK to remove the LTRIM from the original problem query - so still wondering exactly why (given that the customer has no index on the gbmcu column) removing the LTRIM from the original problem query caused it to run through OK (whereas with the LTRIM in the JOIN) the original problem query was
just sitting forever without returning any rows ???
0
 
LVL 1

Assisted Solution

by:vandalesm
vandalesm earned 250 total points
ID: 35026720
When you removed the LEFT OUTER JOIN it will consider that query as INNER JOIN meaning it will only return all matching rows. However, the original query using the LEFT OUTER JOIN will return all non matching rows on SalesFigure1 as well.
The original query should have return 3,857,691
0
 

Author Comment

by:raymurphy
ID: 35026828
The change to the original problem query, vandalesm, wasn't to remove the LEFT OUTER JOIN but just to change the ON LTRIM(a.gbmcu) = LTRIM(b.gbmcu) in the LEFT OUTER JOIN so that it became
ON (a.gbmcu) = (b.gbmcu)  .....
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35027044
hi

the change is very simple

the index hold the data regarding your column the way it is in that table.
when you use a function on the column you render the indexes useless since the index dosent hold the data after function manipulation (for example oracle has wats called a Function Based Index an index that is created while using a function on a column thos saving the manipulated data and RID).

you could consider using a Indexed view to save the query results and then sql server optimizer will use it automatically when the original query is executed, but using this could have an impact on your DML operations.

0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now