T-SQL help needed

[akira220984]

Hi,

I'm new to this forum and to sql server / tsql in general, so go easy on me

Below I have some code which I have been asked to optimise as it runs far too slow. If there any pointers as to how I could go about this, it would be greatly appreciated.

If you need any other information, please ask and I will reply asap.

update ledgerTestRS
set RunningBalance =
(select sum(total)
from ledgerTestRS as innerLoop
where innerLoop.company_name = outerLoop.company_name
and innerLoop.ledger_name = outerLoop.ledger_name
and innerLoop.longtransactiondate <= outerLoop.longtransactiondate)
from ledgerTestRS outerLoop
inner join #tmpchanges on (outerLoop.ledger_id = #tmpchanges.ledger_id)

Ric

 

[gmmastros]

Believe it or not, running totals is the one exception where a cursor performs better than a set based approach.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Read this discussion on MSDN forum

Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?

PluralSight Learning Library

 

[akira220984]

Hi gmmastros and markros,

thank you both for your replies.

Markros, as I'm new to this sql server business, I'm not sure exactly what is going on in the article you wanted me to read, and do not know how to implement a solution based on what I need

More experience on my behalf is needed.

 

[markros]

declare @RunningTotal decimal(18,5)

set @RunningTotal = 0

update ledgerTestRS set @RunningTotal = RunningBalance = RunningBalance + @RunningTotal

PluralSight Learning Library