Home » Database » MDB2_Driver_mssql » Bug #4544

[2005-06-07 16:51 UTC] erudd at netfor dot com

Description:
------------
I have a block of SQL code that I am using to emulate MySQLs limit feature in MSSQL by using a cursor.

The example below will retrieve rows 10 to 30.

now quite exactly how easy it would be to integrate this logic into the mssql driver (and sybase driver as well, probably others).

but it works rather quickly and better than my previous hack at doing sublimit queries.

SELECT TOP 5 Name, uname FROM users WHERE uname NOT IN (SELECT TOP 10 uname FROM users ORDER BY uname ASC) ORDER BY uname ASC

This would retrieve records 11 to 15, but requires knowing what the primary key(s) of the table are to work..

Reproduce code:
---------------
$db =& DB::connect('mssql://user:poass@server/database");

$sql = <<< EOSQL
DECLARE Search CURSOR LOCAL SCROLL READ_ONLY FOR
SELECT * FROM MyTable
DECLARE @limit INT, @offset INT
SET @limit = 20
SET @offset = 10+1
OPEN Search
FETCH ABSOLUTE @offset FROM Search
WHILE @@FETCH_STATUS =0 AND @limit > 1
BEGIN
FETCH NEXT FROM Search
SET @limit = @limit - 1
END
CLOSE Search
DEALLOCATE Search
EOSQL;
$res =& $db->query($sql);
$row =& $res->fetchRow();
do {
echo $row[0]."\n";
$row =& $res->fetchRow();
if (is_null($row)) {
if ($res->nextResult()) {
$row =& $res->fetchRow();
}
}
} while ($row);

[2006-06-19 21:51 UTC] erudd at netfor dot com

This does not require knowledge of the Primary key at all.
It goes soly based on the record index. so it works EXACLTY like LIMIT 10,30 in MySQL.

[2006-06-19 22:00 UTC] erudd at netfor dot com

Exactly it would just return rows 11 through 30.
The only *oddity* and thing that would have to be worked around is the fact that the cursor would return back 20 individual result sets of 1 record. AND when pulling back with an associative array an additional field of 'rowstat' with a value of 1 is returned. (no clue how to tell mssql to not send it, or if it's a php-mssql extension issue).

[2006-06-19 22:17 UTC] erudd at netfor dot com

I'm using this heavily in a webbased ticketing system and havn't noticed any "performance" problems or overhead.

It seems to be less overhead as the server is doing limit emulation instead of having php pull down a few hundred records only to do something with the last 20. This cursor block would have the server do that and only return what I really need.

Though due to the way this works with MSSQL (I haven't found a way to get MSSQL to return one result set) it does require the special PHP code block to pull results back, which I have cooerced into a single while () expression.

while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC
|| ($res->nextResult()
&& $row =& $res->fetchRow(DB_FETCHMODE_ASSOC ))) {

This is still PEAR::DB and not PEAR::MDB2, but should be the same except the constant. I have not yet converted my code to MDB2 (which I like the transaction support MUCH better in MDB2)

[2006-07-04 18:48 UTC] webmaster at kryptronic dot com

I am using a similar method. Instead of using setLimit() on the query before it's executed, I'm re-writing the query as follows and passing it off to MDB2's appropriate function.

For UPDATE/DELETE queries:

$sql = preg_replace('/^UPDATE /i',"UPDATE TOP $count}",$sql);
$sql = preg_replace('/^DELETE /i',"DELETE TOP $count}",$sql);

Which is then passed off to exec($sql).

For SELECT queries the statement is being re-written as follows:

SELECT * FROM
(
SELECT TOP $rows * FROM
(
SELECT TOP $last $sql_after_select
ORDER BY $order_clause
) AS foo
ORDER BY $order_clause_rev
) AS bar
ORDER BY $order_clause

$rows = The count normally passed to setLimit().

$last = $rows plus the offset normally passed to setLimit().

$sql_after_select = preg_replace('/^SELECT /i','',$sql);

$order_clause = A valid ORDER BY clause. Here when one isn't defined I order by the first column I see in the table DESC (ie. ORDER BY id DESC).

$order_clause_rev = A reversed ORDER BY clause. For the example above I would use ORDER BY id ASC.

This works great and consumes less time and server resources than any other method I've tried. A good discussion of this can be found here:

http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/