Speeding up aggregates - SQL Server Forums

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

General SQL Server Forums

Database Design and Application Architecture

Speeding up aggregates

Author

Topic

Heinduplessis
Starting Member

23 Posts

Posted - 2013-09-04 : 13:20:55

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

38200 Posts

Posted - 2013-09-04 : 13:51:10

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

Heinduplessis
Starting Member

23 Posts

Posted - 2013-09-04 : 15:18:23

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-04 : 16:41:18

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.

Heinduplessis
Starting Member

23 Posts

Posted - 2013-09-05 : 06:59:12

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..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-05 : 08:45:17

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?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-06 : 01:20:22

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?

Heinduplessis
Starting Member

23 Posts

Posted - 2013-09-06 : 06:47:23

@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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources