Optimizations / Performance on federated scenarios

[artenisdavid]

Hi Guys, I was testing this amazing platform federating various datasources and I want to know if there is any standard steps to improve the performance on a scenario with multiple datasources. My execution times are really bad and I want to perform this steps (if they exist) for improving the times as much as I'm able to. When I'm refering to this steps I'm talking about configurations, pk-fk declarations, properties for fields...

Your help is much appreciated

[rareddy]

David,

Basically depends on what type of sources you are working with and type of aggregations you are doing. Ideally, you would want to push most of the query processing to the data sources where the actual data is but sometimes this is not possible with sources like file and web services. Keeping that in mind

1) First thing is to look at your query plan and see where it is taking more time. Hopefully, if you can re-write query to make it faster that is better. You can post your query plan here, maybe we can give some hints.

2) You can define costing information on your source tables in your models that will help develop optimized queries by Teiid engine. Look for "cardinality" field on the table properties.

3) You can then look into any hints you can provide to help the optimizer to plan in a certain way which you know as a developer, like one side of join has way more rows vs other. Look at available hints in the documentation.

4) You can consider materialization of the view.

I listed in the order of preference but stackable on one after other. One last thing I want to mention is if your source execution time (time to fetch results from underlying sources) itself is taking time then look into indexing of the table if they are relational sources otherwise, you are bound by that as minimum time.

HTH.

Ramesh..