SQL Performance with Aggregete Sum on LOTS of data

Question

SQL Performance with Aggregete Sum on LOTS of data

spiegdon

Old Hand

Points: 305
More actions
September 25, 2007 at 9:51 am

#198316

Ok... I have a question and am at my wits end about this - here is the scenario:
Ledger Transactions have 1..* Ledger Transaction Line Items
Ledger Transactions have Dates
Ledger Transaction Line Items have 1..1 Ledger Accounts
Ledger Transaction Line Items have Credit and Debit money values
I need to be able to get a view with a "running total" if you will.
The tricky part is that we need to be able to do it in a "point-in-time" fashion. I.e. the beginning balance was 100 on March 15th.
I was able to achieve this with a self-referencing inner join on a temp table populated with the aforementioned key columns. This worked well for the development environment.
Prior to release, I dumped 13000 Ledger Transactions each with two Line Items hitting one of three accounts.
The view now takes 9 minutes to run!
Any thoughts - PLEASE let me know as soon as possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

I use the technique described in the Guru's Guide to Transact-SQL for this type of issue. You can find the solution reprinted here:

http://www.sqlteam.com/article/calculating-running-totals

Phil Factor SSC-Insane Points: 20244 More actions · Answer 2

Have a look at the SQL Server cusrsor workbench on

http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/ which discusses the 'Running total' problem

If you look at the 'Quirky Update' solution, this should tell you the quickest approach in TSQL. Yes, it works and works well!

Best wishes,
Phil Factor

spiegdon Old Hand Points: 305 More actions · Answer 3

Thanks very much for the quick response, I am sorry for the long delay in ack.

We ended up going with the cursor approach as all we are iterating through is the keys at that point anyway. I then do some basic joins to bring out some friendly data. Thanks a lot!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Sounds like a good opportunity for a small datamart for reporting! If you denormalized or perhaps star-schema'd this data it would likely report much more quickly.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

spiegdon Old Hand Points: 305 More actions · Answer 5

No idea what star-schemaed means. Can you please elaborate?

Thanks!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Star Schema is a reporting structure for data where you have fact data (invoice details for example) in a main table that is key-referenced to one or more dimension tables (such as product, time, customer). They tend to be fairly denormalized and can thus report some aggregates fairly efficiently. These structures are common in OLAP and reporting systems.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

spiegdon (10/15/2007)
Thanks very much for the quick response, I am sorry for the long delay in ack.
We ended up going with the cursor approach as all we are iterating through is the keys at that point anyway. I then do some basic joins to bring out some friendly data. Thanks a lot!

Heh... THAT won't fit in a view. 😛

The problem with the cursor method is... it's slow. Oh sure, you're happy now because it runs so much faster than the self join with a triangular join built in, but wait until you run into some real volume... cursor method is going to seem as bad as the first method.

Phil Factor's post above is on the right track... I might be a bit predjudicial, but I like the following article even better... 😉

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

To give you incentive to try it, it'll do a running total on a million row table in about 7 seconds on a slow machine... try the same thing with a cursor. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)