Derby
  1. Derby
  2. DERBY-1996

DatabaseMetaData.getColumns() takes long time to return columns for a particular schema of Apache JDO TCK

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Performance

      Description

      When running the Apache JDO "TCK", JPOX needs to query the columns for a particular schema, and so uses DatabaseMetaData.getColumns(null, schemaName, null, null). This takes the order of 3 minutes on a dual-core Pentium 3GHz (with large ram). I've cut the JPOX part down to a stand-alone test (will attach it).

      Thing to note is that with only the schema files d_* installed it takes the order of 20secs. When the schema_* files are also installed then performance goes downhill fast (even though they are in different schemas ... all *.sql file represents a different schema). No point in me postulating what could be the issue since you guys are the Derby experts.

      1. testcase.zip
        59 kB
        Andy Jefferson

        Issue Links

          Activity

          Gavin made changes -
          Workflow jira [ 12387656 ] Default workflow, editable Closed status [ 12801779 ]
          Mamta A. Satoor made changes -
          Labels derby_triage10_11
          Knut Anders Hatlen made changes -
          Issue Type Bug [ 1 ] Improvement [ 4 ]
          Bug behavior facts [Performance]
          Issue & fix info [Repro attached]
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.
          Hide
          Rick Hillegas added a comment -

          Hi Andy,

          It doesn't look like anyone is working on this issue. You can vote for it and you can advocate for it on derby-dev or derby-user. Note that there are 3 weeks left before we cut the 10.4.2 release.

          The following query may give you some of what you need. I have no idea how this query performs on your schema:

          select s.schemaname, t.tablename, c.columnname, c.columnnumber, c.columndatatype
          from sys.sysschemas s, sys.systables t, sys.syscolumns c
          where c.referenceid = t.tableid
          and t.schemaid = s.schemaid
          and t.tabletype = 'T'
          order by s.schemaname, t.tablename, c.columnname, c.columnnumber
          ;

          A couple points about this query:

          o You can adjust this query by consulting the layout of the system tables. That layout is described in the Derby Reference Guide.

          o The columndatatype column is an object of type org.apache.derby.catalog.TypeDescriptor. That class is not in Derby's public API and therefore is not guaranteed to be stable across releases (although my sense is that it has tended to evolve in upward-compatible ways).

          Show
          Rick Hillegas added a comment - Hi Andy, It doesn't look like anyone is working on this issue. You can vote for it and you can advocate for it on derby-dev or derby-user. Note that there are 3 weeks left before we cut the 10.4.2 release. The following query may give you some of what you need. I have no idea how this query performs on your schema: select s.schemaname, t.tablename, c.columnname, c.columnnumber, c.columndatatype from sys.sysschemas s, sys.systables t, sys.syscolumns c where c.referenceid = t.tableid and t.schemaid = s.schemaid and t.tabletype = 'T' order by s.schemaname, t.tablename, c.columnname, c.columnnumber ; A couple points about this query: o You can adjust this query by consulting the layout of the system tables. That layout is described in the Derby Reference Guide. o The columndatatype column is an object of type org.apache.derby.catalog.TypeDescriptor. That class is not in Derby's public API and therefore is not guaranteed to be stable across releases (although my sense is that it has tended to evolve in upward-compatible ways).
          Hide
          Andy Jefferson added a comment -

          Any comment on when (approx) DatabaseMetaData.getColumns will be capable of giving reasonable performance in situations like this? or is there some SELECT that we can do ourselves to get the same basic information without having to go through getColumns() ?? Thx

          Show
          Andy Jefferson added a comment - Any comment on when (approx) DatabaseMetaData.getColumns will be capable of giving reasonable performance in situations like this? or is there some SELECT that we can do ourselves to get the same basic information without having to go through getColumns() ?? Thx
          Bernt M. Johnsen made changes -
          Link This issue is related to DERBY-2584 [ DERBY-2584 ]
          Mike Matrigali made changes -
          Component/s SQL [ 11408 ]
          Andy Jefferson made changes -
          Field Original Value New Value
          Attachment testcase.zip [ 12343570 ]
          Hide
          Andy Jefferson added a comment -

          To run the test, i've included Maven1 files.

          maven clean installschema
          will install all *.sql files

          maven jar runtest
          will run a simple JDBC test of DatabaseMetaData.getColumns()

          Show
          Andy Jefferson added a comment - To run the test, i've included Maven1 files. maven clean installschema will install all *.sql files maven jar runtest will run a simple JDBC test of DatabaseMetaData.getColumns()
          Andy Jefferson created issue -

            People

            • Assignee:
              Unassigned
              Reporter:
              Andy Jefferson
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Development