Problem with writting a Report (SQL slow running)

[Steve Thorpe]

Hi

I am trying to write a report that calculates the average number of sales
over 7, 14, 31 and 365 days for each hourly period of the day. the problem
is it takes over 4 minutes to run.

e.g.
Average Xactions per Hour
7 Days 14 Days 31 Days 365 Days
00:00 - 01:00 1,141.6 579.2 261.6 28.8
01:00 - 02:00 1,298.0 649.6 293.4 30.0
The report was use to be purely ASP running SQL Statements.
I then changed it to ASP Running a SP 24 times - this reduced running time
by about 1 minute.
I then changed it so that the stored proc looped internally 24 times and
returns the data.

I have ran the Index Tuning Wizard on the SQL and Implemented the indexes
suggested - this actually increase execution time by 20 seconds.
Below is the stored procedure I am currently using that loops internally 24
times and returns the data.

Can anyone suggest a better way / any improvements I could make ?
Many Thanks

Steve

----------------------------------------------------------------------------
-------------------------

CREATE procedure ams_RPT_Gen_Sta ts
@strResult varchar(8) = 'Failure' output,
@strErrorDesc varchar(512) = 'SP Not Executed' output,
@strTest varchar(1),
@strCurrency varchar(3),
@strVFEID varchar(16)
as
declare @strStep varchar(32)

set @strStep = 'Start of Stored Proc'

/* start insert sp code here */
create table ##Averages (
TheHour varchar(2),
Day7Avge float ,
Day14Avge float ,
Day31Avge float ,
Day365Avge float
)
declare @numHour varchar(2)
declare @strSQL varchar(2000)
declare @Wholesalers varchar(64)

declare MyHours cursor FORWARD_ONLY READ_ONLY for
select convert(char(2) , timestamp,14) as TheHour
from xactions
group by convert(char(2) , timestamp,14)
order by convert(char(2) , timestamp,14)
if @strTest = 'Y'
select @Wholesalers = VALUE FROM BUSINESSRULES WHERE NAME =
'TEST_Wholesale rs'
open MyHours

fetch next from MyHours into @numHour

while @@fetch_status = 0

begin
set @strSQL = 'insert into ##Averages (TheHour, Day7Avge) ( select ''' +
@numHour + ''', ' +
'count(*) / 7.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAIL ERID = RETAILER.RETAIL ERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timest amp , GETDATE() ) < 8) and ' +
'xactions.xacti ontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' '

if @strTest = 'Y'
set @strSQL = @strSQL + ' and retailer.BillOr gID not in (' +
@Wholesalers + ') '

if @strCurrency <> '*'
set @strSQL = @strSQL + ' and xactions.XACTIO NCURRENCY = ''' +
@strCurrency + ''' '

if @strVFEID <> '*'
set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''''
set @strSQL = @strSQL + ')'

exec ( @strSQL )
set @strSQL = 'update ##Averages set Day14Avge = ( select ' +
'count(*) / 14.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAIL ERID = RETAILER.RETAIL ERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timest amp , GETDATE() ) < 15) and ' +
'xactions.xacti ontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' '

if @strTest = 'Y'
set @strSQL = @strSQL + ' and retailer.BillOr gID not in (' +
@Wholesalers + ') '

if @strCurrency <> '*'
set @strSQL = @strSQL + ' and xactions.XACTIO NCURRENCY = ''' +
@strCurrency + ''' '

if @strVFEID <> '*'
set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' '

set @strSQL = @strSQL + ') where TheHour = ''' + @numHour + ''' '

exec ( @strSQL )
set @strSQL = 'update ##Averages set Day31Avge = ( select ' +
'count(*) / 31.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAIL ERID = RETAILER.RETAIL ERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timest amp , GETDATE() ) < 32) and ' +
'xactions.xacti ontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' '

if @strTest = 'Y'
set @strSQL = @strSQL + ' and retailer.BillOr gID not in (' +
@Wholesalers + ') '

if @strCurrency <> '*'
set @strSQL = @strSQL + ' and xactions.XACTIO NCURRENCY = ''' +
@strCurrency + ''' '

if @strVFEID <> '*'
set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' '

set @strSQL = @strSQL + ' ) where TheHour = ''' + @numHour + ''' '

exec ( @strSQL )
set @strSQL = 'update ##Averages set Day365Avge = ( select ' +
'count(*) / 365.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAIL ERID = RETAILER.RETAIL ERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timest amp , GETDATE() ) < 366) and ' +
'xactions.xacti ontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' '

if @strTest = 'Y'
set @strSQL = @strSQL + ' and retailer.BillOr gID not in (' +
@Wholesalers + ') '

if @strCurrency <> '*'
set @strSQL = @strSQL + ' and xactions.XACTIO NCURRENCY = ''' +
@strCurrency + ''' '

if @strVFEID <> '*'
set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' '

set @strSQL = @strSQL + ' ) where TheHour = ''' + @numHour + ''' '
exec ( @strSQL )

fetch next from MyHours into @numHour

end -- while fetch

close MyHours
deallocate MyHours

select * from ##Averages order by TheHour

drop table ##Averages
/* end insert sp code here */

if (@@error <> 0)
begin
set @strResult = 'Failure'
set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' +
CONVERT(VARCHAR ,@@Error)
return -1969
end
else
begin
set @strResult = 'Success'
set @strErrorDesc = ''
end

return 0

GO

[Steve Thorpe]

Many Thanks

I'll have a look at making those changes.

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Steve Thorpe (st***********@ nospam.hotmail. com) writes:

I am trying to write a report that calculates the average number of
sales over 7, 14, 31 and 365 days for each hourly period of the day.
the problem is it takes over 4 minutes to run.

Troubleshooting performance problems over newsgroups is often difficult
because, without access to the database it's not possible to test
various scenarios. And without complete knowledge about the tables
involved it is even more difficult. Just seeing the procedure code,
may sometimes be sufficient, but not always.

Anyway, I have two suggestions for your procedure, although none of
them are likely to improve performance radically.

The first is that you use a global temptable. Change this to a local
temp table. This avoids problems if two users run this procedure
simultaneously.

The other is that you rewrite the procedure to not use dynamic SQL.
As far as I can see, the only reason that you use dynamic SQL, is
that you intended to have a comma-separated list in @Wholesalers.

I would suggest that you handle the list like this:

CREATE TABLE #wholesalers (id int NOT NULL)
INSERT #wholesalers (id)
SELECT number FROM iter_intlist_to _table(@wholesa lers)

You find the code for iter_intlist_to _table at
http://www.algonet.se/~sommar/arrays...st-of-integers.

Armed with this temp table, you can, as far as I can see, rip out the
dynamic SQL and replace it with static. This may give some performance
benefit, but only some single second.

However, once you have rewritten the code into static SQL, it will be
more pleasant to take a look at it.

It also helps if you post CREATE TABLE and CREATE INDEX statements
for the involved table. Some hints about data sizes is also good.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp