Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5963

Teiid using all typeInfo takes too long from PG

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 15.0
    • 14.0
    • JDBC Connector
    • None
    • DV Sprint 64, DV Sprint 65
    • 0.5
    • Hide

      We have teiid spring boot app with jdbc connection.

      DDL file

      CREATE DATABASE db;
      USE DATABASE db;
      --use translator
      CREATE FOREIGN DATA WRAPPER postgresql;
      --CREATE FOREIGN DATA WRAPPER mypostgresql type postgresql OPTIONS (supportsOrderBy true);
      --create teiid server
      CREATE SERVER pgserver FOREIGN DATA WRAPPER postgresql OPTIONS (supportsOrderBy true);
      -- create source schema
      CREATE SCHEMA schpostgresql SERVER pgserver;
      -- schema manual_loads
      IMPORT FOREIGN SCHEMA manual_loads FROM SERVER pgserver INTO schpostgresql OPTIONS("importer.useFullSchemaName" 'false', "importer.tableTypes" 'TABLE,VIEW');
      

      Properties

      spring.datasource.platform=pgserver
      spring.datasource.driver-class-name=org.postgresql.Driver
      spring.datasource.jdbcUrl=jdbc:postgresql://test.com:5432/dwh?SearchPath=manual_loads,currentSchema=manual_loads
      spring.datasource.password=
      spring.datasource.username=
      spring.jpa.properties.hibernate.default_schema=manual_loads
      spring.datasource.hikari.schema=manual_loads
      hibernate.temp.use_jdbc_metadata_defaults=false
      spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false
      logging.level.org=TRACE
      
      spring.jpa.hibernate.ddl-auto=none
      spring.main.allow-bean-definition-overriding=true
      spring.main.banner-mode=off
      spring.profiles.active=dev
      teiid.vdb-file=dwh.ddl
      

      In logs we saw that Teeiid load all metadata from DB not only concrete schema

        FE=> Sync
        <=BE BindComplete [unnamed]
        <=BE DataRow(len=2)
        <=BE CommandStatus(SELECT 1)
        <=BE ReadyForQuery(I)
         simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@8301268, maxRows=0, fetchSize=0, flags=16
        FE=> Bind(stmt=S_1,portal=null,$1=<'_xuotedocument'>,type=VARCHAR)
        FE=> Execute(portal=null,limit=0)
        FE=> Sync
        <=BE BindComplete [unnamed]
        <=BE DataRow(len=2)
        <=BE CommandStatus(SELECT 1)
        <=BE ReadyForQuery(I)
         simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@3bbb53f6, maxRows=0, fetchSize=0, flags=16
        FE=> Bind(stmt=S_1,portal=null,$1=<'_xuotelineitem'>,type=VARCHAR)
        FE=> Execute(portal=null,limit=0)
        FE=> Sync
        <=BE BindComplete [unnamed]
        <=BE DataRow(len=2)
        <=BE CommandStatus(SELECT 1)
        <=BE ReadyForQuery(I)
      

      And we used

      		<dependency>
      			<groupId>org.teiid</groupId>
      			<artifactId>teiid-spring-boot-starter</artifactId>
      			<version>1.5.0</version>
      		</dependency>
      		<dependency>
      			<groupId>org.teiid</groupId>
      			<artifactId>spring-odata</artifactId>
      			<version>1.5.0</version>
      		</dependency>		
      

      So, we have loooong start time of app

      Show
      We have teiid spring boot app with jdbc connection. DDL file CREATE DATABASE db; USE DATABASE db; -- use translator CREATE FOREIGN DATA WRAPPER postgresql; -- CREATE FOREIGN DATA WRAPPER mypostgresql type postgresql OPTIONS (supportsOrderBy true ); -- create teiid server CREATE SERVER pgserver FOREIGN DATA WRAPPER postgresql OPTIONS (supportsOrderBy true ); -- create source schema CREATE SCHEMA schpostgresql SERVER pgserver; -- schema manual_loads IMPORT FOREIGN SCHEMA manual_loads FROM SERVER pgserver INTO schpostgresql OPTIONS ( "importer.useFullSchemaName" ' false ' , "importer.tableTypes" ' TABLE , VIEW ' ); Properties spring.datasource.platform=pgserver spring.datasource.driver- class- name=org.postgresql.Driver spring.datasource.jdbcUrl=jdbc:postgresql: //test.com:5432/dwh?SearchPath=manual_loads,currentSchema=manual_loads spring.datasource.password= spring.datasource.username= spring.jpa.properties.hibernate.default_schema=manual_loads spring.datasource.hikari.schema=manual_loads hibernate.temp.use_jdbc_metadata_defaults= false spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults= false logging.level.org=TRACE spring.jpa.hibernate.ddl-auto=none spring.main.allow-bean-definition-overriding= true spring.main.banner-mode=off spring.profiles.active=dev teiid.vdb-file=dwh.ddl In logs we saw that Teeiid load all metadata from DB not only concrete schema FE=> Sync <=BE BindComplete [unnamed] <=BE DataRow(len=2) <=BE CommandStatus(SELECT 1) <=BE ReadyForQuery(I) simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@8301268, maxRows=0, fetchSize=0, flags=16 FE=> Bind(stmt=S_1,portal= null ,$1=< '_xuotedocument' >,type=VARCHAR) FE=> Execute(portal= null ,limit=0) FE=> Sync <=BE BindComplete [unnamed] <=BE DataRow(len=2) <=BE CommandStatus(SELECT 1) <=BE ReadyForQuery(I) simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@3bbb53f6, maxRows=0, fetchSize=0, flags=16 FE=> Bind(stmt=S_1,portal= null ,$1=< '_xuotelineitem' >,type=VARCHAR) FE=> Execute(portal= null ,limit=0) FE=> Sync <=BE BindComplete [unnamed] <=BE DataRow(len=2) <=BE CommandStatus(SELECT 1) <=BE ReadyForQuery(I) And we used <dependency> <groupId> org.teiid </groupId> <artifactId> teiid-spring-boot-starter </artifactId> <version> 1.5.0 </version> </dependency> <dependency> <groupId> org.teiid </groupId> <artifactId> spring-odata </artifactId> <version> 1.5.0 </version> </dependency> So, we have loooong start time of app

      When we started teiid spring boot app, we have long start time.

              rhn-engineering-shawkins Steven Hawkins
              3draven Renat Eskenin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved:

                  Estimated:
                  Original Estimate - 3 hours
                  3h
                  Remaining:
                  Time Spent - 1 hour, 30 minutes Remaining Estimate - 1 hour, 30 minutes
                  1h 30m
                  Logged:
                  Time Spent - 1 hour, 30 minutes Remaining Estimate - 1 hour, 30 minutes
                  1h 30m