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 0 2757 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Allcomp |
last post by:
Hello,
I have seen something strange on a customer's computer.
It is a P4 3 GHz with 512 MB Ram running on a Win2K SP3
When he uses a part of my application, it is really slow (more than 10
sec for something that I do in 1 sec. (in VB6) with an older computer),
he also have a second computer where it is really fast (one second too).
When I look at in the task manager, it seem that Idle process is always
in between 80 and 99%
|
by: Eric J. Holtman |
last post by:
I feel like the answer to this should be blinding obvious. I also
feel like it's probably an exercise in an undergraduate database
design course. I'm off to google for an answer, but I figure I'd
throw it out here to see if there's a quick and dirty solution.
I have in my database a table of every position we have here called
Holdings. It would be equivalent to an Inventory table in every
basic design course.
There are many users...
|
by: Magdelin |
last post by:
Hi,
I accidently uninstalled IIS 5.1 on my XP Pro workstation and I think I
messed up my .Net framework. Then, I reinstalled IIS and ran aspnet_regiis
-i. After this, all asp.net applications except one worked normally. This
application is different from other as it is distributed (uses .Net
Remoting), uses Crystal Reports 9 for web reports, uses Infragistics
NetAdvantage suite controls and windows integrated authetication. The good...
|
by: Brian Russell |
last post by:
We have three servers (beyond my development box) in our organization.
The first is a testing server that has IIS and SQL Server on it. The
second is another testing server that also has IIS and SQL Server. The
final is the production box that only has IIS. I develop on my own
machine, copy to the first testing server, the code is tested, copy the
code from testing 1 to testing 2, the code is tested again, and then
copy testing 2 code...
|
by: Aykut Canturk |
last post by:
We perfectly develop our applicaitons with VS 6.0. We want to move to .net
but all of our development computers almost die with a small test
application. we have min P4 2ghz and 512MB desktop PCs with huge RAM
support, we use windowsXP as op.system but they don't work wery well. speed
id too different. I don't mention about our notebooks. also they have min P4
1.6 with 512 RAM but they also die.
Our customers also need Pc upgrade if we...
| |
by: Geoff Jones |
last post by:
Hi All
I hope you'll forgive me for posting this here (I've also posted to ado site
but with no response so far) as I'm urgently after a solution.
Can anybody help me? I'm updating a table on a database i.e. I've modified
the table in a DataSet and I want to update it to the SQL database which the
table originally came from. I'm using a data adaptor and the update command
and it works BUT it is soooooooooo slow!!! Can anybody tell me...
|
by: Fredrik Melin |
last post by:
Running a solution with about 20 sub-projects total about 150.000 lines of
code.
Typing code is slow (delay when typing fast).
Pressing next line while debugging is slow.
Pressing Ctrl-F for quick find is rediculous slow.
Everything is slow.
Running Exe is fine though
This is on a HP Workstation machine with Xeon 3GHz (HT Enabled) with 2 GB
|
by: billmiami2 |
last post by:
I'm experiencing a strange problem that I believe is related to ADO.NET
but I can't say for sure.
I have a simple ASP.NET reporting interface to a SQL Server 2000
database. One report that we run returns a listing of community
members and their contact information using a stored procedure.
Depending on the selected community, this can return from a hundred to
over 1000 rows. Occasionally, the report stops running when a
community with...
|
by: HC |
last post by:
Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just call it
Express for simplicity).
I have, to try to simplify things, put the exact same DB on two
systems, one running MSDE and one running Express. Both have 2 Ghz
processors (one Intel, one AMD), both have a decent amount of RAM
(Intel system...
|
by: Salad |
last post by:
I have access, for testing at my client's site, a Win2000 computer
running A2003 retail. He recently upgraded all of his other machines to
DualCore Pentiums with 2 gig ram and run A2003 runtime. I believe all
current SPs for Windows and Office are installed on the fast machines.
I have 1 process/subroutine that has worked for a couple of years
without a problem. It works fine on the testing (slow) machine. The
process checks a folder...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |