Is there a way to push down a query to a particular data source?

[bbarani]

Hi,

We have a very simple common model (virtual model) which integrates the data from various sources.

We assign a repository id and object type for each source system to make each source a unique entity (in MMX / Teiid Designer).. We also form UID by concatenating the source system id + repository id + object type to make the data coming from each reposiroty as unique ones.

We integrate millions of data virtually...When we query the data out of MMX / Teiid VDB based on particular object type / repository combination, we see lots of degradation in performance.

Also whenever we use an aggregate function in the query, push down doesnt seem to happen and this causes lots of our of space / perofrmance issues.

We wont be having update rights to add  a field in all our source so as to push down the query to source, instead is there a way to make the query to push down to a particular source connecting binding? This would help us in resolving the memory / performance issues which we have right now.

In most of the cases MMX brings in the entire data corresponding to all data source and process it in memory based on filter criteria.

If there are 5 sources integrated in to a model and if we want to query data corresponding to just 2 sources of those 5 I should be able to specify it as a kind of fully qualified name along with tablename so that the query gets pushed to that particular source.

May be I am not aware of any other ways to implement this in Teiid, can someone let me know if there is a way to do this in Teiid?

Thanks,

Barani

[rareddy]

Barani,

In Teiid, we improved the push down functionality over the MMX capabilities. we also improved buffering capabilities. However, it all depends upon how you are federating, how you wrote the transformations and the user queries. If you are having issue with specific case please provide us a example, along with the generated query plan, then we can analyze and tell you if we support the feature or any improvements you can make to the query.

When use aggregate functions, note that the source systems must also support similar functions to pushdown. Is schema in all your sources sam? so that you can use multi-source vdb? with multi-source vdb Teiid supports pesudo source identifier column.

Ramesh..

[shawkins]

Barani,

In the scenario you're describing, do you have a base view layer with views that have a union all of the 5 sources?  If that is the case, then putting criteria on the repository id should get pushed through and cause non-matching union branches to be removed even if you are using aggregation.  If criteria is not provided, then aggregation operations may need to pull back all of the results.

Can you provide an example of a user query that is not behaving as expected?

Also, compared to MMMX, Teiid has support in general for pushing aggregate opertions through unions.  Teiid 7.3 is also adding support for the notion of partitioned unions, so that the optimizer will be able to perform partition wise joins and aggregation.

Steve