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

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

    adi22
    adi22 Oct 11, 2017 10:38 AM

      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(*)
          shawkins
          shawkins Oct 11, 2017 10:42 AM (in response to adi22)

          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
            • Actions
          • 2. Re: Teiid Dynamic VDB taking more time to execute count(*)
            adi22
            adi22 Oct 12, 2017 5:15 AM (in response to shawkins)

            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>

              • Actions
            • 3. Re: Teiid Dynamic VDB taking more time to execute count(*)
              shawkins
              shawkins Oct 12, 2017 7:49 AM (in response to adi22)

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

                • Actions
              • 4. Re: Teiid Dynamic VDB taking more time to execute count(*)
                adi22
                adi22 Oct 23, 2017 9:47 AM (in response to shawkins)

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

                  • Actions
                • 5. Re: Teiid Dynamic VDB taking more time to execute count(*)
                  shawkins
                  shawkins Oct 23, 2017 9:55 AM (in response to adi22)

                  > 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.

                    • Actions