Query Performance is poor

[brettgab]

Greetings all (esp Terry !),

OK I'm quite new to SQL Server and Transact-SQL and know very little about getting the best performance.

I'm programming for a company and I have a massive query...Here it is...

SELECT S.sys_description AS STORE, V.Territory_Code, (IsNull(Sum(V.Conn_Profit),0) + IsNull(Sum(V.Prod_Profit),0)) as Profit, T.Profit_Target, CASE T.Profit_Target WHEN 0 THEN 0 ELSE (IsNull(Sum(V.Conn_Profit),0) + IsNull(Sum(V.Prod_Profit),0))/T.Profit_Target END AS Profit_Perf, Sum(V.LeaderBill) AS LBU, Sum(V.LeaderBillSimOnly) as LBSO, Sum(V.FR_Upgrade) as FRU, Sum(V.FR_New) as FRN, T.FRunner_Target, CASE T.FRunner_Target WHEN 0 THEN 0 ELSE (IsNull(Sum(V.FR_Upgrade),0) + IsNull(Sum(V.FR_New),0))/T.FRunner_Target END AS FR_Perf, Sum(V.PS_Upgrade) as PSU, Sum(V.PS_New) as PSN, T.PSaver_Target, CASE T.PSaver_Target WHEN 0 THEN 0 ELSE (IsNull(Sum(V.PS_Upgrade),0) + IsNull(Sum(V.PS_New),0))/T.PSaver_Target END AS PS_Perf, Sum(V.Upgrade_PlanInc) AS UGPlanInc, Sum(V.Other_New) AS OtherNEW, (IsNull(Sum(V.LeaderBill),0) + IsNull(Sum(V.LeaderBillSimOnly),0) + IsNull(Sum(V.FR_New),0) + IsNull(Sum(V.PS_New),0) + IsNull(Sum(V.Other_New),0)) AS TotalNew, (IsNull(Sum(V.FR_Upgrade),0) + IsNull(Sum(V.PS_Upgrade),0)) AS TotalUG, (IsNull(Sum(V.FR_New),0) + IsNull(Sum(V.PS_New),0) + IsNull(Sum(V.FR_Upgrade),0) + IsNull(Sum(V.PS_Upgrade),0)) AS Handsets FROM (VW_Conn_Summary_NCC V INNER JOIN system_table S ON V.Territory_Code = S.sys_tbl_code AND S.sys_tbl_type = 'TC' AND V.Invoice_Date >='01-Sep-2001' AND V.Invoice_Date <='10-Sep-2001') LEFT JOIN tbl_StoreTargets T ON V.Territory_Code = T.Territory AND T.ST_Month = '01-Sep-2001' GROUP BY S.sys_description, T.FRunner_Target, T.PSaver_Target, T.Profit_Target, V.Territory_Code
UNION
SELECT CCR.Dept AS STORE, '900' AS Terr_Code, (IsNull(Sum(V2.Conn_Profit),0) + IsNull(Sum(V2.Prod_Profit),0)) as Profit, T.Profit_Target, CASE T.Profit_Target WHEN 0 THEN 0 ELSE (IsNull(Sum(V2.Conn_Profit),0) + IsNull(Sum(V2.Prod_Profit),0))/T.Profit_Target END AS Profit_Perf, Sum(V2.LeaderBill) AS LBU, Sum(V2.LeaderBillSimOnly) as LBSO, Sum(V2.FR_Upgrade) as FRU, Sum(V2.FR_New) as FRN, T.FRunner_Target, CASE T.FRunner_Target WHEN 0 THEN 0 ELSE (IsNull(Sum(V2.FR_Upgrade),0) + IsNull(Sum(V2.FR_New),0))/T.FRunner_Target END AS FR_Perf, Sum(V2.PS_Upgrade) as PSU, Sum(V2.PS_New) as PSN, T.PSaver_Target, CASE T.PSaver_Target WHEN 0 THEN 0 ELSE (IsNull(Sum(V2.PS_Upgrade),0) + IsNull(Sum(V2.PS_New),0))/T.PSaver_Target END AS PS_Perf, Sum(V2.Upgrade_PlanInc) AS UGPlanInc, Sum(V2.Other_New) AS OtherNEW, (IsNull(Sum(V2.LeaderBill),0) + IsNull(Sum(V2.LeaderBillSimOnly),0) + IsNull(Sum(V2.FR_New),0) + IsNull(Sum(V2.PS_New),0) + IsNull(Sum(V2.Other_New),0)) AS TotalNew, (IsNull(Sum(V2.FR_Upgrade),0) + IsNull(Sum(V2.PS_Upgrade),0)) AS TotalUG, (IsNull(Sum(V2.FR_New),0) + IsNull(Sum(V2.PS_New),0) + IsNull(Sum(V2.FR_Upgrade),0) + IsNull(Sum(V2.PS_Upgrade),0)) AS Handsets FROM (VW_Conn_Summary_CC V2 INNER JOIN CallCentreReps CCR ON V2.Rep_Code = CCR.Rep_Code AND V2.Invoice_Date >='01-Sep-2001' AND V2.Invoice_Date <='10-Sep-2001') LEFT JOIN tbl_StoreTargets T ON T.Territory=900 AND T.ST_Month = '01-Sep-2001' GROUP BY CCR.Dept, T.FRunner_Target, T.PSaver_Target, T.Profit_Target ORDER BY Profit DESC

This query is (not surpisingly) very slow. I have heard that you can get much better performance by creating a view. The trouble is views are hard-coded, aren't they ? This query relies on parameters to be entered for date (and in some instances 'Territory'), so I build it on the fly.

What could I look at doing here to improve performance ?

What about creating indices ?

You can probably tell I'm struggling here, so any suggestions are like gold to me.

Brett.

 

[tapks]

Brett!

First of all if you create a store procedure for this query then the performance will be better as the stored procedures are compiled onces.

NExt, if you can create the indexes then it will be much faster.

Hope this will work out for better performance.

TapanKS

 

[brettgab]

Thanks tapks

OK, so you mention creating indices, there are about four fields that this query can be sorted on.

Given this query is a query over a view, what do I do ? Do I need to create nonclustered indices for the tables I'm querying, or do I create seperate views with different order by clauses ?

 

[tapks]

Don't create a view. Create a store procedure & create a non clustured indices on the tables you are quering.

That will do.

 

[tlbroadbent]

I have several recommendations. [ol][li]Check thread183-129404 for a link to an excellent performance-tuning article. It is a must read if you haven't done much query tuning.

[li]Add indexes (or indices, if you prefer) to the tables to cover the criteria in the WHERE and ON clauses. When creating an index from multiple columns, put the most selective columns first in the index. A clustered index is often beneficial for range searches such as the date search in your query.

[li]Change the order of the IsNull and Sum functions or you’ll get incorrect results.

Currently:
IsNull(Sum(V2.FR_Upgrade),0)

Should be:
Sum(IsNull(V2.FR_Upgrade,0))

[li]In the 2nd query of the UNION you have not defined the relationship between VW_Conn_Summary_CC and tbl_StoreTargets. In the first query that relationship is defined by &quot;ON V.Territory_Code = T.Territory.&quot; Without that relationship, SQL will match every record from the first join to every record in tbl_StoreTargets, which could result in a huge result set. Of course, if only one row from tbl_StoreTargets is selected then this is not necessary.

[li]You’ve mixed JOIN criteria and Selection criteria. The selection criteria belongs in a WHERE clause. Even though SQL Server’s parser and compiler accept that syntax, it is incorrect and may provide incorrect results.

Change the 1st query as indicated below.

Current:

FROM (VW_Conn_Summary_NCC V
INNER JOIN system_table S
ON V.Territory_Code = S.sys_tbl_code
AND S.sys_tbl_type = 'TC'
AND V.Invoice_Date >='01-Sep-2001'
AND V.Invoice_Date <='10-Sep-2001')
LEFT JOIN tbl_StoreTargets T
ON V.Territory_Code = T.Territory
AND T.ST_Month = '01-Sep-2001'

Should be:

FROM (VW_Conn_Summary_NCC V
INNER JOIN system_table S
ON V.Territory_Code = S.sys_tbl_code
LEFT JOIN tbl_StoreTargets T
ON V.Territory_Code = T.Territory
WHERE S.sys_tbl_type = 'TC'
AND V.Invoice_Date >='01-Sep-2001'
AND V.Invoice_Date <='10-Sep-2001')
AND T.ST_Month = '01-Sep-2001'

Change the 2nd query as indicated below.

Should be:

FROM (VW_Conn_Summary_CC V2
INNER JOIN CallCentreReps CCR
ON V2.Rep_Code = CCR.Rep_Code)
LEFT JOIN tbl_StoreTargets T
/* Remove the next line if incorrect */
ON v2.Territory_Code = T.Territory
WHERE V2.Invoice_Date >='01-Sep-2001'
AND V2.Invoice_Date <='10-Sep-2001'
AND T.Territory=900
AND T.ST_Month = '01-Sep-2001'

[li]I'd use the view, even if creating a stored procedure. Of course, I'd optimize the view separately before trying to optimize this query.

[li]If these recommendations don't provide sufficient improvement, then insert the results of the 1st query into a temporary table. Then insert the results of the 2nd query in to the same table. Eliminate the UNION query. Union queries are usually very inefficient.

Of course, the UNION as written eliminates duplicate values that might occur with the two queries. If you want to continue this behavior when using a temp table, add a unique constraint to the temp table. If you want to eliminate this behavior in the UNION query and perhaps improve performance somewhat, add ALL to the UNION statement.

UNION ALL
[/ol]Hope some of this helps. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[brettgab]

Terry,

You are certainly a wealth of knowledge on SQL, how long have you been doing it for ?

Thanks for your suggestions. I have done what you said points 2,3,4 and 5.

Point 6....I've tried creating a storod procedure. When I run this query (with no stored procedure), it takes around 40 seconds and does NOT time-out. When I put the query into a stored procedure, it times out after 30 seconds. I have done a keyword search on this topic and as a result have inserted the lines

DBObj.CommandTimeout = 300
DBObj.ConnectionTimeout = 300

Any ideas why this is still timing out after 30 seconds ? I am connecting to the database with a dsn-less connection

DBObj.Open &quot;Driver={SQL Server};Server=MWC_FS;Database=MWC;UID=**;PWD=*******&quot;

Also for point 6, I've never used temporary tables before (you use a # character or something, don't you?) so I'm a bit sceptical about doing this, unless there is a good example to follow which does the same thing you're suggesting. FYI, the above query creates about 35 lines in the first query and then does a UNION with one line (and this will only ever be one line). Given that, is it worth making the change to temporary tables ?

Congratulations on this site and your input, I have never encountered anyone so diligent in helping strangers with their computing problems before.

 

[tlbroadbent]

Sorry for the delay in getting back to you.

How does the 40 seconds compare to the time that the query took previously?

I don't think the temporary table will improve performance in your case. How many records are processed to get the final result?

It appears the settings in your program should handle the timeout. I believe that I saw something once about problems with stored procedure timeouts on a ceratin SQL Server Service Pack. However, I can't remember the details and cannot find a reference now. Which SQL version are you running and which service pack is installed? Also, which MDAC version and service pack are installed? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.