JDBC DatabaseMetadata.getColumns Seems Slow

Connectivity
Teradata Employee

JDBC DatabaseMetadata.getColumns Seems Slow

When I call

ResultSet rs = dbmd.getColumns(null, "CUSTDATA", "TRANS", null);

it takes 16 seconds to get an answer back on a fairly unloaded system.  This table has 27 columns.

This seems to be excesvily slow.  Is there anything that can be done to speed this up?

10 REPLIES
Teradata Employee

Re: JDBC DatabaseMetadata.getColumns Seems Slow

Step 1. Are you using X-views? Some organizations require the use of X-views; however, they are slower than non-X-views. We mention this issue in the Teradata JDBC Driver Reference.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_USEX...

Step 2. You (or your DBA) must collect statistics on the Data Dictionary tables, columns, and indexes that are referenced by the Teradata JDBC Driver's DatabaseMetaData queries.

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

Also, Carrie wrote a blog entry about this topic.

http://developer.teradata.com/blog/carrie/2010/05/if-you-re-not-collecting-statistics-on-your-dictio...

Step 3. You can use the Teradata JDBC Driver's DDSTATS=ON connection parameter to help identify what statistics should be collected on Data Dictionary tables, columns, and indexes.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_DDST...

Teradata Employee

Re: JDBC DatabaseMetadata.getColumns Seems Slow

Tom,

Thanks for you quick responce.

1. I am not using the XVIEWS (but when I do it only adds about half a second.

2.  Collecting the stats doesn't affect the run time of this query.  Also, our DBC says that those columns don't seem to be very skewed.

3. DDSTATS=ON does not suggest any additional statistics (nor did it before we collected the statistics suggested in Step 1).

-Victor

Teradata Employee

Re: JDBC DatabaseMetadata.getColumns Seems Slow

I should have mentioned a Step zero - make sure you're using the latest version of the Teradata JDBC Driver that's available here on Developer Exchange.

http://downloads.teradata.com/download/connectivity/jdbc-driver

You have a couple of choices for how to proceed.

1. You can query the Data Dictionary tables or views yourself, if your application doesn't need all the information provided by DatabaseMetaData.getColumns. A caveat to keep in mind if you are trying to obtain information about a view's columns -- much of the information for a view's columns is not kept in the Data Dictionary, so your application would have to submit a HELP COLUMN command to retrieve it.

2. If you're a customer, you can open an incident with Customer Support and engage some help to determine why it's taking as long as it is.

Teradata Employee

Re: JDBC DatabaseMetadata.getColumns Seems Slow

It was remis of me not to metion versions.  I have been testing this with both 14.00.00.13 (the latest) and 13.10.00.31 (the latest of the 13.10 versions).

We are using this call because we need to write code that works on other servers as well as Teradata.  Furthermore, since we are going to be moving data between Teradata and other servers we very much wanted to use the mapping of Teradata types to java.sql.Type types as a pivot point for column type management, which is informtion the driver has but isn't availble via HELP COLUMN or by querying DBC (from what I can tell by the query the driver uses for getColumns this mapping is a part of that query).

One thing I find curious is that if I create a prepared statement for "SELECT * FROM CUSTDATA.TRANS" it returns the with the ResultSetMetadata (which has some if not all of the same information as getColumns()) in about 100ms.  The difference between these times is striking.

Teradata Employee

Re: JDBC DatabaseMetadata.getColumns Seems Slow

If your application only needs to obtain column information for a single table or view, then the prepare select * technique can be an alternative.

DatabaseMetaData.getColumns can obtain column information for any number of tables and/or views, which is one reason why it's slower.

Re: JDBC DatabaseMetadata.getColumns Seems Slow

Couple of comments:

Applications which call the JDBC or ODBC 'metadata' methods may find response times to be slow/unpredictable due to the work load of their system. That is, other steps like x-views, driver versions etc make neglible difference.

In terms of using 'prepare-describe'.. always ensure to have a test process which verifies that the ResultSet metadata is describing the values consistently to those you would get via getColumns. Obviously, there is metadata that a prepared query cannot return which your application may be looking for such as RI etc.

_H_
N/A

Re: JDBC DatabaseMetadata.getColumns Seems Slow

/*

We have the same problem.  I've never coded a lick of java but here is what i found.

If teradata controls the SQL used to query the catalog there should be a way for teradata improve the performance.

Can teradata change the SQL?

public ResultSet getColumns(String catalog,

                            String schemaPattern,

                            String tableNamePattern,

                            String columnNamePattern)

                     throws SQLException

*/

/*The sql generated by the getColumns menthod uses this SQL  */

    and t.DatabaseName (not casespecific) like trim(trailing from 'DBNAME') (not casespecific)

/* If it used something like this it wouldn't be so slow

    can you fix it, or has it been fixed in any version past 13.10 */   

    and  (  (  t.DatabaseName (not casespecific) like  trim(trailing from 'DBNAME') (not casespecific)   and position( '%' in 'DBNAME')  > 0    )

          or     t.DatabaseName (not casespecific)  =    trim(trailing from 'DBNAME') (not casespecific)             )

Enthusiast

Re: JDBC DatabaseMetadata.getColumns Seems Slow

Hello,

we have a similar issue with  Java & Teradata:

* We use the current JDBC Teradata 14 driver

* we want to read the table meta data using

   getColumns(null, schema, table, "%");

Using our test system it works fine - it need about 0,5 sec to get the result

Using a real world installation then it need 30 seconds to get the results.

* X-View are not used

* statistics are updated

* Same java routine using MS SQL Server or Oracle is working with normal performance

What can we do? I thought Teradata is helpful for using big data...

Or how can we contact the Teradata support?

Teradata Employee

Re: JDBC DatabaseMetadata.getColumns Seems Slow

If your application does not need all the information provided by DatabaseMetaData.getColumns, then your application can query the Data Dictionary views directly.

Regarding support, if you are a Teradata customer, then you can open an incident with Customer Support.