Want aggregate result from a big table
-
Tuesday, January 17, 2012 2:59 AM
Data size 10TB in a table
Want aggregate result from this table into another table.
like: select dim1,dim2,dim3,dim4,sum(v1),sum(v2),avg(v3)
from table group by dim1,dim2,dim3,dim4How to do that?
All Replies
-
Tuesday, January 17, 2012 7:06 AM
Hi,
First of all I think you must use with nolock.
Second run your table and be patient.
SELECT dim1, dim2, dim3, dim4, SUM(v1), SUM(v2), AVG(v3) FROM table WITH (NOLOCK) GROUP BY dim1, dim2, dim3, dim4
As I now if you have a cube for this table you will get quick result as the aggregation are already exists.
I hope this is helpful
Elmozamil Elamir
Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com -
Tuesday, January 17, 2012 9:08 AM
SELECT dim1, dim2, dim3, dim4, SUM(v1), SUM(v2), AVG(v3) FROM table WITH (NOLOCK) WHERE <<condition>> GROUP BY dim1, dim2, dim3, dim4
You would not like to end up reading the entire table.
Please vote as helpful or mark as answer, if it helps -
Tuesday, January 17, 2012 9:19 AM
Hi,
First of all I think you must use with nolock.
Second run your table and be patient.
SELECT dim1, dim2, dim3, dim4, SUM(v1), SUM(v2), AVG(v3) FROM table WITH (NOLOCK) GROUP BY dim1, dim2, dim3, dim4
As I now if you have a cube for this table you will get quick result as the aggregation are already exists.
I hope this is helpful
Elmozamil Elamir
Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com
Very good idear! -
Tuesday, January 17, 2012 9:32 AM
Hi,
if this is answered your question please mark it as answer.
Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com -
Wednesday, January 18, 2012 2:14 AM
Hi,
if this is answered your question please mark it as answer.
Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.comIt is a good idear, but I don't think it is an answer.
You know create a cube need a lot of effort incude ETL and cube.
Maybe ETL for cube cost more effort than table arggregate itself.
-
Wednesday, January 18, 2012 6:06 AM
So what have you decided
- Querying on Relational DBMS
- Querying on Cube
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar -
Thursday, January 19, 2012 4:27 AM
If OLAP is not an option, and you are looking for a solution purely within the relational db, you could use indexed views to pre-aggregate the values. Could also load aggregate tables during the ETL processing.
Either way, at query-time, you would simply be retrieving aggregated data from a separate structure and not scanning the entire 10TB base table.
Hope that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com- Edited by Brent Greenwood Thursday, January 19, 2012 4:28 AM
-
Thursday, February 02, 2012 2:40 AM
Querying on Relational DBMS .So what have you decided
- Querying on Relational DBMS
- Querying on Cube
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar -
Thursday, February 02, 2012 2:43 AM
If OLAP is not an option, and you are looking for a solution purely within the relational db, you could use indexed views to pre-aggregate the values. Could also load aggregate tables during the ETL processing.
Either way, at query-time, you would simply be retrieving aggregated data from a separate structure and not scanning the entire 10TB base table.
Hope that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com
It is very good idear and very helpful.Who can do some further discuss about "separate structure" things, thanks !
.png)
