Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method

[Martin Kalén <mk...@apache.org>]

Andreas Bohnert wrote:
> Postgres (for example) don't use cursors on default. The PG jdbc driver 
> loads the entire ResultSet all at once and keeps it in memory! So, even 
> if you do a getIteratorByQuery the memory load on a large resultset is 
> huge!
> 
> You can get around this, if you set the fetchsize on the jdbc statement
> for example:
>            stmt = con.createStatement();
>            stmt.setFetchSize(1);
>            ResultSet rs = stmt.executeQuery(sql);
> 
> This actually forces the jdbc driver to use a cursor and browse the 
> resultset one by one.
> 
> There is no possibility to do this with ojb's getIteratorByQuery! Or is 
> there?

Have a look at http://issues.apache.org/jira/browse/OJB-31

This is now fixed (in beta state) in the OJB_1_0_RELEASE branch,
where you can set a global max cap hint through the connection-pool
descriptor in your repository.

All Iterator objects should also set fetchSize hints of 1 internally
in OJB -- so please give it a try without setting any hint at first,
and see if you get better results automagically.

Regards,
  Martin

P.S. This will also be merged with the trunk (v1.1 pre) eventually.
I will re-open the issue in JIRA and set the status there once it
has been done so you can add yourself as observer of OJB-31 in JIRA
to get mail notification on updates.


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method

[Andreas Bohnert <oj...@weberhofer.at>]

> "Bleeding edge" development for OJB v1.1+ is in CVS HEAD, but should also
> not be broken. OJB_1_0_RELEASE is maintenance for the last release OJB
> and is thus pre-1.0.4 at the moment.
>
> As a rule of thumb, OJB_1_0_RELEASE branch is currently as stable as 
> v1.0.3
> plus some bugfixes and new minor improvements like fetchSize hints.
>
> I would suggest that you run with a CVS snapshot from the branch instead
> of patching 1.0.3 with the changes.
>
> Regards,
>  Martin
>
ah, ok. I will do that.
thank you!

regards,
andreas


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method

[Martin Kalén <mk...@apache.org>]

Andreas Bohnert wrote:
> I have just run several tests with different hints. It works very well, 
> however, if I don't set a hint, no cursors are used, even with 
> getIteratorByQuery!

Good to hear that it's working well!

> But maybe that's not too bad. If you enable cursors on iterators by 
> default, MySql users could run into troubles.

I will put this on the TODO list as it was my intention that
getIteratorByQuery should set fetchSize hints of 1 automatically.

If that's not working it should either be fixed or backed out
(which might be an option considering your MySQL info).

> just a question: can I apply your patch easily to the 1.0.3 sources? I 
> don't know how stable the current OJB_1_0_RELEASE branch is.

"Bleeding edge" development for OJB v1.1+ is in CVS HEAD, but should also
not be broken. OJB_1_0_RELEASE is maintenance for the last release OJB
and is thus pre-1.0.4 at the moment.

As a rule of thumb, OJB_1_0_RELEASE branch is currently as stable as v1.0.3
plus some bugfixes and new minor improvements like fetchSize hints.

I would suggest that you run with a CVS snapshot from the branch instead
of patching 1.0.3 with the changes.

Regards,
  Martin


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method

[Andreas Bohnert <oj...@weberhofer.at>]

hi martin,

I have just run several tests with different hints. It works very well, 
however, if I don't set a hint, no cursors are used, even with 
getIteratorByQuery!

But maybe that's not too bad. If you enable cursors on iterators by 
default, MySql users could run into troubles.
I just found this on the mysql side:

/There are some caveats with this /(using a cursor)/ approach. You will 
have to read all of the rows in the result set (or close it) before you 
can issue any other queries on the connection, or an exception will be 
thrown. Also, any tables referenced by the query that created the 
streaming result will be locked until all of the results have been read 
or the connection closed.
*http://dev.mysql.com/doc/connector/j/en/cj-implementation-notes.html*
/
just a question: can I apply your patch easily to the 1.0.3 sources? I 
don't know how stable the current OJB_1_0_RELEASE branch is.

regards,
andreas



Martin Kalén wrote:
> Have a look at http://issues.apache.org/jira/browse/OJB-31
>
> This is now fixed (in beta state) in the OJB_1_0_RELEASE branch,
> where you can set a global max cap hint through the connection-pool
> descriptor in your repository.
>
> All Iterator objects should also set fetchSize hints of 1 internally
> in OJB -- so please give it a try without setting any hint at first,
> and see if you get better results automagically.
>
> Regards,
>  Martin
>
> P.S. This will also be merged with the trunk (v1.1 pre) eventually.
> I will re-open the issue in JIRA and set the status there once it
> has been done so you can add yourself as observer of OJB-31 in JIRA
> to get mail notification on updates.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>