-
1. Re: Slowness while deploying VDB.
rareddy Aug 22, 2016 10:41 AM (in response to kulbhushanc)That may be due the time it takes to import metadata from the source database in each case. You can trying using importer properties on your model to restrict what you are trying to import. Especially try setting the "schemaPattern" in importer properties JDBC Translators · Teiid Documentation
Oracle translator in absence of this property tries to import all the visible schema from the source database. In MySQL and SQL-Server the visibility of other schemas in the database to the importing user is much more limited by default.
Ramesh..
-
2. Re: Slowness while deploying VDB.
ichanjasper Oct 12, 2016 12:35 PM (in response to rareddy)I am facing the similar issue in Oracle. I am joining 2 schema together from same oracle DB using Teiid. And it took me 2 minutes to deploy VDB. I have attached my import properties and DDL. Is there anyway to improve the performance on Oracle?
And I am using the following import properties:
Adding Model - SCHEMA = [SUGARCRM], MODELNAME = [SugarCRMSubDS], CONNECTORNAME = [439793206], CONNECTIONNAME = [439793206]
TEIID IMPORT PROPERTY [import.userFullSchemaName, false]
TEIID IMPORT PROPERTY [import.trimColumnNames, true]
TEIID IMPORT PROPERTY [import.schemaPattern, SUGARCRM]
Adding Model - SCHEMA = [FOODMART], MODELNAME = [FoodmartSubDS], CONNECTORNAME = [-813757334], CONNECTIONNAME = [-813757334]
TEIID IMPORT PROPERTY [import.userFullSchemaName, false]
TEIID IMPORT PROPERTY [import.trimColumnNames, true]
TEIID IMPORT PROPERTY [import.schemaPattern, FOODMART]
Added schema as attachment.
-
tables.txt.zip 9.6 KB
-
-
3. Re: Slowness while deploying VDB.
rareddy Oct 12, 2016 12:37 PM (in response to ichanjasper)Ivan,
You have these tables defined inside your VDB or you are importing these during the deployment from Oracle data source? Read my comments above, try inline DDL to see if that helps.
Ramesh..
-
4. Re: Slowness while deploying VDB.
m.ardito Oct 12, 2016 12:54 PM (in response to kulbhushanc)Hi,
in this other thread (Re: Slowness while restarting VDB. ) I recently updated my post, adding details and timings about loading my VDB (through web console though) to give you some better reference, have you seen them?
have you tried to deploy the same VDB through web interface? I usually deploy by web console or by jboss-cli.sh and timings are similar...
hth.
-
5. Re: Slowness while deploying VDB.
ichanjasper Oct 12, 2016 4:37 PM (in response to rareddy)Hi Ramesh,
We are importing tables during the deployment from oracle DS. It took 2 minutes for oracle, but only 2 second for postgres for identical tables and data. InlineDDL is not an option for us at this moment. What else can we do to improve teiid performance on oracle?
-
6. Re: Slowness while deploying VDB.
rareddy Oct 12, 2016 4:43 PM (in response to ichanjasper)I advise you to set
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaPattern" value="my-schema"/>inside your vdb's oracle model element to make sure you are not retrieving the whole database metadata down.
Ramesh..
-
7. Re: Slowness while deploying VDB.
shawkins Oct 12, 2016 4:47 PM (in response to ichanjasper)Is it possible to use debug logging to see what step of the import is taking so long? Generally if you have set the schema pattern set, that is sufficient. Just to double check, the message above says import.schemaPattern - the actual import property is importer.schemaPattern
-
8. Re: Slowness while deploying VDB.
ichanjasper Oct 12, 2016 5:12 PM (in response to shawkins)It is not due to debug logging and I was using "importer.schemaPattern". Sorry for the confusion.
And I have tried to simplify my vdb by only selecting "table" type and containing only 1 model (schema) in VDB which is pointed to oracle schema "SUGARCRM". It still took 79823ms.
<entry key="importer.tableTypes" value="TABLE"/>
<entry key="importer.userFullSchemaName" value="false"/>
<entry key="importer.trimColumnNames" value="true"/>
<entry key="importer.schemaPattern" value="SUGARCRM"/>
If I put 2 models in VDB which is pointed oracle schema "SUGARCRM" and oracle schema "FOODMART". It took total 112705ms to deploy VDB. Same code and same db schemas, it only took 2000ms on postgres.
Model 1:
<entry key="importer.tableTypes" value="TABLE"/>
<entry key="importer.userFullSchemaName" value="false"/>
<entry key="importer.trimColumnNames" value="true"/>
<entry key="importer.schemaPattern" value="SUGARCRM"/>
Model 2:
<entry key="importer.tableTypes" value="TABLE"/>
<entry key="importer.userFullSchemaName" value="false"/>
<entry key="importer.trimColumnNames" value="true"/>
<entry key="importer.schemaPattern" value="FOODMART"/>
-
9. Re: Slowness while deploying VDB.
shawkins Oct 13, 2016 11:44 AM (in response to ichanjasper)Is it possible to turn on debug logging at least for org.teiid.CONNECTOR to see what import step is taking the longest?
-
10. Re: Slowness while deploying VDB.
ichanjasper Oct 13, 2016 5:15 PM (in response to shawkins)Hi Steven,
Thank you for looking. Here is the debug log from org.teiid.CONNECTOR. It took total 90 seconds for importing index info for 2 schemas and 25 seconds for importing foreign keys.
2016-10-13 14:09:44,416 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing tables
2016-10-13 14:09:44,508 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing columns
2016-10-13 14:09:46,011 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing primary keys
2016-10-13 14:09:52,421 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing index info
2016-10-13 14:10:49,110 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing foreign keys
2016-10-13 14:11:09,816 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing tables
2016-10-13 14:11:09,881 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing columns
2016-10-13 14:11:10,795 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing primary keys
2016-10-13 14:11:12,673 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing index info
2016-10-13 14:11:41,599 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing foreign keys
2016-10-13 14:11:45,566 DEBUG VirtualDataSourceQueryService,http-apr-8080-exec-5:247 - Deployment time for virtual data source, -2146727971: 121460ms
-
11. Re: Slowness while deploying VDB.
shawkins Oct 14, 2016 9:08 AM (in response to ichanjasper)What oracle driver/server version are you using? It seems like there may be some issues with Oracle's implementation of those DatabaseMetaData.getImportedKeys and DatabaseMetadata.getIndexInfo that is causing the slow down.
-
12. Re: Slowness while deploying VDB.
ichanjasper Oct 14, 2016 1:25 PM (in response to shawkins)I am using progress oracle driver 5.13 to connect to oracle 11g.
-
13. Re: Slowness while deploying VDB.
rareddy Oct 14, 2016 1:27 PM (in response to ichanjasper)Try Oracle native drive to see if there is any difference?
-
14. Re: Slowness while deploying VDB.
ichanjasper Oct 14, 2016 1:29 PM (in response to rareddy)Will do. I will let you guys know the result soon. Thanks for your suggestion.