How to do sum of column when there is 1million records

Question

How to get sum of column soon when there is 1million records or than that in the table?

Andrew Fenster · Answer

Yes, doing a sum when there are a million records in the table takes the database longer than doing a sum with a few thousand records. If you have performance problems, you might have to add an index. For example, suppose you had this SQL: select SUM(sale_price) from sales where store_id = 57 If you have millions of records, you might need to put an index on the store_id column to improve performance.

Mahesh Chand · Answer

As Andrew mentioned, you don't need to fetch any records. Just use SUM function.

Vijay Yadav · Answer

Hi, I mean to ask it will take more time to fetch the record from millions of record?

Andrew Fenster · Answer

If you just want the sum, nothing else, you should use a simple SQL query to let the database do it. You should NOT try fetching 1 million records to the web server and then try to sum them there. That would be slow. select SUM(sale_price) from sales The database can do this kind of thing very efficiently.

John Penn · Answer

That data should be in a partitioned table but that option is available only in the Enterprise, Development or Eval version of SQL Server. Otherwise you can use a partitioned view which logically splits a large table into smaller tables.

Suthish Nair · Answer

So you saying there will be no filters applied for fetching data. Check my reply on your other thread, use Partitioned Table best practices. 1 million records is nothing now days :) ..

How to do sum of column when there is 1million records

Answers (6)