Grails 为 MSSQL 生成有问题的分页查询
我正在使用 Grails 1.3.7 和 MSSQL Server 2008。 通过动态查找器或标准进行简单查询,如下所示:
Invoice.findAllByClient(client, [max: 25, offset: 100000, sort: 'title'])
or
Invoice.createCriteria().list(max: 25, offset: 100000) {
eq('client', client)
order('title')
}
并观察抛出到 MSSQL Server 的实际查询:
select top 100000 等
查询显然效率低下。对于 Grails 来说,这是一个痛苦的事实,它无法为 MSSQL 生成有效的查询,还是我遗漏了一些东西?
请帮忙!
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!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
不,这并不是说 Grails(或者更确切地说 Hibernate,底层数据库技术)无法生成更有效的查询。这并不是由于 SQL Server 2000 的限制,SQL Server 2000 不支持用于分页查询的更有效的通用语法(请参阅 例如)。
如果您没有指定方言 对于您的数据源,Hibernate 将默认使用基本 SQLServerDialect,其中 生成您所看到的低效查询。切换到 SQLServer2008Dialect数据源应该使用公共表表达式生成更有效的查询,例如(取自 Javadoc),
要在 Grails 中执行此操作,只需在中设置
dialect
属性conf/Datasource.groovy
为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),
To do this in Grails, simply set the
dialect
property inconf/Datasource.groovy
as documented here, e.g.