One thing to keep in ming with OFBiz, and most webapps, is that you won't hold a cursor open for long. Depending on how the db handles things using a short-life cursor versus offset/limit may not make too much of a difference, on the other hand it may... Anyway, I added it in Moqui yesterday, but adding it in OFBiz would be a bit more work. If anyone goes for it chances are it would make it into the project if contributed though.
UNION is interesting, but that's one feature of SQL that I haven't found the need to use much. I suppose it depends on your data model though... dunno, maybe sometimes it's quite useful. -David On Feb 28, 2011, at 5:26 AM, Mark Gordon wrote: > Thanks David. > > The problem is that we have some huge tables. 9 million+ records. And the > limit and offset are perfect for dealing with these large tables. Postgres > can grab a 1000 records starting at record 5,000,000 very efficiently. This > allows us to operate on large datasets without holding a cursor open. Also > for UI's that page through data it is perfect. For the UI side you can > close/reopen cursor as normally people are not paging through 10's of > thousands of records. > > I noticed that someone has been working on UNION statements inside the > entity engine. That would be a nice addition. > > -Mark > > On Sun, Feb 27, 2011 at 5:12 PM, David E Jones <[email protected]> wrote: > >> >> That's somewhat true, ie OFBiz mostly uses generic SQL, but there are >> variations supported for different databases configured on the datasource >> element in the entityengine.xml file. Most of the variations are for the >> meta-data operations (maintain tables, columns, indexes, fks), but they are >> not limited to that. >> >> If someone wanted to implement such a thing it could certainly be done. >> This is right up there on the would be nice list with the "FOR UPDATE" >> support. I've already added FOR UPDATE support to Moqui (or rather included >> it in the original design), and this is a good point that I'll consider over >> there instead of relying on a cursor to do the offset/limit. >> >> -David >> >> >> On Feb 27, 2011, at 1:14 PM, Scott Gray wrote: >> >>> Hi Mark >>> >>> You can impose a limit by using EntityFindOptions.setMaxRows(...) but >> there isn't any option to set an offset because the syntax (and in some >> cases even support) varies across different databases. OFBiz doesn't >> support differing SQL dialects per database so at the moment we're limited >> to commands that have universal support. >>> >>> The only real work around is to use an EntityListIterator and then page >> forward to the desired offset. >>> >>> Regards >>> Scott >>> >>> HotWax Media >>> http://www.hotwaxmedia.com >>> >>> On 28/02/2011, at 8:27 AM, Mark Gordon wrote: >>> >>>> Hello, >>>> >>>> I have a small application that is using a version of the entity engine >> from >>>> 2007. We want to be able to use limit and offset functionality of our >>>> database (postgres). I have scanned the current trunk release and >> release >>>> 10.04 to see if there is any offset and limit. I have seen some >> references >>>> but I can figure out how to use it and even if it is what I am looking >> for. >>>> >>>> Is this functionality in the latest trunk? Can anyone point to how to >> use >>>> it if it is indeed available. >>>> >>>> Thanks! >>>> >>>> Mark >>> >> >>
