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_Stats
@.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_Wholesalers'
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.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 8) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '
if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '
if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.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.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 15) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '
if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '
if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.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.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 32) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '
if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '
if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.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.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 366) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '
if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '
if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.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
GOMany Thanks
I'll have a look at making those changes.
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E5F2C02272EYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.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(@.wholesalers)
> You find the code for iter_intlist_to_table at
> http://www.algonet.se/~sommar/array...ist-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, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
