How teiid executes sql queries internally?

[kulbhushanc]

Hi,

I want to know how teiid executes sql queries internally i.e.
1.     How joining is performed across the database?

2.     If its insert query will it be directory gets executed on database as a native query(translated by teiid)?

3.     If joining operation takes place on teiid (in memory of teiid server itself) then in that case is all the data from the respective tables fetched into the memory of teiid?

4.     Query like insert into some_conn.some_db.some_table (select * from another_conn.another_db.another_table), how it executes? i.e. teiid server fetch all data from some_table in memory and executes insert statements/batch insert to another_table.

Thanks,

Kulbhushan Chaskar.

[shawkins]

> How joining is performed across the database?

If the join can be pushed to the source, it will be.  Otherwise if it for example crosses databases Teiid will plan to execute the join in a number of ways:

- traditional, for example sort merge or nested loop

- dependent, using values from one side of the join to filter the other.  And depending on the source/settings the entire value set or one whole side of the join could be pushed to the source.

> If its insert query will it be directory gets executed on database as a native query(translated by teiid)?

It will depend upon the insert and the source.  If possible it will be pushed to the source.  However if you for example have an insert with a query expression from another source, then Teiid will create a processing plan to accommodate that - which could including executing batched inserts or single inserts against the target source depending upon its capabilities

> If joining operation takes place on teiid (in memory of teiid server itself) then in that case is all the data from the respective tables fetched into the memory of teiid?

While all of the data may be fetched it will not necessarily be in memory at the same time.  All processing uses a batch model that allows for subsets of the data to be processed based upon the buffer manager configuration.

>   Query like insert into some_conn.some_db.some_table (select * from another_conn.another_db.another_table), how it executes? i.e. teiid server fetch all data from some_table in memory and executes insert statements/batch insert to another_table.

Yes, batches will be pulled incrementally to perform inserts on the target.  As mentioned above it will be up source capabilities as to whether batching can be performed.