Zend Framework

• Type: Improvement
• Status: Resolved
• Priority: Trivial
• Resolution: Fixed
• Affects Version/s: 1.6.2, 1.7.6
• Fix Version/s: 1.9.2
• Component/s: Zend_Db
• Labels:

  None

When no offset is given in a Zend_Db_Select object, the SQL generated by Zend_Db_Adapter_Pdo_Mssql gives non-optimal results. It looks like 'SELECT * FROM(SELECT TOP n * FROM(SELECT TOP m * FROM... ORDER BY fld DESC) ORDER BY fld ASC as inner_tbl) ORDER BY fld DESC as outer_tbl.

This convoluted query is only needed if an offset if given. If only a limit is given, a much simpler and lighter query can be used: SELECT TOP n * FROM...

I propose the following diff:

===================================================================
--- library-1.6.2/Zend/Db/Adapter/Pdo/Mssql.php 2009-02-18 07:41:49 UTC (rev 6802)
+++ library-1.6.2/Zend/Db/Adapter/Pdo/Mssql.php 2009-02-18 08:01:42 UTC (rev 6803)
@@ -321,17 +321,21 @@
             $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order));
         }

-        $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($count+$offset) . ' ', $sql);
+        if ($offset > 0) {
+            $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . 
+ ($count+$offset) . ' ', $sql);

-        $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
-        if ($orderby !== false) {
-            $sql .= ' ORDER BY ' . $order . ' ';
-            $sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
+            $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
+            if ($orderby !== false) {
+                $sql .= ' ORDER BY ' . $order . ' ';
+                $sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
+            }
+            $sql .= ') AS outer_tbl';
+            if ($orderby !== false) {
+                $sql .= ' ORDER BY ' . $order . ' ' . $sort;
+            }
+        } else {
+            $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . 
+ ($count) . ' ', $sql);
         }
-        $sql .= ') AS outer_tbl';
-        if ($orderby !== false) {
-            $sql .= ' ORDER BY ' . $order . ' ' . $sort;
-        }

         return $sql;
     }

• Assignee:

  Ralph Schindler

  Reporter:

  TNF ICT Dept

• Created:

  18/Feb/09 12:12 AM

  Updated:

  24/Aug/09 10:50 AM

  Resolved:

  24/Aug/09 10:50 AM