5 Replies Latest reply on Oct 23, 2017 9:55 AM by Steven Hawkins

    Teiid Dynamic VDB taking more time to execute count(*)

    Aditi Patel Newbie

      I have create a dynamic VDB with two source models using jdbc-simple translator and one view model.

      Using DBvisualiser to query the VDB but it is even taking more time to execute simple count(*) query.

      Locally when same query is run on source server, it is taking only 0.02 sec while teiid is taking 0.11 secs.

       

       

      Can you please help me out on how to reduce the query time and improve performance?

        • 1. Re: Teiid Dynamic VDB taking more time to execute count(*)
          Steven Hawkins Master

          jdbc-simple does not push the count operations to the source.  You could try jdbc-ansi instead.  Is there not a built-in translator for your source?

          1 of 1 people found this helpful
          • 2. Re: Teiid Dynamic VDB taking more time to execute count(*)
            Aditi Patel Newbie

            Hi,

             

            I am very beginner in this area. Below is the data source info:

             

                          <datasource jta="true" jndi-name="java:/dynamicST-DS-1" pool-name="dynamicST-DS-1" enabled="true">

                                <connection-url>jdbc:sybase:Tds:ieatclvm1002:2640</connection-url>

                                <driver>sybase</driver>

                                <security>

                                    <user-name>dc</user-name>

                                    <password>dc</password>

                                </security>

                            </datasource>

             

            Here Sybase driver is added as below:

                                <driver name="sybase" module="com.sybase">

                                    <driver-class>com.sybase.jdbc4.jdbc.SybDataSource</driver-class>

                                    <xa-datasource-class>com.sybase.jdbc4.jdbc.SybXADataSource</xa-datasource-class>

                                </driver>

             

            I have defined VDB.xml as below:

             

            <model name="SrcModel_1" type="PHYSICAL" visible="true">

              <property name="importer.useFullSchemaName" value="false"/>

            <property name="importer.TableTypes" value="VIEW" />

            <property name="importer.importKeys" value="false"/>

            <property name="importer.schemapattern" value="dc"/>

            <property name="importer.schemapattern" value="dcpublic"/>

            <property name="importer.UseQualifiedName" value="true" />

            <source name="SrcModel_1" translator-name="jdbc-simple" connection-jndi-name="java:/dynamicST-DS-1" />

            </model>

            • 3. Re: Teiid Dynamic VDB taking more time to execute count(*)
              Steven Hawkins Master

              Replace translator-name="jdbc-simple" with translator-name="sybase"

              • 4. Re: Teiid Dynamic VDB taking more time to execute count(*)
                Aditi Patel Newbie

                Using translator-name="sybase" it is taking more time. I have tried using jdbc-ansi and it worked pretty well.

                • 5. Re: Teiid Dynamic VDB taking more time to execute count(*)
                  Steven Hawkins Master

                  > Using translator-name="sybase" it is taking more time.

                   

                  I could only guess that you may not have successfully updated the translator property.

                   

                  jdbc-ansi supports count pushdown as well.  In most cases you should use the most specific translator possible.