Database metadata getColumns() call behaviour

Database
Enthusiast

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
2 REPLIES
Teradata Employee

Re: Database metadata getColumns() call behaviour

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

Enthusiast

Re: Database metadata getColumns() call behaviour

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