4 Replies Latest reply on May 22, 2015 8:10 AM by Steven Hawkins

    How to improve the performance of the teiid VDB

    govindaraj s Novice

      Hi,

       

           I have more than 500 tables with millions of records in the teiid VDB,

       

           I am facing the performance issue while application connecting to the teiid VDB, So I have materialized 300 tables which is frequently used

       

           I am using ttl snapshot and updatable command to refresh the internal materialized view, Materialized views occupy more than 10 GB in hard disk when it is in cache

       

           Still I am facing the performance issue

       

           Details:

           1. Teiid 8.7

           2. Internal materialization (ttl snapshots and updatable)

           3. transaction view contains more join (joining tables are materialized)

       

       

           Is there any way to improve the performance of the teiid VDB?

       

       

      Thanks & Regards

      Govindaraj s.

        • 1. Re: How to improve the performance of the teiid VDB
          Van Halbert Master

          Hi Govindaraj s

           

          Let's start with before you began materializing.    What are the issues you were seeing with slow performance?  Can you explain the steps that were were doing to see the slow performance.

           

          Also, can you provide the the following information for running the server on:

          -  type of machine, with amount of memory

          -  VM size and any changes you have made


          Thanks.


          Van

          • 2. Re: How to improve the performance of the teiid VDB
            Steven Hawkins Master

            > I am facing the performance issue while application connecting to the teiid VDB, So I have materialized 300 tables which is frequently used

             

            It matters a great deal what is being materialized.  It's not just that the tables are frequently used, but how they are used.  You should materialize as high up in the logical model as possible.  Materializing a view that is further reused in joins, etc. may lead to other problems as the access to the materialized table won't get pushed to the source.

             

            Beyond the machine metrics that Van is asking for, you'll need to assess if you are experiencing a general issue or something that is specific to certain queries.  If it's the latter, then you need to approach this from starting with the query plans.

            • 3. Re: How to improve the performance of the teiid VDB
              govindaraj s Novice

              Thanks Van and Steven,

               

              The server information:

               

              OS: Window Server 2008 R2 Enterprise

              Processor: Inter(R) Xeon(R) CPU X5650 @ 2.67GHz 2.66GHz (2 processors)

              Installed memory (RAM): 16.0 GB (15.7 GB usable)

              System type: 64-bit Operating System

              Hard Disk: 100GB (14GB free of 100GB)

               

               

              And also i wrote one Stored Procedure which is used to get cache when it deployed initially, so the idea is the application to be fast at the first time also

               

              BEGIN

                LOOP ON (SELECT * FROM SYSADMIN.MatViews WHERE (SYSADMIN.MatViews.VDBName = 'SourceModel') AND (SYSADMIN.MatViews.SchemaName = 'ViewModel') AND (SYSADMIN.MatViews.LoadState = 'NEEDS_LOADING')) AS curMatView

                BEGIN

                EXEC SYSADMIN.refreshMatView(('ViewModel.' || curMatView.Name), FALSE);

                END

                SELECT 'SUCCESS' AS OP;

              END

               

               

              Van,

              When i try to select materialized view with 100 records it takes more than 30 seconds to load and sometimes it shows some error like "the buffer size exceed" in the log

               

               

              Steven,

              I have created runtime primary key [UUID()] for Composite views, so I cannot refresh it by using refreshMatViewRow(), is there any option to refresh the Composite view with runtime Primary Key (which is not available in the source db)

               

              I materialized lower level tables of the logical model, over it I built composite view (joins)

               

               

              is Internal Materialization enough to increase the performance or need to do anything?

               

               

              Thanks & Regards

              Govindaraj s.

              • 4. Re: How to improve the performance of the teiid VDB
                Steven Hawkins Master

                > When i try to select materialized view with 100 records it takes more than 30 seconds to load and sometimes it shows some error like "the buffer size exceed" in the log

                 

                We need the exact error.

                 

                > I have created runtime primary key [UUID()] for Composite views, so I cannot refresh it by using refreshMatViewRow(), is there any option to refresh the Composite view with runtime Primary Key (which is not available in the source db)

                 

                For a row based refresh to be effective it's imperative that specifying the primary key against the view will result in a performant source queries.  Are you using internal or external materialization?  You will have more control over refresh with external. 

                 

                > I materialized lower level tables of the logical model, over it I built composite view (joins)

                 

                So you'll have to check you query plans to see if this is appropriate.  More than like it is not.  Materialization is best suited for after you are performing some federation, denormalization, or aggregation.

                 

                > is Internal Materialization enough to increase the performance or need to do anything?

                 

                As I don't have a sense yet about whether you are experiencing a general performance issue, you likely need to go at analyzing performance by starting with individual problem queries.