Speedup patch for getTables() and getIndexInfo() - Mailing list pgsql-jdbc

Hi !!

The following patch speeds up the queries in getTables() and
getIndexInfo(). It replaces the looping of queries with a simple join
operation. Patch is against the current CVS (2002-09-08).

   - Panu

PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases

--------------------------------------------------------------------------

--- AbstractJdbc1DatabaseMetaData.java.org    Sun Sep  8 20:58:57 2002
+++ AbstractJdbc1DatabaseMetaData.java    Sun Sep  8 21:02:58 2002
@@ -1836,7 +1836,9 @@
          f[4] = new Field(connection, "REMARKS", iVarcharOid, NAME_SIZE);

          // Now form the query
-        StringBuffer sql = new StringBuffer("select relname,oid,relkind from
pg_class where (");
+        StringBuffer sql = new
StringBuffer(connection.haveMinimumServerVersion("7.2") ?
+          "select relname,oid,relkind,obj_description(oid,'pg_class') from
pg_class where (" :
+              "select c.relname,c.oid,c.relkind,d.description from pg_class c
left outer join pg_description d on (d.objoid = c.oid) where (" );

          boolean notFirst = false;
          for (int i = 0;i < types.length;i++)
@@ -1863,24 +1865,6 @@
          while (r.next())
          {
              byte[][] tuple = new byte[5][0];
-
-            // Fetch the description for the table (if any)
-            String getDescriptionStatement =
-                connection.haveMinimumServerVersion("7.2") ?
-                "select obj_description(" + r.getInt(2) + ",'pg_class')" :
-                "select description from pg_description where objoid=" + r.getInt(2);
-
-            java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
-
-            byte remarks[] = null;
-
-            if (((AbstractJdbc1ResultSet)dr).getTupleCount() == 1)
-            {
-                dr.next();
-                remarks = dr.getBytes(1);
-            }
-            dr.close();
-
              String relKind;
              switch (r.getBytes(3)[0])
              {
@@ -1911,7 +1895,7 @@
              tuple[1] = null;        // Schema name
              tuple[2] = r.getBytes(1);    // Table name
              tuple[3] = (relKind == null) ? null : relKind.getBytes();    // Table type
-            tuple[4] = remarks;        // Remarks
+            tuple[4] = r.getBytes(4);    //Remarks
              v.addElement(tuple);
          }
          r.close();
@@ -3096,8 +3080,8 @@
                                 "x.indkey, " +
                                 "c.reltuples, " +
                                 "c.relpages, " +
-                               "x.indexrelid " +
-                               "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
+                               "b.attname " +
+                               "FROM pg_index x left outer join pg_attribute b on (x.indexrelid
= b.attrelid), pg_class c, pg_class i, pg_am a " +
                                 "WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
                                 " AND (c.oid = x.indrelid) " +
                                 " AND (i.oid = x.indexrelid) " +
@@ -3118,7 +3102,6 @@
              {
                  columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
              }
-            java.sql.ResultSet columnNameRS = connection.ExecSQL("select a.attname
FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
              for (int i = 0; i < columnOrdinals.length; i++)
              {
                  byte [] [] tuple = new byte [13] [];
@@ -3134,14 +3117,7 @@
                             Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes()
:
                             Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
                  tuple[7] = Integer.toString(i + 1).getBytes();
-                if (columnNameRS.next())
-                {
-                    tuple[8] = columnNameRS.getBytes(1);
-                }
-                else
-                {
-                    tuple[8] = "".getBytes();
-                }
+                tuple[8] = r.getBytes(9);
                  tuple[9] = null;  // sort sequence ???
                  tuple[10] = r.getBytes(7);    // inexact
                  tuple[11] = r.getBytes(8);