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