Uploaded image for project: 'Hibernate ORM'
  1. HHH-7370

SQLServer2005Dialect: sql-transformation to common_table_expression not needed when offset = 0

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects versions: 4.1.4
    • Fix versions: 4.1.5
    • Components: hibernate-core
    • Environment:
      SQLServer2005, SQLServer2008, SQLServer2010
    • Last commented by a user?:
      true
    • Sprint:

      Description

      When using pagination feature (setMaxResults) without setting an offset (setFirstResult( 0 )), it is not necessary to perform the complex sql-transformation into a common_table_expression.
      In such case it is much more simplier to use the old approach by using the "top" keyword.

      createSQLQuery("select id from Product2 order by id").setMaxResults(2)

      Instead to transform it into:

       WITH query AS (select
              id,
              ROW_NUMBER() OVER (
          ORDER BY
              CURRENT_TIMESTAMP) - 1 as __hibernate_row_nr__ 
          from
              Product2 
          order by id) SELECT
              *
          FROM
              query 
          WHERE
              __hibernate_row_nr__ >= 2 
              AND __hibernate_row_nr__ < 4  
      

      we would trasform it into:

      select top (2) id from Product2 order by id
      

      Motivations:

      • the resulting sql is smaller and easier to read
      • the trasformation is faster (probably also the execution on sqlserver)
      • the trasformation is more robust against bugs, as it is much siplier.

      Pullrequest will follow asap...

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: