Optimize TSQL?!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimize TSQL?!!

Author

Topic

mem
Starting Member

28 Posts

Posted - 2004-04-20 : 18:03:33

Hello,

I'm having some issues with a procedure running a little slower than expected. Actually, it's a lot slower than expected.

Here's the setup....

4 tables

Manager table
Supervisor table
Representative table
Sales transactions table

Ok, the procedure produces a report that displays sales totals for all supervisors assigned to a specific manager. The sales totals are derived from the sales transactions made by each representative. So, Manager A will show all his supervisors totals based on their representatives sales transactions. Also, this is a monthly report.

If that made any sense, here's the TSQL:

ALTER PROCEDURE dbo.MonthlyMgrReport
@varSession_ID int,
@varReport_Month datetime

AS
BEGIN
SET NOCOUNT ON

DECLARE @varMgr_ID int
SET @varMgr_ID = (SELECT Mgr_ID FROM tblManagers WHERE Session_ID = @varSession_ID)

SELECT Q1.Sup_Name AS 'Supervisor',
Number_Of_CN AS 'Contract Renewals',
Number_Of_NS AS 'New Services',
Number_Of_PP AS 'Pre-Paids',
Total_Sales AS 'Total Sales',
IsNull(CASE Total_Sales WHEN 0 THEN 0 ELSE CAST( CAST(Number_Of_CN AS decimal(8,2)) / CAST(Total_Sales AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS 'Contract Renewal % Pene',
Monthly_Goal AS 'Goal',
CASE IsNull(Monthly_Goal, 0) WHEN 0 THEN 0 ELSE CAST( CAST(Total_Sales AS decimal(8,2)) / CAST(Monthly_Goal as decimal(8,2)) * 100 AS decimal(8,2)) END AS '% Goal',
Number_Of_Data AS 'Data',
IsNull(CASE Total_Sales WHEN 0 THEN 0 ELSE CAST( CAST(Number_Of_Data AS decimal(8,2)) / CAST(Total_Sales AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS 'Data % Pene',
IsNull(CASE Total_Sales WHEN 0 THEN 0 ELSE CAST( (CAST(Number_Of_Data AS decimal(8,2)) + CAST(Number_Of_NData AS decimal(8,2))) / CAST(Total_Sales AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS 'EF/TS % Pene',
IsNull(Acc_Count, 0) AS 'Acc',
IsNull(CASE Total_Sales WHEN 0 THEN 0 ELSE CAST( CAST(Acc_Count AS decimal(8,2)) / CAST(Total_Sales AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS 'Acc/TS % Pene',
IsNull(CASE Acc_Count WHEN 0 THEN 0 ELSE CAST( CAST(Acc_ComboPack AS decimal(8,2)) / CAST(Acc_Count AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS 'Acc CP/Acc % Pene',
IsNull(CASE Handset_Count WHEN 0 THEN 0 ELSE CAST( CAST(Acc_Count AS decimal(8,2)) / CAST(Handset_Count AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS 'Acc/HS % Pene',
IsNull(CASE Handset_Count WHEN 0 THEN 0 ELSE CAST( CAST(Acc_Revenue AS decimal(8,2)) / CAST(Handset_Count AS decimal(8,2)) AS decimal(8,2)) END, 0) AS 'Acc Rev/HS',
IsNull(CASE Handset_Count WHEN 0 THEN 0 ELSE CAST( CAST(Handset_Count_GT_79 AS decimal(8,2)) / CAST(Handset_Count AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS '$79 HS % Pene',
IsNull(CASE Total_CN_NS WHEN 0 THEN 0 ELSE CAST( CAST(AR_Count_GT_45 AS decimal(8,2)) / CAST(Total_CN_NS AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS '$45 % Pene',
IsNull(CASE Total_CN_NS WHEN 0 THEN 0 ELSE CAST( CAST(AR_Count_GT_55 AS decimal(8,2)) / CAST(Total_CN_NS AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS '$55 % Pene',
IsNull(CASE Number_Of_NS WHEN 0 THEN 0 ELSE CAST( CAST(TwoYR_Count AS decimal(8,2)) / CAST(Number_Of_NS AS decimal(8,2)) * 100 AS decimal(8,2)) END, 0) AS '2 Yr % Pene'
FROM
(
SELECT TOP 100 PERCENT tblMonthlyReporting_Sups.Sup_FName + ' ' + tblMonthlyReporting_Sups.Sup_LName AS Sup_Name
SUM(CASE WHEN tblTransactions.Sales_Type_ID = 2 THEN 1 ELSE 0 END) AS Number_Of_CN,
SUM(CASE WHEN tblTransactions.Sales_Type_ID = 1 THEN 1 ELSE 0 END) AS Number_Of_NS,
SUM(CASE WHEN tblTransactions.Sales_Type_ID = 4 THEN 1 ELSE 0 END) AS Number_Of_PP,
SUM(CASE WHEN tblTransactions.Sales_Type_ID <> 4 THEN Data_Feature_Count ELSE 0 END) AS Number_Of_Data,
SUM(CASE WHEN tblTransactions.Sales_Type_ID <> 4 THEN NData_Feature_Count ELSE 0 END) AS Number_Of_NData,
SUM(CASE WHEN tblTransactions.Sales_Type_ID = 1 THEN 1 WHEN tblTransactions.Sales_Type_ID = 2 THEN 1 WHEN tblTransactions.Sales_Type_ID = 4 THEN 1 ELSE 0 END) AS Total_Sales,
IsNull(SUM(Acc_Quantity), 0) AS Acc_Count,
SUM(CASE WHEN Combo_Pack = 1 THEN 1 ELSE 0 END) AS Acc_ComboPack,
IsNull(SUM(Acc_Revenue), 0) AS Acc_Revenue,
SUM(CASE WHEN tblTransactions.Handset_Revenue <> 0 AND Service_Only = 0 THEN 1 ELSE 0 END) AS Handset_Count,
SUM(CASE WHEN tblTransactions.Handset_Revenue >= 79.99 AND Service_Only = 0 THEN 1 ELSE 0 END) AS Handset_Count_GT_79,
SUM(CASE WHEN tblTransactions.Access_Rate >= 45 THEN 1 ELSE 0 END) AS AR_Count_GT_45,
SUM(CASE WHEN tblTransactions.Sales_Type_ID BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS Total_CN_NS,
SUM(CASE WHEN tblTransactions.Access_Rate >= 55 THEN 1 ELSE 0 END) AS AR_Count_GT_55,
SUM(CASE WHEN tblTransactions.Contract_Length = 2 AND tblTransactions.Sales_Type_ID = 1 THEN 1 ELSE 0 END) AS TwoYR_Count
FROM tblMonthlyReporting_Sups
LEFT JOIN tblMonthlyReporting_Reps ON tblMonthlyReporting_Sups.Sup_ID = tblMonthlyReporting_Reps.Sup_ID
AND tblMonthlyReporting_Reps.Reporting_Month = @varReport_Month
AND tblMonthlyReporting_Reps.Term = 0
AND tblMonthlyReporting_Reps.Active = 1
LEFT JOIN tblTransactions ON tblMonthlyReporting_Reps.Session_ID = tblTransactions.Session_ID
AND CONVERT(varchar, DATEADD(day, (-CAST(DAY(tblTransactions.Order_Date) AS int)+1), tblTransactions.Order_Date), 101) = @varReport_Month
AND tblTransactions.Active = 1
WHERE tblMonthlyReporting_Sups.Mgr_ID = @varMgr_ID
AND tblMonthlyReporting_Sups.Reporting_Month = @varReport_Month
AND tblMonthlyReporting_Sups.Term = 0
AND tblMonthlyReporting_Sups.Active = 1
GROUP BY tblMonthlyReporting_Sups.Sup_LName, dbo.tblMonthlyReporting_Sups.Sup_FName
ORDER BY tblMonthlyReporting_Sups.Sup_LName
) AS Q1

SET NOCOUNT OFF
END

Ok, well I'm looking to optimize this. Any suggestions wold be appreciated.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-20 : 18:53:54

How long does it take to execute? Based upon its complexity, I would say anything under 30 seconds is pretty fast.

Also, why are you using TOP 100 PERCENT in your derived table?

Are your JOIN conditions properly indexed? How about the columns in the WHERE clause? Do you have a report server to run this query on so that you don't affect production? Have you run a trace on this and then sent the trace through the index tuning wizard?

Tara

mem
Starting Member

28 Posts

Posted - 2004-04-20 : 19:46:21

The report runs about 40 secs. I was hoping to get it in the 20 sec range. Although, this report retrieves around 20,000 records.

The top 100 percent is for an order by. Sorry, that was from a old version of this query.

Yes, the joins are indexed. Columns too. Yes, I've tracing reports and match the indexes based on that.

This report runs on the same server and is accessed via asp. That is my main reason for lowering the query times. It's difficult for a user to sit there for 40 seconds and wait for a report to generate.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-20 : 19:53:46

Why not limit the result set to a more reasonable number and use paging? So the would see let's say 100 rows per page. And to get to the next set, they hit next. So only return the 100 rows per page. 20,000 rows being returned over the wire is going to be slow regardless of your query.

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-20 : 20:02:04

Kick it off as a separate process from ASP and e-mail them the final output. Makes the "30 second" rule go away.

Besides, what happens in a few months when there's more data in the system? It's only going to get slower from here on out.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mem
Starting Member

28 Posts

Posted - 2004-04-20 : 20:10:22

tara,

the only problem with paging is totals. Paging through a report that might have totals is kinda confusing.

Micheal,

I believe your right. Sending reports view email or personal folders would a be good option. I guess if they want to view the report via the web they could wait. small price to pay ;)
Although, these reports are for sales/marketing people. They're a little needy. Which makes my life difficult.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-20 : 20:36:10

Mem, I know exactly what you mean.
As far as the waiting goes, if you do this in ASP, and your ASP page times out, they get nothing. That's why I was suggesting making all reports a "queued up" type thing. I've written a system like that in asp and asp.net. it wasn't easy to write, but I never get e-mails about reports timing out etc. They can run as big of a report as they like. It will get e-mailed to them evenutally onces it's done. While that report is processing, the web servers are still able to serve up pages because they are not grinding on a report.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mem
Starting Member

28 Posts

Posted - 2004-04-20 : 20:59:18

How did you distribute the reports? Via message queue? With SQL server jobs? Or maybe a service?

What was the format of the reports? PDF, excel, cvs, txt?

I guess the network guys didn't limit the size of the emails? I would depending on the amount of data the reports could still be fairly large...

I think your process is probably the only viable solution. Writing some type of scheduler that users could enter in times, distribution lists, etc. would be ideal. Although, it would be a lot of code.

Ton of work = semi-job security ;)

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-21 : 15:47:35

I distributed reports via an e-mail queue that was read by a windows service that sends the e-mails.

What format? All of those and them some. I let teh user pick what format they want. The reporting package (Crystal, Active Reports, etc) handles the converstion.

For a one time report, I just dropped a record into a report queue, which then dropped a record into the e-mail queue once the report was finished. I had a separate system that handled creating records in the report queue at scheduled times for recurring reports.

It's a lot of work, but if you do it right it's a generic system that you can use for years.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-04-22 : 19:07:41

With 20,000 rows for 1 month, I suspect the tblTransactions table contains a few hundred thousand rows. You need to prune that down quickly.

The WHERE clause you have can not use an index on the Order_Date field:

AND CONVERT(varchar, DATEADD(day, (-CAST(DAY(tblTransactions.Order_Date) AS int)+1), tblTransactions.Order_Date), 101) = @varReport_Month

Try changing it to:

AND CONVERT(varchar, DATEADD(day, (-CAST(DAY(tblTransactions.Order_Date) AS int)+1), tblTransactions.Order_Date), 101) = @varReport_Month
AND tblTransactions.Order_Date BETWEEN @varReport_Month AND DATEADD(ss,-1,DATEADD(mm,1,@varReport_Month))

That should allow an index on Order_Date to be used. I assume you have an index. This also assumes the period for transactions for the report is from the first day of the month (passed as @varReport_Month) thru the last second of the last day of the month. Adjust for your business rules.

I would think this query should return in less than 10 seconds.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources