2 Replies Latest reply: Sep 19, 2013 2:20 PM by JustinCave RSS

    Performance improvement tips for big table

    user10978624

      We have one big table which has around 69 Millions records.And its very slow performance wise.We have appropriate indexes on this table but fetching data from this table is quite slow.We don't have any partition currently on this table.And there is another main dimension table which has 6 million record which is joined with the above fact table.When we are fetching record from dimension table it is fast but whenever we use the fact table objects for reporting it takes lot of time to give result.

      Our reporting tool is BO.So my question is how to improve the performance of this big fact table?

        • 1. Re: Performance improvement tips for big table
          JustinCave

          You don't, in general, improve the performance of a table-- a table does not have performance characteristics.  You improve the performance of SQL statements.

           

          Pick the most important (or a few of the most important) queries against this table.  Why are those queries slow?  Are they using indexes when they should be scanning the whole table?  Are they failing to use indexes that they could be using?  Are they aggregating data?  If so, could you pre-aggregate the data in materialized views?  Do the queries scan a large number of rows in a predictable pattern that would be amenable to partitioning (and do you have the partitioning option available?)?

           

          Justin    

          • 2. Re: Performance improvement tips for big table
            Martin Preiss

            are these indexes used? What kind of queries are you executing? Can you provide us the execution plans? Partitioning is an answer to some common problems in DWH/DSS - but without more information it is almost impossible to say something relevant.