3 Replies Latest reply on Oct 9, 2013 7:20 AM by shawkins

    Teiid sql generation issue

    ashurov_mf
    ashurov_mf Oct 8, 2013 12:14 AM

      We use vdb.xml to specify schema. There are 3 models:

       
      



      <model name="Stat">
        <property name="importer.schemaPattern" value="public"/>
        <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.importKeys" value="true"/>
        <property name="importer.importIndexes" value="true"/>
        <property name="importer.importApproximateIndexes" value="true"/>
        <source name="Stat" translator-name="postgresql-conf" connection-jndi-name="java:/main-ds"/>
    </model>
<model name="Dims" visible="true" type="PHYSICAL">
        <source name="Dims" translator-name="postgresql-conf" connection-jndi-name="java:/dims-ds"/>
        <metadata type="DDL"><![CDATA[
            CREATE FOREIGN TABLE Creative (creative_id integer PRIMARY KEY, order_id integer not null, user_id integer not null) OPTIONS (CARDINALITY 200000, nameinsource 'statistics.creative_m');
            CREATE FOREIGN TABLE Zone (zone_id integer PRIMARY KEY, user_id integer not null) OPTIONS (CARDINALITY 100000, nameinsource 'statistics.zone_m');
]]>
        </metadata>
    </model>
<model name="Custom" visible="true" type="VIRTUAL">
        <metadata type = "DDL"><![CDATA[
CREATE VIEW Billing AS
                SELECT b.zone_id, b.creative_id, b.requests, b.actions,b."date", c.user_id AS creative_user_id, c.order_id,
                z.user_id AS zone_user_id
                FROM
                    ((Stat.billing_by_day AS b
                    INNER JOIN /*+ optional */ Dims.creative AS c ON b.creative_id = c.creative_id)
                    INNER JOIN /*+ optional */ Dims.zone AS z ON b.zone_id = z.zone_id);
</metadata>
    </model>

      So java:/main-ds and java:/dims-ds connectors to diffrent postgres databases.We prepare sql like:

      SELECT "date", SUM("requests") as "requests", SUM("actions") as "actions" FROM Custom."Billing" WHERE "date" >= '2013-08-01' AND "date" < '2013-09-01' AND ("zone_user_id" IN (9781) OR "creative_user_id" IN (9781)) GROUP BY "date"

       

      Teiid generates two sql: one to main db to billing_by_day table, and one:

      SELECT g_1.zone_id AS c_0, g_0.creative_id AS c_1 FROM Dims.Creative AS g_0, Dims.Zone AS g_1 WHERE (g_1. user_id = 9781) OR (g_0.user_id = 9781) ORDER BY c_0, c_1

      That cross-join select executing on two big tables with many matching by the user, so it can't be done quickly on db (over 5 mins).

       

      Issue: why teiid generate cross-join select instead of two independent selects? Are there ways to configured teiid to split that sql on two?

       

      Thanks