Problem with slow running SQL

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