calculation in big data - 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)

calculation in big data

Author

Topic

nobelins
Starting Member

8 Posts

Posted - 2010-01-31 : 10:34:30

Hi I'm developing simple application. I have two table. In the first table i keep product names. In the second there is some log data about each product and this table is growing consistently. My application list the names of the products using the first table. The problem begins here. I have to make some calculations for each product using the second table such as how many product sold in total etc... As there is more than 100.000 rows in the second table my application makes these calculations for each product and it makes my application very slow.I'm using simple SUM operations to make the calculations. Is there any way to calculate a value among big data.a much faster way i mean.

I'm using a simple calculation method like this;

SELECT PRODUCT_NAME,DATE_ADD,(SELECT SUM(ENTRY) FROM PRODUCT_LOG WHERE PRODUCT_ID = PRODUCTS.ID) AS TOTAL_ENTRY,
,(SELECT SUM(SOLD) FROM PRODUCT_LOG WHERE PRODUCT_ID = PRODUCTS.ID) AS TOTAL_SOLD
FROM PRODUCTS

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-31 : 10:42:47

[code]SELECT PRODUCT_NAME,DATE_ADD
,SUM(ENTRY)AS TOTAL_ENTRY
,SUM(SOLD)AS TOTAL_SOLD
FROM PRODUCTS P
INNER JOIN PRODUCT_LOGS PG ON P.PRODUCTID = PG.PRODUCTID
GROUP BY PRODUCT_NAME,DATE_ADD
ORDER BY PRODUCT_NAME,DATE_ADD[/code]

YOU SHOULD HAVE INDEX ON PRODUCTID(CLUSTERED) TO MAKE IT FASTER.

OTHER WAY:

[code]SELECT DISTINCT PRODUCT_NAME,DATE_ADD
,SUM(ENTRY)OVER (PARTITION BY PRODUCT_NAME,DATE_ADD) AS TOTAL_ENTRY
,SUM(SOLD)OVER (PARTITION BY PRODUCT_NAME,DATE_ADD) AS TOTAL_SOLD
FROM PRODUCTS P
INNER JOIN PRODUCT_LOGS PG ON P.PRODUCTID = PG.PRODUCTID
ORDER BY PRODUCT_NAME,DATE_ADD[/code]

nobelins
Starting Member

8 Posts

Posted - 2010-01-31 : 11:05:01

thank you sodeep

this is really very helpful.
i'll work on it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources