locked
Group by query on a huge table RRS feed

  • Question

  • Hi everybody,

    I want to aggregate the data of one of my tables (5 billions of records) and put it in a target table.
    I tried to do it within a SQL query but I reach my tempdb limit such as:

    insert into target_table
    select log_date, site_id,server_id,sum(queries),sum(volumes)
    from source_table
    group by log_date, site_id,server_id

    How could I avoid this tempdb problem.

    Thanks Pete
    • Edited by ms_bi_pete Wednesday, May 30, 2012 7:56 AM
    Wednesday, May 30, 2012 7:52 AM

Answers

  • What indexes does the source table have?

    If you using SQL Server 2008 and onwards please look at this site to take advantage of using minimally logging feature

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-1.aspx   ---Minimal Logging changes in SQL Server 2008

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   ---Minimal Logging changes in SQL Server 2008


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by ms_bi_pete Thursday, June 7, 2012 9:25 AM
    Wednesday, May 30, 2012 7:55 AM
    Answerer

All replies

  • What indexes does the source table have?

    If you using SQL Server 2008 and onwards please look at this site to take advantage of using minimally logging feature

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-1.aspx   ---Minimal Logging changes in SQL Server 2008

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   ---Minimal Logging changes in SQL Server 2008


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by ms_bi_pete Thursday, June 7, 2012 9:25 AM
    Wednesday, May 30, 2012 7:55 AM
    Answerer
  • ALTER DATABASE tempdb ON tempdev = 25
    The above will increase it by 25 MB

    Many Thanks & Best Regards, Hua Min


    Wednesday, May 30, 2012 8:11 AM
  • Hua

    Are you sure? I am getting 

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'ON'.

    I think proper syntax is

    ALTER DATABASE tempdb
    MODIFY FILE 

          NAME = temp_data
        , SIZE = 2000


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 30, 2012 8:17 AM
    Answerer
  • Sorry my mistake above. It is

    USE master
    GO
    ALTER DATABASE tempdb
    MODIFY FILE
    (NAME = tempdev,
    SIZE = 2048MB)
    GO


    Many Thanks & Best Regards, Hua Min

    Wednesday, May 30, 2012 8:27 AM
  • try inserting the data in batches.

    Thanks and regards, Rishabh K

    Wednesday, May 30, 2012 8:56 AM