ZF-2094: Bad performance of Zend_Db_Adapter_Oracle::describeTable()

Description

{{Zend_Db_Adapter_Oracle::describeTable()}} method is terribly slow on my system. Single call to that method can take about 10-15 sec. to complete on Celeron 2.6GHz with 768 Mb RAM on Oracle 10g. This causes problems even when schema caching is enabled, especially during development, since caches should be cleaned after almost each actual schema change.

The problem with this method is that it uses rather complex query and do not give Oracle a chance to use its indexes or reuse already prepared statement.

I've found the solution that can improve performace of that method by several orders of magnitude:

{{UPPER()}} functions should be removed from WHERE clause (and letting Oracle use indexes, table and schema names are stored all uppercase in oracle anyway)

Binded arguments should be used for variable substitution to let Oracle reuse prepared statements.

Here is the patch that implements above suggestions:


--- lib/Zend/Db/Adapter/Oracle.php      (revision 1262)
+++ lib/Zend/Db/Adapter/Oracle.php      (revision 1263)
@@ -295,14 +295,15 @@
                 ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND C.CONSTRAINT_TYPE = 'P'))
               ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME
             JOIN ALL_TABLES TB ON (TB.TABLE_NAME = TC.TABLE_NAME AND TB.OWNER = TC.OWNER)
-            WHERE "
-            . $this->quoteInto('UPPER(TC.TABLE_NAME) = UPPER(?)', $tableName);
+            WHERE TC.TABLE_NAME = :TABNAME";
+        $bind = array('TABNAME'=>strtoupper($tableName));
         if ($schemaName) {
-            $sql .= $this->quoteInto(' AND UPPER(TB.OWNER) = UPPER(?)', $schemaName);
+            $sql .= ' AND TB.OWNER = :SCHEMANAME';
+            $bind['SCHEMANAME'] = strtoupper($schemaName);
         }
         $sql .= ' ORDER BY TC.COLUMN_ID';
 
-        $stmt = $this->query($sql);
+        $stmt = $this->query($sql, $bind);
 
         /**
          * Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection

Comments

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

{quote}table and schema names are stored all uppercase in oracle anyway{quote}

In Oracle you can do this:


create table "foo"  (
   "bar"  VARCHAR2(50)  not null,
   "baz"  VARCHAR2(50)  not null
);

By requesting ALL_TAB_COLUMNS or ALL_TABLES, you will obtain in lowercase:


TABLE_NAME                     
------------------------------ 
foo

SVN11667: use prepared statement instead of statement without modifications on case. Application to Zend_Db_Adapter_Pdo_Oci.

Changing issues in preparation for the 1.7.0 release.