locked
selecting data from one table RRS feed

  • Question

  • Hi,

    am storing logs in one table named msg_log. every month we are storing more than 2000000 rows in this table. how i improve the performance of the table. when am generating reports it taking more than 30 seconds for generating the record. 

    Table partitioned table  and creating new table for each month which one is better for the problem please advice me.

    Monday, September 24, 2012 11:46 AM

Answers

  • Hi Sudheesh Bangalore, 

    I suggest you are using Partitioned Tables and Indexes to improve the performance. Partitioning a table or index may improve query performance, based on the types of queries you frequently run and on your hardware configuration.

    For more information about it, please see:
    Designing Partitions to Improve Query Performance: http://msdn.microsoft.com/en-us/library/ms177411(v=sql.105).aspx 


    Thanks,
    Eileen


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Eileen Zhao Tuesday, October 2, 2012 7:16 AM
    Tuesday, September 25, 2012 8:41 AM

All replies

  • Are you familiar with Analysis Services? Have you created cube in Analysis Services that you use for the reports?

    Tatyana Yakushev [PredixionSoftware.com]

    Monday, September 24, 2012 4:21 PM
    Answerer
  • Hi Sudheesh Bangalore, 

    I suggest you are using Partitioned Tables and Indexes to improve the performance. Partitioning a table or index may improve query performance, based on the types of queries you frequently run and on your hardware configuration.

    For more information about it, please see:
    Designing Partitions to Improve Query Performance: http://msdn.microsoft.com/en-us/library/ms177411(v=sql.105).aspx 


    Thanks,
    Eileen


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Eileen Zhao Tuesday, October 2, 2012 7:16 AM
    Tuesday, September 25, 2012 8:41 AM