-
1. Re: How to improve the performance of the teiid VDB
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
> 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
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
> 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.