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

I have made similar changes in my work to make DatabaseMetaData schema
aware.  Could you not commit this patch unitl reviewing the schema
changes?  I will submit them this weekend.

On 12 Sep 2002, Dave Cramer wrote:

> Panu,
>
> The getIndexInfo uses an outer join which won't be backward compatible?
> I did apply the getTables patch and will commit soon, can you modify the
> getIndexInfo part?
>
> Dave
> On Sun, 2002-09-08 at 14:18, Panu Outinen wrote:
> > 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);
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>