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_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

GO

Many 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/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, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp