calculation in vast amour of data

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)

calculation in vast amour of data

Author

Topic

nobelins
Starting Member

8 Posts

Posted - 2010-02-11 : 18:15:10

Hi,

I have two table.In the first table we keep product information and in the second there is a vast amout of data that holds products movements. This table is very large.there is more than 200.000 rows.in my application i have to list product names,their avg prices and the total about of the sold and received count. The problem is when i try to calculate total sold and received fields using the second table it takes too much time.i'm using simple sum operations to calculate these variables. there is nearly 4000 products and this performance issue makes my application useless.

here is the first table (PRODUCTS):

Product Id Product Name
-----------------------------
001 Apple IPhone
002 Sony PlayStation3
003 Apple IMAC
etc..
-----------------------------

The second table (PR_LOG):

Product_Id Sold Received Date
----------------------------------------------
001 3 0 2007-04-05
001 1 0 2007-04-05
003 0 4 2007-04-05
001 2 0 2007-04-05
002 0 21 2007-04-05
...
(more than 200.000 records)
----------------------------------------------

The result table i wanted to have:

Product_Id Product Name Sold Received
--------------------------------------------------------
001 Apple IPhone 8 2
002 Sony PlayStation3 2 12
003 Apple IMAC 5 8
etc..
--------------------------------------------------------

My current query;

SELECT Product_Id,Product,
(SELECT SUM(Sold) FROM PR_LOG WHERE Product_Id = PRODUCTS.Product_Id) AS TOTAL_SOLD,
(SELECT SUM(Received) FROM PR_LOG WHERE Product_Id = PRODUCTS.Product_Id) AS TOTAL_RECEIVED
FROM PRODUCTS

The application should count the sold and received columns using the Product Id index as fast as possible.

Maybe this is a simple issue but for a starter for me this is a nightmare.

thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-11 : 19:52:47

USE THIS QUERY. ALSO HAVE CLUSTERED INDEX ON PRODUCTID,YOUR QUERY WILL RUN FASTER

SELECT P.Product_Id,P.Product,SUM(Sold) TOTAL_SOLD,SUM(Received)TOTAL_RECEIVED
FROM PRODUCTS P 
INNER JOIN PR_LOG PR ON P.PRODUCTID = PR.PRODUCTID
GROUP BY P.Product_Id,P.Product
ORDER BY P.Product_Id,P.Product

nobelins
Starting Member

8 Posts

Posted - 2010-02-13 : 14:14:35

thanks sodeep
it worked.but i have a question. what's the difference between clustured and non-clustured indexses?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-14 : 00:46:08

Please Check Books Online.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources