offset

[andreacfm]

Steve,

did you ever think to implement the offset as limit in the db query ?
This could really be a great speed improvement.
Now is just a "cut" of the returned query.
Isn't it?

Andrea

[Steve Bryant]

Andrea,

Great question. That is actually based on database support. So, in
some databases (Access, DB2, Derby, MSSQL) it is just a cut of the
returned query. In others (MySQL, Oracle, PostGreSQL) the OFFSET
statement is used in the query itself.

At some point, I would like to create an "MSSQL2008" adaptor to take
advantage of the new features in that version of MS SQL (the "MSSQL"
adaptor is documented to support 2000 and above).

For anyone writing your own adaptor for a database with offset
support, see the following methods for offset functionality:
dbHasOffset, getMaxRowsPrefix, getMaxRowsSuffix, getMaxRowsWhere.

Steve

[Andrea Campolonghi]

Great 

Thanks

2010/7/23 Steve Bryant <sebt...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "DataMgr" group.
To post to this group, send email to dat...@googlegroups.com.
To unsubscribe from this group, send email to datamgr+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/datamgr?hl=en.

--
Andrea Campolonghi

Cell : +39 347 2298435
and...@andreacfm.com
http://www.andreacfm.com

Railo Team
and...@getrailo.org
http://getrailo.org

[Sean Ford]

I too, would like to see dataMgr handle offsets more efficiently.
I've just started to convert a personal project from hand-written CRUD
cfcs to either CF9 ORM or dataMgr, and this is one area where I see a
drawback to dataMgr. (at least when it pertains to MSSQL).

In the short-term, would it be possible to create the new MSSQL2008
connecter based on the current one, but adding in functionality to
offer paging in a more optimized format. Right now, I use the
following template for my paging queries:

---
WITH CompleteResultset AS (
SELECT ROW_NUMBER() OVER ( ORDER BY dinnerTimeID DSC ) AS Row,
*
FROM lookupDinnerTimes
WHERE 1 = 1
)

SELECT *
FROM CompleteResultset
WHERE Row BETWEEN [offset] AND [offset +maxrows -1]
---

For large datasets, you can add TOP [offset +maxrows -1] after the
first SELECT, so that the database only returns as many records as
necessary.


--
Sean Ford
se...@seanford.com

On Jul 23, 9:58 am, Andrea Campolonghi <acampolon...@gmail.com> wrote:
> Great
>
> Thanks
>

> 2010/7/23 Steve Bryant <sebto...@gmail.com>

>
>
>
>
>
> > Andrea,
>
> > Great question. That is actually based on database support. So, in
> > some databases (Access, DB2, Derby, MSSQL) it is just a cut of the
> > returned query. In others (MySQL, Oracle, PostGreSQL) the OFFSET
> > statement is used in the query itself.
>
> > At some point, I would like to create an "MSSQL2008" adaptor to take
> > advantage of the new features in that version of MS SQL (the "MSSQL"
> > adaptor is documented to support 2000 and above).
>
> > For anyone writing your own adaptor for a database with offset
> > support, see the following methods for offset functionality:
> > dbHasOffset, getMaxRowsPrefix, getMaxRowsSuffix, getMaxRowsWhere.
>
> > Steve
>

> > On Fri, Jul 23, 2010 at 6:57 AM, andreacfm <acampolon...@gmail.com> wrote:
> > > Steve,
>
> > > did you ever think to implement the offset as limit in the db query ?
> > > This could really be a great speed improvement.
> > > Now is just a "cut" of the returned query.
> > > Isn't it?
>
> > > Andrea
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "DataMgr" group.
> > To post to this group, send email to dat...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > datamgr+u...@googlegroups.com<datamgr%2Bunsu...@googlegroups.com >
> > .

[Steve Bryant]

Sean,

That is exactly the plan. It is just a matter of making the time to do it.

If you are in a hurry to get that, you could always make the connector
yourself. As you indicated, it shouldn't be too difficult.

I am hoping to get to it pretty soon myself, but I don't have a
history of making good predictions of when I will get things done for
my open source projects (client demands tending to overwhelm internal
plans).

Steve

> To unsubscribe from this group, send email to datamgr+u...@googlegroups.com.

[Sean P. Ford]

Steve,

I looked into this a bit tonight and frankly don't know where to start. I thought about adding a supported database type called MSSQL2008, but in reality, I'm not sure this is the right approach. The ROW_NUMBER() attribute and associated functions to use it for OFFSET mimicking have been around since MSSQL2005. That leaves only MSSQL2000 as unsupported, while both MSSQL2008 and R2 support it as well. Would it not be better to rewrite the default query for the newer (and presumably, more popular) versions in use, and have a fallback to the un-optimized version for 2000? I would imagine that once the database is identified as MSSQL we could run SELECT SERVERPROPERTY('productversion') to get the version number, with version 8 and lower getting the fallback code and 9 and higher taking advantage of the code I posted earlier.

Also, from the post I read earlier (along with the 2.5 docs) I tried the offset method, be it unoptimized, on a test query and it did not work on a MSSQL table. Is it implemented yet? (I am running the 2.5 beta.)

Sean

Sean,

Steve

>> > datamgr+u...@googlegroups.com<datamgr%2Bunsubscribe@googlegr
>> > datamgr+oups.com >

[Steve Bryant]

Sean,

You have inspired me.

I didn't know about SELECT SERVERPROPERTY('productversion') and that
really opened some doors for me. Although my offset was working for
SQL Server on my local copy (probably some modification that I hadn't
committed to the public repo yet), I went ahead and added the feature
using a version of the syntax found here:
http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server

By use of the SQL syntax you provided to get a version number, I was
able to avoid having a separate file for different versions of SQL
Server. Instead it detects the version currently running and responds
appropriately (either using the default slicing behaviour or the newer
syntax available to SQL Server 2005 and better).

I uploaded all of this to the public repo:
http://sebtools-svn.cvsdude.com/DataMgr

What is up here is very close to what will be DataMgr 2.5 Beta 1, I
just have a few very small tweaks and a bit of testing between here
and there (not to mention some vacation time).

Thanks for the help on this and let me know how it goes for you,

Steve

[Sean P. Ford]

Steve,

Great to hear..I'm going to play with it this weekend and will report back.

By the way, not a big deal, but I just noticed while pulling down the files that the displayname of some of the DB-specific files still read MS-SQL....can't say I haven't done that a few hundred times...lol

--Sean

Sean,

You have inspired me.

Steve

>>> > datamgr+r