none
Want aggregate result from a big table

    Question

  • 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,dim4

    How to do that?

    Tuesday, January 17, 2012 2:59 AM

All replies

  • 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

    MyBlog


    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 7:06 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:08 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

    MyBlog


    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:19 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
    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

    It 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 2:14 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
    Wednesday, January 18, 2012 6:06 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

    Thursday, January 19, 2012 4:27 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
    Querying on Relational DBMS .
    Thursday, February 02, 2012 2:40 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 !

     

    Thursday, February 02, 2012 2:43 AM