[DBAL-93] [ORACLE] Speed improvement of mechanism for limiting query results Created: 16/Feb/11  Updated: 17/Jun/11  Resolved: 17/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.1
Fix Version/s: 2.1

Type: Improvement Priority: Minor
Reporter: Martin Ivičič Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

any


Sub-Tasks:
Key
Summary
Type
Status
Assignee
DBAL-130 Add tests for modify limit query func... Sub-task Resolved Benjamin Eberlei  

 Description   

Currently the mechanism for limiting query results in Oracle is suboptimal.

Doctrine uses the following pseudo SQL statement to limit query results:

SELECT b.* FROM (
   SELECT a.*, ROWNUM AS doctrine_rownum FROM (
      $query
  ) a
) b
WHERE doctrine_rownum BETWEEN $min AND $max

It's much faster to use the following:

SELECT * FROM (
  SELECT a.*, rownum AS doctrine_rownum FROM {
    $query
  ) a WHERE rownum <= $max
) WHERE doctrine_rownum >= $min

The updated code in DBAL/Platforms/OraclePlatform.php would look like this:

public function modifyLimitQuery($query, $limit, $offset = null)
{
	$limit = (int) $limit;
	$offset = (int) $offset;
	if (preg_match('/^\s*SELECT/i', $query)) {
		if ( ! preg_match('/\sFROM\s/i', $query)) {
			$query .= " FROM dual";
		}
		if ($limit > 0) {
			$max = $offset + $limit;
			$column = '*';
			if ($offset > 0) {
				$min = $offset + 1;
				$query = 'SELECT * FROM (SELECT a.' . $column . ', rownum AS doctrine_rownum FROM (' . 
						 $query . 
						 ') a WHERE rownum <= ' . $max . ') WHERE doctrine_rownum >= ' . $min;
			} else {
				$query = 'SELECT a.'.$column.' FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
			}
		}
	}
	return $query;
}

With the current implementation the request for fairly complicated query selecting data from multiple tables one of which reaches 2 million rows takes 5.98 seconds comparing to 1.64 with the proposed one.

In addition It would be nice to have the min and max values as SQL query parameters so that Oracle's internal caching mechanisms could take part, but I'm aware of the fact that it would not be so easy to implement.



 Comments   
Comment by Benjamin Eberlei [ 17/Jun/11 ]

Fixed

Generated at Wed Apr 23 16:30:44 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.