SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Speeding up aggregates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Heinduplessis
Starting Member

South Africa
23 Posts

Posted - 09/04/2013 :  13:20:55  Show Profile  Reply with Quote
I have a transaction table containing all the debits and credits for all my customers. Before performing another transaction, I would do a sum(amount) on the tx table to find the balance of a customer, which contains all the debits (sales) and credits (payments).

This works really well to a point but pretty much breaks down when the tx table reach 100k transactions.

I'm using indexes and even tried applying an indexed view (http://www.sqlbadpractices.com/speeding-up-aggregates-with-indexed-views/) however transactions slows down to a crawl because of the sum(amount) effort.

What would be best practice to get around this problem? Would it make sense to have a running balance on the customer field and hope it never goes out of sync for some reason?

Many thanks

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/04/2013 :  13:51:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us the query, the DDL for the table and the indexes.

I have tables with hundreds of millions of rows, some with billions, and do not have this issue with SUM.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Heinduplessis
Starting Member

South Africa
23 Posts

Posted - 09/04/2013 :  15:18:23  Show Profile  Reply with Quote
Thanks for your time, here's the main table:

CREATE TABLE [dbo].[Tx](
	[TxID] [int] IDENTITY(1,1) NOT NULL,
	[TxDateTime] [date] NULL,
	[Amount] [money] NULL,
	[Description] [varchar](100) NULL,
	[CreatedDateTime] [date] NULL,
	[RemoteTxID] [int] NULL,
	[RemoteSequenceNr] [int] NULL,
	[InvoiceNumber] [varchar](50) NULL,
	[AuthID] [int] NULL,
	[TradeSystemID] [int] NULL,
	[TxStatusID] [int] NULL,
	[WarehouseID] [int] NULL,
	[TxTypeID] [int] NULL,
	[AccountNumber] [varchar](50) NULL,
 CONSTRAINT [Pk_Tx] PRIMARY KEY CLUSTERED 
(
	[TxID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


The query:

select sum(Amount) from Tx where AccountNumber='ABC001'


On 3 million records it takes about 600ms (On SSD drive), going up almost exponentially as the number of rows double.

Regards,
Hein

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 09/04/2013 :  16:41:18  Show Profile  Reply with Quote
What are the indexes you have on the table?

If you don't already have it, create a non-clustered non-unique index on AccountNumber column and add amount as an included column. Just be aware that adding an index will take up some disk space, and it will require time to create, and will slow down inserts etc. But all of those should be relatively minor for a varchar(50) column.

And as always, test in a dev environment before you pull the trigger in production.
Go to Top of Page

Heinduplessis
Starting Member

South Africa
23 Posts

Posted - 09/05/2013 :  06:59:12  Show Profile  Reply with Quote
Thanks, yes I've got that exact index. I'm implementing a roll-up balance on the account table, summing only the records inserted after the roll-up date. So every day or week I can just run a quick query to update the roll-up balance and performance will be restored.

In theory..
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 09/05/2013 :  08:45:17  Show Profile  Reply with Quote
If you have not already taken care of it, make sure that the statistics are kept updated and indexes are reorganized/rebuilt if fragmented.

Assuming none of that is the issue, look at your query plan to see if it is making use of the index.

Is the 600 ms that you mentioned time taken for one accountnumber or is it for all the account numbers. With 3 million rows, if it is for all the account numbers, that does not sound unreasonable at all. If it is for one account number, does the time change if you run the query a few times one after the other immediately?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 09/06/2013 :  01:20:22  Show Profile  Reply with Quote
I'd revisit your indexed view and see what's going on there. That should solve most of your problems and if it's causing significant delay you could be missing an exact index or some sublety. Can you post DDL?
Go to Top of Page

Heinduplessis
Starting Member

South Africa
23 Posts

Posted - 09/06/2013 :  06:47:23  Show Profile  Reply with Quote
@James, 600ms for one account number containing 3 million sales rows. If I use the same table, but query a different account number, it drops to 10ms. So the index works.

@Lotz, I'll recheck - the indexed view does help, but it also slows down to an eventual traffic jam. I'll post the scheme here as soon as I can, will probably only be Monday though.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000