Uploaded image for project: 'Doctrine DBAL'
  1. Doctrine DBAL
  2. DBAL-93

[ORACLE] Speed improvement of mechanism for limiting query results

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0.1
    • Fix Version/s: 2.1
    • Component/s: Platforms
    • Labels:
      None
    • Environment:
      any

      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.

        Activity

        cicovec Martin Ivičič created issue -
        cicovec Martin Ivičič made changes -
        Field Original Value New Value
        Summary Speed improvement of mechanism for limiting query results [ORACLE] Speed improvement of mechanism for limiting query results
        Description Currently the mechanism for limiting query results in Oracle is suboptimal.

        Doctrine uses the following pseudo SQL statement to limit query results:
        {code:sql}
        SELECT b.* FROM (
           SELECT a.*, ROWNUM AS doctrine_rownum FROM (
              $query
          ) a
        ) b
        WHERE doctrine_rownum BETWEEN $min AND $max
        {code}

        It's much faster to use the following:
        {code:sql}
        SELECT * FROM (
          SELECT a.*, rownum AS doctrine_rownum FROM {
            $query
          ) a WHERE rownum <= $max
        ) WHERE doctrine_rownum >= $min
        {code}

        The updated code in DBAL/Platforms/OraclePlatform would look like this:
        {code}
        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;
        }
        {code}

        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.
        Currently the mechanism for limiting query results in Oracle is suboptimal.

        Doctrine uses the following pseudo SQL statement to limit query results:
        {code:sql}
        SELECT b.* FROM (
           SELECT a.*, ROWNUM AS doctrine_rownum FROM (
              $query
          ) a
        ) b
        WHERE doctrine_rownum BETWEEN $min AND $max
        {code}

        It's much faster to use the following:
        {code:sql}
        SELECT * FROM (
          SELECT a.*, rownum AS doctrine_rownum FROM {
            $query
          ) a WHERE rownum <= $max
        ) WHERE doctrine_rownum >= $min
        {code}

        The updated code in DBAL/Platforms/OraclePlatform.php would look like this:
        {code}
        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;
        }
        {code}

        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.
        beberlei Benjamin Eberlei made changes -
        Fix Version/s 2.0.3 [ 10119 ]
        Fix Version/s 2.0.2 [ 10118 ]
        beberlei Benjamin Eberlei made changes -
        Fix Version/s 2.0.4 [ 10131 ]
        Fix Version/s 2.0.3 [ 10119 ]
        beberlei Benjamin Eberlei made changes -
        Fix Version/s 2.0.5 [ 10132 ]
        Fix Version/s 2.0.4 [ 10131 ]
        beberlei Benjamin Eberlei made changes -
        Fix Version/s 2.0.6 [ 10141 ]
        Fix Version/s 2.0.5 [ 10132 ]
        beberlei Benjamin Eberlei made changes -
        Fix Version/s 2.1 [ 10068 ]
        Fix Version/s 2.0.6 [ 10141 ]
        Hide
        beberlei Benjamin Eberlei added a comment -

        Fixed

        Show
        beberlei Benjamin Eberlei added a comment - Fixed
        beberlei Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        beberlei Benjamin Eberlei made changes -
        Workflow jira [ 12395 ] jira-feedback2 [ 17699 ]
        beberlei Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17699 ] jira-feedback3 [ 20054 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={expand=changesets[0:20].revisions[0:29],reviews, query=DBAL-93}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            beberlei Benjamin Eberlei
            Reporter:
            cicovec Martin Ivičič
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: