Running Total, long calculation time. Faster? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running Total, long calculation time. Faster?

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-05-02 : 19:50:18

Hi,

I was wondering is there any faster way in T-SQL to get a running total faster then this way:

CREATE TABLE tbl_Sensor5
Time smalldatetime
Power integer

CREATE VIEW/SP EnergyDay
SELECT
Time,
Power,
(Select SUM(Power) FROM tbl_Sensor5 as B
WHERE B.Power <= A.Power) as
RunningTotal
FROM
tbl_Sensor5 as A
<WHERE time.day = today()>

Result should be like

If I use this method to get my energy-logging online (sql2008Express-reporting services), I'm getting report loading times up to 2 minutes, which is way to much.

I can not change the data entry into the table, this is standard software, I can not fit some VB code to get an upsumming column in the table.

Is there any other way to get this running total faster out of this table? Via stored procedures and some variable up-summing code? Don't know I am new to this, but I can't imagine companies having such long loading times just to get a simple running total column.

Any standard ways? Examples? I have been strungling with this problem a long time but nowhere found a solution.

Please help,

Best regards,

Joris

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-02 : 20:06:46

Make sure Sensor5.power has a clustered index.


Select
 a.Time
,a.Power
,sum(b.Power) as [Running total]
from
TBL_Sensor5 a
Cross Join
TBL_Sensor5 b
where
a.Power >= b.Power
group by 
a.Time,a.Power

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-05-03 : 05:21:28

If I use that syntax it even takes longer, I have to cancel execution because it never seems to end...

How to set this clustered index? In fact, i'm not using tbl_sensor5 there but another query that contains all sensors.

And I'm not using a typical SQL2008 DB it's a linked Access DB which i'm not autorised to change.

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-05-03 : 07:12:15

Problem solved by searching this forum on running total.
Using Stored Procedure Variables. Loading time 1-2 secs.

ALTER PROCEDURE [dbo].[GrafiekVandaag]
AS
BEGIN

SET NOCOUNT ON;

Create Table #GrafiekVandaag (Tijd datetime, Verbruik real, [Verbruik Totaal] real, Opbrengst real, [Opbrengst Totaal] real)

INSERT INTO #GrafiekVandaag (Tijd, Verbruik,[Verbruik Totaal], Opbrengst, [Opbrengst Totaal])
SELECT Tijd, Verbruik, 0, Opbrengst, 0 FROM

(select top 100 percent Sensor1.Time_rounded as Tijd, (12*1000/75)*Sensor1.Data_edit as Verbruik, 12*Sensor5.Data_edit AS Opbrengst
FROM

(AccessDBEnergyProf...Sensor1 LEFT OUTER JOIN
AccessDBEnergyProf...Sensor5 ON Sensor1.Time_rounded = Sensor5.Time_rounded)

WHERE
YEAR(Sensor1.Time_rounded) = YEAR(getdate()) and
MONTH(Sensor1.Time_rounded) = MONTH(getdate()) and
DAY(Sensor1.Time_rounded) = DAY(getdate())

ORDER BY
Sensor1.Time_rounded) as AccessTbl

ORDER BY
Tijd

DECLARE @VerbruikTotaal real
SET @VerbruikTotaal = 0
UPDATE #GrafiekVandaag
SET @VerbruikTotaal = [Verbruik Totaal] = @VerbruikTotaal + Verbruik/12000

DECLARE @OpbrengstTotaal real
SET @OpbrengstTotaal = 0
UPDATE #GrafiekVandaag
SET @OpbrengstTotaal = [Opbrengst Totaal] = @OpbrengstTotaal + Opbrengst/12000

SELECT * FROM #GrafiekVandaag ORDER BY Tijd

DROP TABLE #GrafiekVandaag
END

Thanks for your reply!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-03 : 12:47:20

Even faster

WHERE Sensor1.Time_rounded >= DATEDIFF(DAY, 0, getdate())
and Sensor1.Time_rounded < DATEDIFF(DAY, -1, getdate())

Also make sure you have a proper clustered index on #grafiekvandaag table!

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources