From: "Jacques Le Roux" <[EMAIL PROTECTED]>
From: "ian tabangay" <[EMAIL PROTECTED]>
>
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 <
[EMAIL PROTECTED]> 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...
