May 24, 2006 at 2:55 pm
I am trying to run this query and wanting to optimize it a bit. It runs fine but just wondering if there are other ideas that will run faster and still return the same information. Right now it takes approx ~8 secs to run and trying to get that optimized down to about !/2 that. not sure if its possible but i know there are more ways to skin a cat and would like a few extra eyes on this as well. Thanks in advance...
SELECT Opportunity.1,Opportunity.2,
Opportunity.3,Opportunity.4,etc...
Product.1,CompanyA.1,CompanyB.2,CompanyB.3,
FROM (((((((Opportunity LEFT OUTER JOIN Opportunity.1 ON Opportunity.A = or Competitor__Opportunity.A)
LEFT OUTER JOIN Product ON Opportunity.B = Product.B) LEFT OUTER JOIN Company ON Opportunity.C= Company.C) LEFT OUTER JOIN Product ON Opportunity.D = Product.D) LEFT OUTER JOIN Company ON Opportunity.E= Company.E) LEFT OUTER JOIN Product ON Opportunity.F = Product.F) LEFT OUTER JOIN Company ON Opportunity.G= Company.G) LEFT OUTER JOIN Product ON Opportunity.H = Product.H) LEFT OUTER JOIN Company ON Opportunity.I= Company.I)
WHERE (Opportunity.a = Company.a AND ( (Opportunity.D = 0) AND (Opportunity.S <> '0') AND (Opportunity.S <> '100') AND (Opportunity.Erd >= {d'2006-05-01'}) AND (Opportunity.Erd <= {d'2007-05-31'}) AND (EXISTS (SELECT * FROM FE_Code WHERE FE_Code.District_Id = 0x0000003E AND (Opportunity.Oppty_FE_Code_Id = FE_Code.FE_Code_Id)))))
This query has many joins and once again..can i get a few suggestions?
DHeath
May 24, 2006 at 3:15 pm
This query will not work as is. You have syntax errors in your joins, and SQL Server will not let you join the same table multiple times without defining table aliases.
A couple of things to consider here. It is very difficult to assist in optimizing a query like this without knowledge of the table/index schema information and some sample data. From a first glance, you could remove ALL of the perenthesis for better readability. As far as performance, the way you are using self-joins here is a bit confusing. Can you post your real query?
May 24, 2006 at 3:42 pm
ok here is the original query... hope this helps and yes it does work fine as is... tried to simplify before seems to have caused problems..sorry
SELECT Opportunity.Opportunity_Id,Opportunity.Rn_Create_Date,Opportunity.Opportunity_Name,
Opportunity.Expected_Revenue_Date,Opportunity.Status,Opportunity.Prod_Div_Ch_Partner,
Opportunity.Weighted_Amt,Opportunity.Raw_Total,Opportunity.Opportunity_Id,Opportunity.Partner_Type,
Product.Product_Code,Company.Zip,Company.State_,Company.City,FE_Code.FE_Code,Employee.Last_Name,
TTID_23R2826R2833.Last_Name,TTID_101R2850R2864.Product_Line_Code,TTID_58R987L949.Competitor_Name
FROM (((((((Opportunity LEFT OUTER JOIN Competitor__Opportunity
ON Opportunity.Opportunity_Id = Competitor__Opportunity.Opportunity_Id) LEFT OUTER JOIN Product
ON Opportunity.Product_Id = Product.Product_Id) LEFT OUTER JOIN Company
ON Opportunity.Company_Id = Company.Company_Id) LEFT OUTER JOIN FE_Code
ON Opportunity.Oppty_FE_Code_Id = FE_Code.FE_Code_Id) LEFT OUTER JOIN Employee
ON Opportunity.Lead_FE = Employee.Employee_Id) LEFT OUTER JOIN Employee
AS TTID_23R2826R2833
ON FE_Code.Employee_Id = TTID_23R2826R2833.Employee_Id) LEFT OUTER JOIN Product_Line
AS TTID_101R2850R2864
ON Product.Product_Line_Id = TTID_101R2850R2864.Product_Line_Id) LEFT OUTER JOIN Competitor
AS TTID_58R987L949 ON Competitor__Opportunity.Competitor_Id = TTID_58R987L949.Competitors_Id
WHERE (Opportunity.Company_Id = Company.Company_Id
AND ( (Opportunity.Deleted = 0) AND (Opportunity.Status <> '0')
AND (Opportunity.Status <> '100') AND (Opportunity.Expected_Revenue_Date >= {d'2006-05-01'})
AND (Opportunity.Expected_Revenue_Date <= {d'2007-05-31'})
AND (EXISTS (SELECT * FROM FE_Code WHERE FE_Code.District_Id = 0x000000000000003E
AND (Opportunity.Oppty_FE_Code_Id = FE_Code.FE_Code_Id)))))
DHeath
May 24, 2006 at 5:50 pm
Once you've got rid of as many of those as possible, to get more improvement, you'll need to run the query with 'set statistics profile on', then post the execution plan (the entire StmtText column)
o.Opportunity_Id,o.Rn_Create_Date,o.Opportunity_Name,
.Expected_Revenue_Date,o.Status,o.Prod_Div_Ch_Partner,
.Weighted_Amt,o.Raw_Total,o.Opportunity_Id,o.Partner_Type,
.Product_Code,c.Zip,c.State_,
.City,fec.FE_Code,e.Last_Name,
.Last_Name,
.Product_Line_Code,
.Competitor_Name
Opportunity o
Company c
o.Company_Id = c.Company_Id
join Employee e
o.Lead_FE = e.Employee_Id
join Competitor__Opportunity cp_o
o.Opportunity_Id = cp_o.Opportunity_Id
join Product p
o.Product_Id = Product.Product_Id
join FE_Code fec
o.Oppty_FE_Code_Id = fec.FE_Code_Id
o.Deleted = 0
o.Status <> '0'
o.Status <> '100'
o.Expected_Revenue_Date >= {d'2006-05-01'}
(
select FE_Code_Id
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 25, 2006 at 2:47 am
Just a slight improvement, but I would change Tim's IN clause back to an EXISTS in the following form:
The use of the EXISTS like this will mean that the subquery will return upon the first match and also that indexes on the o.Oppty_FE_Code_Id and FE_Code_Id columns can be used (if they exist).
One caveat though, if there is always only one FE_Code.FE_Code_Id that matches o.Oppty_FE_Code_Id then you should join directly to the FE_Code table instead of using EXISTS or IN.
Austin
May 25, 2006 at 2:59 am
IN returns on the first match, too. In fact I'm pretty sure that the two statements are regraded as eqivaluent by the optimiser.
You still need to check the left joins are necessary, then get an execution plan. If you're not sure how to proceed, ask.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 25, 2006 at 3:45 am
I wasn't sure that was true for SQL Server, might it depend upon which version is being used?
Either way, the use of the IN or EXISTS would imply that the outer join to FE_Code should be replaced with an inner join between FE_Code and Opportunity. Ironically the use of the IN/EXITS in conjunction with the LEFT JOIN mean that the features of both action are negated. The IN/EXISTS forces an INNER JOIN and the inclusion of the LEFT JOIN means that all matches will be sought and that duplicates would not be quashed.
Austin
May 25, 2006 at 3:59 am
The left join and the IN clause don't cancel because they aren't necessarily looking at the same records. The IN clause check that the FE code of the opportuinty matches at least one code in the FE table for the appropriate district.
Having siad that, given that one might expect FE_code to be unique, it does look as though this might be a mistake and the district ID where clause could be put in the main query - as you suggest, cancelling the effect of the left join.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 25, 2006 at 4:41 am
Of course, Tim is quite right. In the query the Employee_Id generated are not constrained to District_Id = 0x0003E. If this is a true requirement then the IN condition must be kept.
To clarify, what I was trying to allude to is that the left join on FE_Code is cancelled because the IN requires that a matching FE_Code_Id must exist. Also, if the use of the IN where solely to reduce duplicates or for performance of a fast evaluation, then this is negated by the join.
Austin
May 25, 2006 at 8:35 am
Sorry for the late post. I wrote this yesterday afternoon, but I was unable to post it due to a server outage.
Again, without knowing your table/index schema information, all I can do is make guesses. I would suggest looking into the following:
1. Remove the parenthesis for readability.
2. Lose the 'Opportunity.Company_Id = Company.Company_Id' condition in your WHERE clause. This is the same as the join condition and may be creating an INNER JOIN instead of the LEFT JOIN that you specefied between Opportunity and Company.
3. Lose the Exists section in the WHERE clause. If the FE_Code row must exist, isn't this the same as an INNER JOIN FE_Code ON Opportunity.Oppty_FE_Code_Id = FE_Code.FE_Code_Id and FE_Code.District_Id = 0x000000000000003E ?
You should take your version through QA and view the execution plan. This will help you determine which steps are costing the most and you can look further into that area of the query. Make sure that you are only joining on indexed columns. It is very helpful to have the columns included in the WHERE clause indexed as well.
You should take some time and verify that this query is returning the correct results. Combining OUTER JOINs with WHERE clause filters on the outer table can turn the JOIN into an INNER JOIN. See this link: http://www.sqlservercentral.com/columnists/sjones/outerjointrouble.asp
May 25, 2006 at 12:57 pm
Thanks to everyone.... the help is much appreciated and helped me to see it all from a few different views...
DHeath
May 26, 2006 at 3:47 pm
Re the previous two posts, the IN subquery will be equivalent to a where clause in the main query (and therefore to an inner join) if and only if FE_Code in the FE_Codes table is unique (for the range of relevant cases).
I still agree that this is likely, though the fact that an EXISTS clause was used in the original code (is it existing production code?) would certainly suggest that checking that would be a good idea.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply