Problem with SUM Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with SUM Query

Author

Topic

ashok.arumugam
Starting Member

2 Posts

Posted - 2010-04-07 : 03:09:56

Hi,

I have a table which has 150 columns ahead. In that table there are two columns with name AmountToPay,TotalAmountPaid and there are nearly 2 lakhs of record in my table. So I need to SUM those two columns for all those 2 lakhs record. If i use the query as
SELECT SUM(AmountToPay),SUM(TotalAmountPaid) FROM PayDetail it takes nearly 6-8 secs for execution. Can you please let me know is there any way to optimize and retrieve the SUM quickly within 1 sec.

Please advice.

Thanks In Advance.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-07 : 03:40:14

Try this query and then let me know about the performance becuase i can not test it on huge table because i dont have...


DECLARE @AmountToPay AS Money, @TotalAmountPaid AS Money --whatever the datatype of your columns
SET @AmountToPay = 0
SET @TotalAmountPaid = 0
SELECT @AmountToPay = @AmountToPay + AmountToPay , @TotalAmountPaid = @TotalAmountPaid + TotalAmountPaid FROM PayDetail
SELECT @AmountToPay, @TotalAmountPaid

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-07 : 04:02:40

I would be surprised if that is faster than SUM().
But we will see.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 04:04:43

quote:
Originally posted by ashok.arumugam

Hi,

I have a table which has 150 columns ahead. In that table there are two columns with name AmountToPay,TotalAmountPaid and there are nearly 2 lakhs of record in my table. So I need to SUM those two columns for all those 2 lakhs record. If i use the query as
SELECT SUM(AmountToPay),SUM(TotalAmountPaid) FROM PayDetail it takes nearly 6-8 secs for execution. Can you please let me know is there any way to optimize and retrieve the SUM quickly within 1 sec.

Please advice.

Thanks In Advance.

Are they running value kind of columns. or do you want all of them to contain same sum values?

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

Asken
Starting Member

38 Posts

Posted - 2010-04-07 : 04:25:37

Sounds like a server issue... I'm not to familiar with the Indian numbering system (wiki is you friend) but 2 lakhs would be 200 000 rows and to sum 200k rows up takes 543 ms on a three year old server... indexes won't do you much good as it would still need to scan the whole table.

Is that the full query you're running?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-07 : 04:57:07

quote:
Originally posted by webfred

I would be surprised if that is faster than SUM().
But we will see.

No, you're never too old to Yak'n'Roll if you're too young to die.

In my case means in my database i run this query and i got improved execution plan...
but i dont know with the huge table (more than 2 lacs records)
how it works...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ashok.arumugam
Starting Member

2 Posts

Posted - 2010-04-07 : 06:04:41

Hi Vaibhav Tiwari,

Thanks for your fast reply.

The data type for two columns is Decimal and it also has NULL value present for some record.When I executed the query provided by you it is just returning NULL value as result.

Please advice.

Thank you.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-07 : 06:18:34

Do you allow null values for these columns if yes then use this query


DECLARE @AmountToPay AS Money, @TotalAmountPaid AS Money --whatever the datatype of your columns
SET @AmountToPay = 0
SET @TotalAmountPaid = 0
SELECT @AmountToPay = @AmountToPay + COALESCE(AmountToPay, 0) , 
@TotalAmountPaid = @TotalAmountPaid + COALESCE(TotalAmountPaid, 0) FROM PayDetail
SELECT @AmountToPay, @TotalAmountPaid

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-07 : 07:15:53

This is running faster than sum in my database,
but i think this is not the problem
because for 2 lacs records sum function will also give result within miliseconds...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources