Performance way to calculate Balance

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

Transact-SQL (2008)

Performance way to calculate Balance

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2012-04-06 : 10:56:26

Hi ,

I've a table with :

ID tipo Valor
1 E 100
2 S 100
3 E 200
..

I need to extract data with a Virtual Column "balance"
I created a Function to calculate the balance but it's poor in perfomance.
Column "Tipo" determines if add or subtract to the balance.

Something like this:

ID tipo Valor balance
1 E 100 100
2 S 100 0
3 E 200 200

What is the best way to calculate a balance without using Temp Tables?

Regards

Pedro

Query:

select id,valor , saldo=RetSaldo(id)
from table1

Function retsaldo(@nseq INT)
)
RETURNS MONEY
AS
BEGIN
DECLARE @vl MONEY

SELECT @vl=SUM(valor_mov * CASE WHEN tipo='E' THEN 1 ELSE -1 END) FROM table1
WHERE id <= @nseq
RETURN @vl

END

psfaro
Starting Member

49 Posts

Posted - 2012-04-06 : 11:17:40

Hi ,

It's better in perfomance like this

select id,valor ,
saldo=(select SUM(valor * CASE WHEN tipo='E' THEN 1 ELSE -1 END) FROM table1 tb2 "+;
WHERE tb2.id <= table1.id)"+;
from table1

Regards

Pedro

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:18:49

see scenario 1

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-04-08 : 17:34:54

quote:
Originally posted by psfaro
What is the best way to calculate a balance without using Temp Tables?

What you've run into is a nasty little bit of "Hidden RBAR" known as a "Triangular Join". Please see the following URL for an article written specifically about the problem you're having with performance.
[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]

The solutions offered by visakh16 and psfaro have the same problem built in because they both use an inequality in one form or another of a correlated subquery (APPLY isn't much more than a correlated subquery).

Of course, this problem goes away with the new features of SQL Server 2012 but, assuming that you don't have that, I need to know 2 things...

1. Why can't you use a Temp Table? You've just killed one of the most effective methods there (million rows calculated in less than 3 seconds) is for doing running totals by saying you can't use one.

2. What's the max number of rows you have for any given "tipo"?

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources