Zend Framework

• Type: Patch
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.0.1, 1.0.2
• Fix Version/s: 1.7.0
• Component/s: Zend_Db_Adapter_Oracle
• Labels:

  None

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:

1. UPPER() functions should be removed from WHERE clause (and letting Oracle use indexes, table and schema names are stored all uppercase in oracle anyway)
2. 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

• Assignee:

  Mickael Perraud

  Reporter:

  Andrey Lebedev

• Created:

  24/Oct/07 2:18 AM

  Updated:

  01/Dec/08 1:23 PM

  Resolved:

  04/Oct/08 1:47 AM