[DBIx-Class-Devel] OffsetFetchNext Support for SQL Server 2012

[fREW Schmidt]

I just pushed a totally working version of OffsetFetchNext.  OFN is
the new pagination for SQL Server 2012 and it's pretty great compared
to what we had before.  To be clear, here is the difference:

   -- BEFORE

   SELECT [me].[id], [me].[from], [me].[to], [me].[group_id]
     FROM (
       SELECT [me].[id], [me].[from], [me].[to], [me].[group_id],  ROW_NUMBER() OVER (
           ORDER BY
           SELECT( 1 )
          ) AS [rno__row__index]
         FROM (
           SELECT [me].[id], [me].[from], [me].[to], [me].[group_id]
             FROM [Lookup] [me]
          ) [me]
      ) [me]
   WHERE [rno__row__index] >= '1' AND [rno__row__index] <= '25'


   -- AFTER

   SELECT [me].[id], [me].[from], [me].[to], [me].[group_id]
     FROM [Lookup] [me]
   ORDER BY 1
   OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

SQL Server claims that the former costs 8% more than the latter, so
that's a pretty nice win too.  Also, as riba pointed out to me, with
the latter we will always be generating the same kind of query whether
or not we have an offset, so queries should be cached better.

The commits are viewable at
https://github.com/dbsrgits/dbix-class/commits/topic/mssql-2012-limit-dialect

I think "more fine grained versioning" should be squashed into
"refactor code needing version", but wanted to make sure that made
sense to others first.

The real question is what the right soln is for "Steal bindtype
transforms from SQLite".  For some reason SQL Server is much more
picky wrt bind types in the new pagination, so I had to add that in.
I suspect that we may need to make a better regex for INTEGERs for SQL
Server, eg we should add bits already.  Wanted to get some thoughts
there.

Lastly, wrt the actual implementation, the only kinda gross thing in
my mind is the hardcoded \'1' and ' ORDER BY 1'.  I'm open to better
solutions.

--
fREW Schmidt
http://blog.afoolishmanifesto.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20130719/6556a477/attachment.pgp