Our company is in the process of moving to SQL Server 2012 from an earlier version (as part of a Dynamics AX upgrade). We have a database of approximately 60,000 products, and on the new system, loading anything other than the first page of a grid is far too slow. The first page takes a few seconds, any other page is over a minute.
In SQL Server 2012, they’ve introduced a way to specify a limit and offset in the order clause. I’ve created a very hacky workaround to use this in our application, updating the CDbConnection’s driver map to use:
Mssql2012Schema
<?php
class Mssql2012Schema extends CMssqlSchema
{
protected function createCommandBuilder()
{
return new Mssql2012CommandBuilder($this);
}
}
Mssql2012CommandBuilder
<?php
class Mssql2012CommandBuilder extends CMssqlCommandBuilder
{
public function applyLimit($sql, $limit, $offset)
{
return $limit > 0
? $sql . ' OFFSET ' . (int)$offset . ' ROWS '
. 'FETCH NEXT ' . (int)$limit . ' ROWS ONLY'
: $sql;
}
}
Every page loads in the same amount of time now, and it also fixes the problem where you would have duplicate records over the final two pages. It’s not very robust though, as it requires that the order and limit are applied one after the other to prevent a syntax error.
For all of our use cases, it works fine, but is there any plan to add official support for SQL Server 2012+ to the framework? I think following the same approach as I have would work fine, requiring that people override CDbConnection::$driverMap if they wish to use the newer version.
The newer syntax offers much cleaner paging, better performance, and fewer restrictions on the existing criteria. The only annoyance is that it has to be applied as part of the ORDER BY clause, so it doesn’t fit in quite so cleanly with the base command builder class methods.