Concerned about how ofbiz is doing paginate

[ian tabangay]

Hi. I was taking a look at how to indicate an offset into my findList
queries and I stumbled upon a quite disturbing behavior within ofbiz and I
would like to ask why it was design like that.
I was taking a look at the service 'performFindList' (in
org.ofbiz.common.FindServices of rev 686591) to see how it does paginating.
Heres a snippet of the code and how I understood the code:

// service calls performFind method to get an instance of EntityListIterator
(line 358)
        Map result = performFind(dctx,context);
        ...
// retrieve the target range of values from the EntityListIterator (lines
369 and 370)
        EntityListIterator it = (EntityListIterator) result.get("listIt");
        list = it.getPartialList(start+1, viewSize.intValue());
        ...
// return the partial list as the result of the service
        result.put("list",list);

Further inspecting how EntityListIterator retrieves the partial list, heres
a snippet of the method getPartialList from the class
org.ofbiz.entity.util.EntityListIterator (of rev 673024) and how I
understood the code:

// attempts to move resultSet pointer to the desired offset (line 457)
         if (!resultSet.absolute(start)) {
         ....
// calls resultset.next() to get the values until it has retrieved the
desired size or there is nothing more to get from the resultSet (lines
472-475)
        while (number > numRetreived && (nextValue = this.next()) != null) {
            list.add(nextValue);
            numRetreived++;
        }

This is what I understood from the codes mentioned:
1. The resultSet returns all of the rows as qualified by the prepared
statement.
2. Paging is done by selecting only the target rows.

Implications:
1. The query will always retrieve the all of the rows regardless of the
viewSize and viewIndex.
2. Traversing from each pages would mean retrieving the all of the rows
everytime a page is loaded.

I think this puts alot of unnecessary load into the application when some of
it could be performed within the database level. It was also the reason why
I was looking for a way to put in 'offset' within the query.
Anyways please do comment on my observation. Please correct me if I
misunderstood the code.
Thanks.


~ ian

[David E Jones]

Your assertions are incorrect, that's not how it works.

I would recommend reading up a bit more on JDBC and how ResultSet data  
is handled along with cursors in the database.

-David


On Sep 9, 2008, at 5:06 AM, ian tabangay wrote:

> Hi. I was taking a look at how to indicate an offset into my findList
> queries and I stumbled upon a quite disturbing behavior within ofbiz  
> and I
> would like to ask why it was design like that.
> I was taking a look at the service 'performFindList' (in
> org.ofbiz.common.FindServices of rev 686591) to see how it does  
> paginating.
> Heres a snippet of the code and how I understood the code:
>
> // service calls performFind method to get an instance of  
> EntityListIterator
> (line 358)
>        Map result = performFind(dctx,context);
>        ...
> // retrieve the target range of values from the EntityListIterator  
> (lines
> 369 and 370)
>        EntityListIterator it = (EntityListIterator)  
> result.get("listIt");
>        list = it.getPartialList(start+1, viewSize.intValue());
>        ...
> // return the partial list as the result of the service
>        result.put("list",list);
>
> Further inspecting how EntityListIterator retrieves the partial  
> list, heres
> a snippet of the method getPartialList from the class
> org.ofbiz.entity.util.EntityListIterator (of rev 673024) and how I
> understood the code:
>
> // attempts to move resultSet pointer to the desired offset (line 457)
>         if (!resultSet.absolute(start)) {
>         ....
> // calls resultset.next() to get the values until it has retrieved the
> desired size or there is nothing more to get from the resultSet (lines
> 472-475)
>        while (number > numRetreived && (nextValue = this.next()) !=  
> null) {
>            list.add(nextValue);
>            numRetreived++;
>        }
>
> This is what I understood from the codes mentioned:
> 1. The resultSet returns all of the rows as qualified by the prepared
> statement.
> 2. Paging is done by selecting only the target rows.
>
> Implications:
> 1. The query will always retrieve the all of the rows regardless of  
> the
> viewSize and viewIndex.
> 2. Traversing from each pages would mean retrieving the all of the  
> rows
> everytime a page is loaded.
>
> I think this puts alot of unnecessary load into the application when  
> some of
> it could be performed within the database level. It was also the  
> reason why
> I was looking for a way to put in 'offset' within the query.
> Anyways please do comment on my observation. Please correct me if I
> misunderstood the code.
> Thanks.
>
>
> ~ ian

[ian tabangay]

Thank you for your reference. I have read it and my understanding is still
the same. Sorry for being hard headed. I made a bench mark between
pagination using ResultSet data with cursors and pagination using limit and
offset. Here's my code for both test:

  /**
  * Test for pagination via ResultSet cursor
  **/
  public void testResultSet() throws Exception {
    Class.forName("org.postgresql.Driver");
    String url = "jdbc:postgresql://127.0.0.1/ofbiz";
    Connection con = DriverManager.getConnection(url, "postgres",
"postgres");
    Statement ps = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
    int size = 100;
    int index = 217;
    int number = size * index;
    long startTime = System.currentTimeMillis();
    ResultSet rs = ps.executeQuery("SELECT * FROM product_facility");
    if (!rs.next()) {
      System.err.println("Nothing to return");
      return;
    }
    if (!rs.absolute(number)) {
      System.err.println("Failed to set cursor");
      return;
    }
    int counter = 0;
    do {
      store(rs);
      counter ++;
    } while (size > counter && rs.next());
    System.out.println("ENDED: " + (System.currentTimeMillis() -
startTime));
  }

  /**
  * Test for pagination via LIMIT and OFFSET
  **/
  public void testLimitOffset() throws Exception {
    Class.forName("org.postgresql.Driver");
    String url = "jdbc:postgresql://127.0.0.1/ofbiz";
    Connection con = DriverManager.getConnection(url, "postgres",
"postgres");
    Statement ps = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
    int size = 100;
    int index = 217;
    int number = size * index;
    long startTime = System.currentTimeMillis();
    ResultSet rs = ps.executeQuery("SELECT * FROM product_facility LIMIT " +
size + " OFFSET " + number);
    if (!rs.next()) {
      System.err.println("Nothing to return");
      return;
    }
    do {
      store(rs);
    } while (rs.next());
    System.out.println("ENDED: " + (System.currentTimeMillis() -
startTime));
  }

Having a row size of 21897, here are my results (in ms):
testResultSet(): 875, 922, 875, 875, 859 (Ave: 881.2 ms)
testLimitOffset(): 94, 78, 62, 78, 78 (Ave: 78 ms)

These figures are the reason why I wasn't convinced with your answer. But
again, please do comment on this. It is important for me to understand why
it was designed like this when the use of limit and offset were one of the
basics in sql pagination.

~ ian

On Tue, Sep 9, 2008 at 7:40 PM, David E Jones <[hidden email]>wrote:

>
> Your assertions are incorrect, that's not how it works.
>
> I would recommend reading up a bit more on JDBC and how ResultSet data is
> handled along with cursors in the database.
>
> -David

[David E Jones]

What are the database and JDBC driver versions you are working with?

Also, the JDBC code you included does a few things differently than  
what the Entity Engine does. Have you done similar tests using the  
Entity Engine instead of plain JDBC? Also, why not set the cursor size  
and what not (as is done, optional of course, in the entityengine.xml  
file)?

-David


On Sep 9, 2008, at 7:08 AM, ian tabangay wrote:

> Thank you for your reference. I have read it and my understanding is  
> still
> the same. Sorry for being hard headed. I made a bench mark between
> pagination using ResultSet data with cursors and pagination using  
> limit and
> offset. Here's my code for both test:
>
>  /**
>  * Test for pagination via ResultSet cursor
>  **/
>  public void testResultSet() throws Exception {
>    Class.forName("org.postgresql.Driver");
>    String url = "jdbc:postgresql://127.0.0.1/ofbiz";
>    Connection con = DriverManager.getConnection(url, "postgres",
> "postgres");
>    Statement ps = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
>    int size = 100;
>    int index = 217;
>    int number = size * index;
>    long startTime = System.currentTimeMillis();
>    ResultSet rs = ps.executeQuery("SELECT * FROM product_facility");
>    if (!rs.next()) {
>      System.err.println("Nothing to return");
>      return;
>    }
>    if (!rs.absolute(number)) {
>      System.err.println("Failed to set cursor");
>      return;
>    }
>    int counter = 0;
>    do {
>      store(rs);
>      counter ++;
>    } while (size > counter && rs.next());
>    System.out.println("ENDED: " + (System.currentTimeMillis() -
> startTime));
>  }
>
>  /**
>  * Test for pagination via LIMIT and OFFSET
>  **/
>  public void testLimitOffset() throws Exception {
>    Class.forName("org.postgresql.Driver");
>    String url = "jdbc:postgresql://127.0.0.1/ofbiz";
>    Connection con = DriverManager.getConnection(url, "postgres",
> "postgres");
>    Statement ps = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
>    int size = 100;
>    int index = 217;
>    int number = size * index;
>    long startTime = System.currentTimeMillis();
>    ResultSet rs = ps.executeQuery("SELECT * FROM product_facility  
> LIMIT " +
> size + " OFFSET " + number);
>    if (!rs.next()) {
>      System.err.println("Nothing to return");
>      return;
>    }
>    do {
>      store(rs);
>    } while (rs.next());
>    System.out.println("ENDED: " + (System.currentTimeMillis() -
> startTime));
>  }
>
> Having a row size of 21897, here are my results (in ms):
> testResultSet(): 875, 922, 875, 875, 859 (Ave: 881.2 ms)
> testLimitOffset(): 94, 78, 62, 78, 78 (Ave: 78 ms)
>
> These figures are the reason why I wasn't convinced with your  
> answer. But
> again, please do comment on this. It is important for me to  
> understand why
> it was designed like this when the use of limit and offset were one  
> of the
> basics in sql pagination.
>
> ~ ian
>
> On Tue, Sep 9, 2008 at 7:40 PM, David E Jones  
> <[hidden email]>wrote:
>
>>
>> Your assertions are incorrect, that's not how it works.
>>
>> I would recommend reading up a bit more on JDBC and how ResultSet  
>> data is
>> handled along with cursors in the database.
>>
>> -David

[ian tabangay]

Here's the result of the benchmarking you've requested. Attached are the files used for this benchmark. Please comment if I did the testing the wrong way and how it should be done.

Testing procedures:
1. Entity ProductFacility was used for both tests. ProductFacility has 21897 rows at the time of the testing.
2. Both test were deployed as a service which is being called thru 'Run Service' available under Webtools
3. Services are called alternately, starting with testLimitOffset, clearing cache after each execution of a service.
4. Each service has a viewSize of 100 and viewIndex of 217.
5. Note that org.ofbiz.entity.util.EntityFindOptions (of rev 496982) and org.ofbiz.entity.datasource.GenericDAO (of rev 686591) were changed to accommodate insertions of LIMIT and OFFSET into the sql builder. I added a field 'offset' and getter-setter for it for the class org.ofbiz.entity.util.EntityFindOptions. I inserted the following lines on line 724 for the class org.ofbiz.entity.datasource.GenericDAO. Modified classes are attached for your inspection.

-- start code --
    // LIMIT
    if (findOptions.getMaxRows() > 0) {
      sqlBuffer.append(" LIMIT " + findOptions.getMaxRows());
    }
   
    // OFFSET
    if (findOptions.getOffset() > 0) {
      sqlBuffer.append(" OFFSET " + findOptions.getOffset());
    }
-- end code --

6. The database being used is postgresql 8.1
7. Platform specs: Intel CoreDuo 1.8Ghz, 2gb Ram, Windows XP ver 2002 SP 2

Test Results are as follow:
testResultSet:
797, 875, 750, 750, 765 (Ave: 787.4 ms)
generated sql: SELECT PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, MAXIMUM_STOCK, ALLOCATION FROM public.PRODUCT_FACILITY

testLimitOffset:
141, 125, 109, 93, 94 (Ave: 112.4 ms)
generated sql: SELECT PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, MAXIMUM_STOCK, ALLOCATION FROM public.PRODUCT_FACILITY LIMIT 100 OFFSET 21700

Also, why not set the cursor size and what not (as is done, optional of course, in the entityengine.xml file)?

Sorry I dont understand what you mean by this.

Again, thank you very much for your time.

~ ian

On Tue, Sep 9, 2008 at 9:17 PM, David E Jones <[hidden email]> wrote:

What are the database and JDBC driver versions you are working with?

Also, the JDBC code you included does a few things differently than what the Entity Engine does. Have you done similar tests using the Entity Engine instead of plain JDBC? Also, why not set the cursor size and what not (as is done, optional of course, in the entityengine.xml file)?

-David

[Jacques Le Roux]

This is interesting, but you did not answer about the JDBC driver used.

I'm far from a DB specialist but maybe you could read http://troels.arvin.dk/db/rdbms/
This document explains some aspects of
CURSOR http://troels.arvin.dk/db/rdbms/#select-limit
and
LIMIT-OFFSET couple http://troels.arvin.dk/db/rdbms/#select-limit-offset.

Did you have a look at the use-iterator=true(minilang) <use-iterator>(widget) option (entityListIterator) ? This uses a database cursor for you.

I suppose your lack of OFBiz researches is the reason David did not answer you, the cursor word should have make you aware ;o). Simply look for "use-iterator" in *.java,*.xml file in Eclipse...

Jacques
PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in OFBiz...
  ----- Original Message -----
  From: ian tabangay
  To: [hidden email]
  Sent: Wednesday, September 10, 2008 6:21 AM
  Subject: Re: Concerned about how ofbiz is doing paginate


  Here's the result of the benchmarking you've requested. Attached are the files used for this benchmark. Please comment if I did the testing the wrong way and how it should be done.

  Testing procedures:
  1. Entity ProductFacility was used for both tests. ProductFacility has 21897 rows at the time of the testing.
  2. Both test were deployed as a service which is being called thru 'Run Service' available under Webtools
  3. Services are called alternately, starting with testLimitOffset, clearing cache after each execution of a service.
  4. Each service has a viewSize of 100 and viewIndex of 217.
  5. Note that org.ofbiz.entity.util.EntityFindOptions (of rev 496982) and org.ofbiz.entity.datasource.GenericDAO (of rev 686591) were changed to accommodate insertions of LIMIT and OFFSET into the sql builder. I added a field 'offset' and getter-setter for it for the class org.ofbiz.entity.util.EntityFindOptions. I inserted the following lines on line 724 for the class org.ofbiz.entity.datasource.GenericDAO. Modified classes are attached for your inspection.

  -- start code --
      // LIMIT
      if (findOptions.getMaxRows() > 0) {
        sqlBuffer.append(" LIMIT " + findOptions.getMaxRows());
      }
     
      // OFFSET
      if (findOptions.getOffset() > 0) {
        sqlBuffer.append(" OFFSET " + findOptions.getOffset());
      }
  -- end code --

  6. The database being used is postgresql 8.1
  7. Platform specs: Intel CoreDuo 1.8Ghz, 2gb Ram, Windows XP ver 2002 SP 2

  Test Results are as follow:
  testResultSet:
  797, 875, 750, 750, 765 (Ave: 787.4 ms)
  generated sql: SELECT PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, MAXIMUM_STOCK, ALLOCATION FROM public.PRODUCT_FACILITY

  testLimitOffset:
  141, 125, 109, 93, 94 (Ave: 112.4 ms)
  generated sql: SELECT PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, MAXIMUM_STOCK, ALLOCATION FROM public.PRODUCT_FACILITY LIMIT 100 OFFSET 21700



    Also, why not set the cursor size and what not (as is done, optional of course, in the entityengine.xml file)?
  Sorry I dont understand what you mean by this.

  Again, thank you very much for your time.

  ~ ian



  On Tue, Sep 9, 2008 at 9:17 PM, David E Jones <[hidden email]> wrote:


    What are the database and JDBC driver versions you are working with?

    Also, the JDBC code you included does a few things differently than what the Entity Engine does. Have you done similar tests using the Entity Engine instead of plain JDBC? Also, why not set the cursor size and what not (as is done, optional of course, in the entityengine.xml file)?

    -David

[ian tabangay]

>
> This is interesting, but you did not answer about the JDBC driver used.


Sorry I want being clear. What I meant by "6. The database being used is
postgresql 8.1" was I am also using postgresql 8.1 as my database driver.

Did you have a look at the use-iterator=true(minilang)
> <use-iterator>(widget) option (entityListIterator) ? This uses a database
> cursor for you.


Yes I have. The EntityListIterator provides convenience methods to control
the cursor on the ResultSet object and retrieving the current values in it.
The use of cursor enables us to traverse through the values returned by the
PreparedStatement. Cursors enables ofbiz to go to the desired position of
the ResultSet and iterates the values from that position until the desired
size of values is retrieved. Ofbiz also uses cursors to retrieve the size of
the ResultSet by moving the current position to the last position and
getting the index of that position. As far as usage of cursors and how it
was implemented on Ofbiz is concerned, this was all I found necessary to
highlight.  Please correct me if I did miss some points.

Regarding the benchmark times, I wasn't able to include it in my past posts
but I think I should highlight the fact that the time used to get the
desired range of values (partial or all) is almost insignificant as compared
to the time to retrieve the ResultSet object from the PreparedStatement. As
for a benchmark for retrieving partial all values through cursors from a
given ResultSet (with or without limit-offset), that I haven't timed their
performance. If need be, I can produce these benchmarks if it would be
helpful.

PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in
> OFBiz...


That would be very helpful but i think LIMIT-OFFSET cannot work with the
existing paginate form generated by ofbiz. Looking at the code and doing
some preliminary patching, the form bases the number of pages from the size
of the (complete) list. Since by using LIMIT-OFFSET you're already getting
the partial list, the form would not get the size of the complete list;
pagination would therefore be incorrect.

~ Ian

On Sat, Sep 20, 2008 at 10:14 PM, Jacques Le Roux <
[hidden email]> wrote:

> This is interesting, but you did not answer about the JDBC driver used.
>
> I'm far from a DB specialist but maybe you could read
> http://troels.arvin.dk/db/rdbms/
> This document explains some aspects of
> CURSOR http://troels.arvin.dk/db/rdbms/#select-limit
> and
> LIMIT-OFFSET couple http://troels.arvin.dk/db/rdbms/#select-limit-offset.
>
> Did you have a look at the use-iterator=true(minilang)
> <use-iterator>(widget) option (entityListIterator) ? This uses a database
> cursor for you.
>
> I suppose your lack of OFBiz researches is the reason David did not answer
> you, the cursor word should have make you aware ;o). Simply look for
> "use-iterator" in *.java,*.xml file in Eclipse...
>
> Jacques
> PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in
> OFBiz...

[Jacques Le Roux]

From: "ian tabangay" <[hidden email]>
> >
>> This is interesting, but you did not answer about the JDBC driver used.
>
>
> Sorry I want being clear. What I meant by "6. The database being used is
> postgresql 8.1" was I am also using postgresql 8.1 as my database driver.

AS you can see on this page http://jdbc.postgresql.org/download.html#supported
There are some versions for this driver. I don't think it would change your concern much anyway.
David asked that because the number of rows retrieved by default depends of the driver. Of course this have an impact on
performance.
Normally you should be able to set up this number (through ResultSet.setFetchSize or in OFBiz EntityListIterator.setFetchSize or
even result-fetch-size in entityengine.xml)

> Did you have a look at the use-iterator=true(minilang)
>> <use-iterator>(widget) option (entityListIterator) ? This uses a database
>> cursor for you.
>
>
> Yes I have. The EntityListIterator provides convenience methods to control
> the cursor on the ResultSet object and retrieving the current values in it.
> The use of cursor enables us to traverse through the values returned by the
> PreparedStatement. Cursors enables ofbiz to go to the desired position of
> the ResultSet and iterates the values from that position until the desired
> size of values is retrieved. Ofbiz also uses cursors to retrieve the size of
> the ResultSet by moving the current position to the last position and
> getting the index of that position. As far as usage of cursors and how it
> was implemented on Ofbiz is concerned, this was all I found necessary to
> highlight.  Please correct me if I did miss some points.

As I explained before I'm not a DB specialist and I don't know much about JDBC.
Now, quickly looking at EntityListIterator.java I think you pretty well described how it works.
Though Ofbiz does not "retrieve the size of the ResultSet" but the size of the remaining rows not already ran through.
Also there is setFetchSize method but anyway all this is not related to your paginate concern.

> Regarding the benchmark times, I wasn't able to include it in my past posts
> but I think I should highlight the fact that the time used to get the
> desired range of values (partial or all) is almost insignificant as compared
> to the time to retrieve the ResultSet object from the PreparedStatement. As
> for a benchmark for retrieving partial all values through cursors from a
> given ResultSet (with or without limit-offset), that I haven't timed their
> performance. If need be, I can produce these benchmarks if it would be
> helpful.
>
> PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in
>> OFBiz...
>
>
> That would be very helpful but i think LIMIT-OFFSET cannot work with the
> existing paginate form generated by ofbiz. Looking at the code and doing
> some preliminary patching, the form bases the number of pages from the size
> of the (complete) list. Since by using LIMIT-OFFSET you're already getting
> the partial list, the form would not get the size of the complete list;
> pagination would therefore be incorrect.

Yes I agree, I can't see any use of LIMIT-OFFSET in OFBiz too. And this was what I tried to explain in my previous message.
All DBMS support cursors. But not all support LIMIT-OFFSET (see - in my previous link - Oracle, DB2 and MS-SQL at least).
I guess it's the reason why it's not used in OFBiz EE which must be DBMS agnostic.
What I mean by "How to use cursor or LIMIT-OFFSET couple in OFBiz" is to explain how to not use it and use a cursor strategy
instead. Of course you may use it in you preferred DBMS like Postgres or MySql but such code can't be in OFBiz. I hope to have been
more
clear on this point this time.

HTH

Jacques
PS : note about cursors in OFBiz. In some DBMS you need to set use-proxy-cursor and result-fetch-size in entityengine.xml
DatasourceInfo section. Proxy-cursor-name is by default p_cursor in OFBiz. But I can't see any uses in any of the DBMS implemented
OOTB.

> ~ Ian
>
> On Sat, Sep 20, 2008 at 10:14 PM, Jacques Le Roux <
> [hidden email]> wrote:
>
>> This is interesting, but you did not answer about the JDBC driver used.
>>
>> I'm far from a DB specialist but maybe you could read
>> http://troels.arvin.dk/db/rdbms/
>> This document explains some aspects of
>> CURSOR http://troels.arvin.dk/db/rdbms/#select-limit
>> and
>> LIMIT-OFFSET couple http://troels.arvin.dk/db/rdbms/#select-limit-offset.
>>
>> Did you have a look at the use-iterator=true(minilang)
>> <use-iterator>(widget) option (entityListIterator) ? This uses a database
>> cursor for you.
>>
>> I suppose your lack of OFBiz researches is the reason David did not answer
>> you, the cursor word should have make you aware ;o). Simply look for
>> "use-iterator" in *.java,*.xml file in Eclipse...
>>
>> Jacques
>> PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in
>> OFBiz...
>

[Jacques Le Roux]

From: "Jacques Le Roux" <[hidden email]>

> From: "ian tabangay" <[hidden email]>
>> >
>>> This is interesting, but you did not answer about the JDBC driver used.
>>
>>
>> Sorry I want being clear. What I meant by "6. The database being used is
>> postgresql 8.1" was I am also using postgresql 8.1 as my database driver.
>
> AS you can see on this page http://jdbc.postgresql.org/download.html#supported
> There are some versions for this driver. I don't think it would change your concern much anyway.
> David asked that because the number of rows retrieved by default depends of the driver. Of course this have an impact on
> performance.
> Normally you should be able to set up this number (through ResultSet.setFetchSize or in OFBiz EntityListIterator.setFetchSize or
> even result-fetch-size in entityengine.xml)
>
>
>> Did you have a look at the use-iterator=true(minilang)
>>> <use-iterator>(widget) option (entityListIterator) ? This uses a database
>>> cursor for you.
>>
>>
>> Yes I have. The EntityListIterator provides convenience methods to control
>> the cursor on the ResultSet object and retrieving the current values in it.
>> The use of cursor enables us to traverse through the values returned by the
>> PreparedStatement. Cursors enables ofbiz to go to the desired position of
>> the ResultSet and iterates the values from that position until the desired
>> size of values is retrieved. Ofbiz also uses cursors to retrieve the size of
>> the ResultSet by moving the current position to the last position and
>> getting the index of that position. As far as usage of cursors and how it
>> was implemented on Ofbiz is concerned, this was all I found necessary to
>> highlight.  Please correct me if I did miss some points.
>
> As I explained before I'm not a DB specialist and I don't know much about JDBC.
> Now, quickly looking at EntityListIterator.java I think you pretty well described how it works.
> Though Ofbiz does not "retrieve the size of the ResultSet" but the size of the remaining rows not already ran through.
> Also there is setFetchSize method but anyway all this is not related to your paginate concern.
>
>> Regarding the benchmark times, I wasn't able to include it in my past posts
>> but I think I should highlight the fact that the time used to get the
>> desired range of values (partial or all) is almost insignificant as compared
>> to the time to retrieve the ResultSet object from the PreparedStatement. As
>> for a benchmark for retrieving partial all values through cursors from a
>> given ResultSet (with or without limit-offset), that I haven't timed their
>> performance. If need be, I can produce these benchmarks if it would be
>> helpful.
>>
>> PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in
>>> OFBiz...
>>
>>
>> That would be very helpful but i think LIMIT-OFFSET cannot work with the
>> existing paginate form generated by ofbiz. Looking at the code and doing
>> some preliminary patching, the form bases the number of pages from the size
>> of the (complete) list. Since by using LIMIT-OFFSET you're already getting
>> the partial list, the form would not get the size of the complete list;
>> pagination would therefore be incorrect.
>
> Yes I agree, I can't see any use of LIMIT-OFFSET in OFBiz too. And this was what I tried to explain in my previous message.
> All DBMS support cursors. But not all support LIMIT-OFFSET (see - in my previous link - Oracle, DB2 and MS-SQL at least).
> I guess it's the reason why it's not used in OFBiz EE which must be DBMS agnostic.
> What I mean by "How to use cursor or LIMIT-OFFSET couple in OFBiz" is to explain how to not use it and use a cursor strategy
> instead. Of course you may use it in you preferred DBMS like Postgres or MySql but such code can't be in OFBiz. I hope to have
> been more
> clear on this point this time.

Of course this does not mean that we could not have specific faster mechanismes for PostGres and MySql. Like anything in OFBiz it's
open... This is my opininon at least...
So you may continue discussing this and maybe you could provide a patch later...

Jacques

> HTH
>
> Jacques
> PS : note about cursors in OFBiz. In some DBMS you need to set use-proxy-cursor and result-fetch-size in entityengine.xml
> DatasourceInfo section. Proxy-cursor-name is by default p_cursor in OFBiz. But I can't see any uses in any of the DBMS implemented
> OOTB.
>> ~ Ian
>>
>> On Sat, Sep 20, 2008 at 10:14 PM, Jacques Le Roux <
>> [hidden email]> wrote:
>>
>>> This is interesting, but you did not answer about the JDBC driver used.
>>>
>>> I'm far from a DB specialist but maybe you could read
>>> http://troels.arvin.dk/db/rdbms/
>>> This document explains some aspects of
>>> CURSOR http://troels.arvin.dk/db/rdbms/#select-limit
>>> and
>>> LIMIT-OFFSET couple http://troels.arvin.dk/db/rdbms/#select-limit-offset.
>>>
>>> Did you have a look at the use-iterator=true(minilang)
>>> <use-iterator>(widget) option (entityListIterator) ? This uses a database
>>> cursor for you.
>>>
>>> I suppose your lack of OFBiz researches is the reason David did not answer
>>> you, the cursor word should have make you aware ;o). Simply look for
>>> "use-iterator" in *.java,*.xml file in Eclipse...
>>>
>>> Jacques
>>> PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in
>>> OFBiz...
>>
>