DatabaseMetaData.getColumns() with specified schema name gives poor performance

[Andy Jefferson <an...@jpox.org>]

Hi,

I've been using DatabaseMetaData.getColumns(...) to get lists of existing 
columns. The schema is the schema "DATASTOREIDENTITY0" of the Apache JDO 
project TCK.

The schema in Apache JDO is created specifying lowercase identifiers but they 
haven't quoted the identifiers so will be stored UPPERCASE in Derby.

If I call
DatabaseMetaData.getColumns(conn, null, "datastoreidentity0", null);
it returns in the order of 60ms with no results (as I would expect).

If I call
DatabaseMetaData.getColumns(conn, null, "DATASTOREIDENTITY0", null);
it returns in the order of 170000ms with the correct results for the selected 
schema. 


I personally consider 3 minutes just to give basic schema information (of a 
schema that is *not* particularly big) to be totally unacceptable.


1. Has anyone else observed this?
2. Can I register it as a bug?
3. Any alternative query that can be made of schema tables to get the same 
information in an acceptable time ? (With Oracle JPOX has to do a SELECT of 
some system table because Oracles implementation of getColumns() is similarly 
errmmmm poor).


Thanks in advance
-- 
Andy

Re: [-SPAM-] Re: DatabaseMetaData.getColumns() with specified schema name gives poor performance

[Andy Jefferson <an...@jpox.org>]

> Please open a jira issue for this. If you can provide a standalone repro
> for the problem,  that would be helpful.

Raised as
http://issues.apache.org/jira/browse/DERBY-1996
and "Apache JDO TCK" schema attached with small standalone app.


Thanks.
-- 
Andy
Java Persistent Objects - JPOX

Re: DatabaseMetaData.getColumns() with specified schema name gives poor performance

[Sunitha Kambhampati <ks...@gmail.com>]

Andy Jefferson wrote:

>The schema in Apache JDO is created specifying lowercase identifiers but they 
>haven't quoted the identifiers so will be stored UPPERCASE in Derby.
>
>If I call
>DatabaseMetaData.getColumns(conn, null, "DATASTOREIDENTITY0", null);
>it returns in the order of 170000ms with the correct results for the selected 
>schema. 
>
>
>I personally consider 3 minutes just to give basic schema information (of a 
>schema that is *not* particularly big) to be totally unacceptable.
>
>
>2. Can I register it as a bug?
>  
>
Please open a jira issue for this. If you can provide a standalone repro 
for the problem,  that would be helpful.
Junit tests are welcome.

Thanks,
Sunitha.

Re: DatabaseMetaData.getColumns() with specified schema name gives poor performance

[Bryan Pendleton <bp...@amberpoint.com>]

> I personally consider 3 minutes just to give basic schema information (of a 
> schema that is *not* particularly big) to be totally unacceptable.

Yes, this sounds like it is way too long.

Can you construct a small standalone test program that demonstrates
the problem? For example, a program along the lines of:
  - create the database
  - create all the tables, views, indexes, other schema objects
  - get the system time
  - call getColumns()
  - get the system time again and report on the value you got.

That would be useful because it would allow other people to experiment
and see if they encounter your problem.

thanks,

bryan