Grails generates questionable pagination queries for MSSQL

Question

I'm using Grails 1.3.7 and MSSQL Server 2008. Doing simple query through both dynamic finders or criteria like this:

Invoice.findAllByClient(client, [max: 25, offset: 100000, sort: 'title'])

or

Invoice.createCriteria().list(max: 25, offset: 100000) {
    eq('client', client)
    order('title')
}

and watching the actual query that is thrown to the MSSQL Server:

select top 100000 etc

The query is obviously inefficient. Is that a bitter truth with Grails that cannot generate an efficient query for MSSQL or I'm missing something?

Please help!

Answer 1

No, it's not that Grails (or rather Hibernate, the underlying database technology) cannot generate somewhat more efficient queries. It doesn't due to the limitations of SQL Server 2000, which does not support more efficient generic syntax for paginating queries (see e.g.).

If you don't specify a dialect for your data source, Hibernate will default to using the base SQLServerDialect, which generates the inefficient queries you are seeing. Switching to the SQLServer2008Dialect in your data source should generate somewhat more efficient queries using common table expressions, e.g. (taken from the Javadoc),

WITH query AS (
    SELECT ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__,
    original_query_without_orderby
)
SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN offset AND offset + last

To do this in Grails, simply set the dialect property in conf/Datasource.groovy as documented here, e.g.

dataSource {
   // configuration you already have
   dialect = org.hibernate.dialect.SQLServer2008Dialect
}