All Forums Database
awalanchal 12 posts Joined 04/09
04 May 2011
Database metadata getColumns() call behaviour

hi

We are using Teradata JDBC 13.0.0.6 to connect to TD DBMS 13.0. We query database for getColumns(), and try it on two different Databases.

for database 1: (JDBC TRACE)

2011-04-08.14:17:25.165 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdbc_4.TDSession@3a763a76 Read message 1, 2392 bytes, time: 1859 ms
2:17:25 PM getColumns EXIT

for database 2: (JDBC TRACE)

2011-04-08.16:15:17.349 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdbc_4.TDSession@28172817 Read message 1, 2392 bytes, time: 289807 ms
4:15:17 PM getColumns EXIT

In databse 1 getColumns call is taking 1.859 seconds and for database 2 its taking 289.807 seconds, that is way to large to be different. Database 1 is a test database and is small, whereas database 2 is a production database and is huge.

Can someone please explain why this behavior, or is there a way to tweak getcolumns call.

Thanks

tomnolan 594 posts Joined 01/08
05 Jul 2012

Teradata JDBC Driver version 13.0.0.6 is rather old. You should upgrade to the most recent version available at http://downloads.teradata.com/download/connectivity/jdbc-driver

We have made some improvements recently to the DatabaseMetaData getColumns method.

Connection parameter USEXVIEWS=ON will slow down your DatabaseMetaData methods. If speed is important to you, then omit connection parameter USEXVIEWS. When you omit USEXVIEWS, then the non-X views are queried, which means that your Teradata Database userid must have select access to the non-X views.

Collecting statistics on Data Dictionary columns and indexes is important for performance. It is recommended that the Teradata Database administrator execute the following SQL commands on a regular basis.

Please refer to the Teradata JDBC Driver Reference for the recommended list of commands.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_5.html#CHDGGHEA

 

awalanchal 12 posts Joined 04/09
11 Jul 2013

hi
We have seen this in 13.0 earlier on. But it still exist in 13.1 and 14.0. Mentioned below is time taken by getCol in two different setups.
Development setup - very small database with just a few tables.
Time taken = 2890 msecs
Customer setup - large database with many tables.
Time taken = 26797 msecs
Sometimes this time taken is even more than 2 minutes. We have tried USEXVIEWS=ON, but no luck. Timing is the same.
 
Is there any other solution to workaround this problem ?
 
Thanks

You must sign in to leave a comment.