You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Michael Gentry <bl...@gmail.com> on 2020/03/14 14:31:25 UTC
MySQL + Iterated/Streamed Query
I'm trying to do an iterated query (using the 4.1 RC) against MySQL and it
appears that the MySQL JDBC driver loads everything into memory first
instead of streaming results. This results in memory-related exceptions
for larger results.
From searching, it appears that the MySQL JDBC driver needs the following
hint to stream results:
statement =
connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(Integer.MIN_VALUE);
Is there an easy way to get the iterated query to provide that hint when
getting a connection? I've not found a way yet. Best I've found so far is
to swap out a whole slew of classes to change the DataSource that's
actually returned.
Thanks,
mrg
Re: MySQL + Iterated/Streamed Query
Posted by Michael Gentry <bl...@gmail.com>.
That worked perfectly. Used a much higher fetch size for performance,
though. The memory usage held steady and was able to process all 30M
records.
Thanks!
On Tue, Mar 17, 2020 at 10:50 AM Andrus Adamchik <an...@objectstyle.org>
wrote:
> This translates to java.sql.Statement#setFetchSize(), i.e. the number of
> records to retrieve at once from the DB, so it has to be positive. E.g.
> 1000 or something.
>
> From java.sql.Statement#setFetchSize javadocs:
>
> "Gives the JDBC driver a hint as to the number of rows that should be
> fetched from the database when more rows are needed for ResultSet objects
> generated by this Statement. If the value specified is zero, then the hint
> is ignored. The default value is zero."
>
> Andrus
>
>
>
> > On Mar 17, 2020, at 4:54 PM, Michael Gentry <bl...@gmail.com> wrote:
> >
> > Do you use Integer.MIN_VALUE for the ObjectSelect.statementFetchSize()?
> >
> >
> > On Tue, Mar 17, 2020 at 2:14 AM Andrus Adamchik <an...@objectstyle.org>
> > wrote:
> >
> >> Yeah, had to deal with this before. While there's no API to directly
> >> access the Statement (I think we should introduce it just in case),
> there
> >> is an alternative: add "useCursorFetch=true" URL parameter and use
> >> "ObjectSelect.statementFetchSize(..)".
> >>
> >> Andrus
> >>
> >>
> >>> On Mar 14, 2020, at 5:31 PM, Michael Gentry <bl...@gmail.com>
> wrote:
> >>>
> >>> I'm trying to do an iterated query (using the 4.1 RC) against MySQL and
> >> it
> >>> appears that the MySQL JDBC driver loads everything into memory first
> >>> instead of streaming results. This results in memory-related
> exceptions
> >>> for larger results.
> >>>
> >>> From searching, it appears that the MySQL JDBC driver needs the
> following
> >>> hint to stream results:
> >>>
> >>> statement =
> >>> connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
> >>> java.sql.ResultSet.CONCUR_READ_ONLY);
> >>> statement.setFetchSize(Integer.MIN_VALUE);
> >>>
> >>> Is there an easy way to get the iterated query to provide that hint
> when
> >>> getting a connection? I've not found a way yet. Best I've found so
> far
> >> is
> >>> to swap out a whole slew of classes to change the DataSource that's
> >>> actually returned.
> >>>
> >>> Thanks,
> >>>
> >>> mrg
> >>
> >>
>
>
Re: MySQL + Iterated/Streamed Query
Posted by Andrus Adamchik <an...@objectstyle.org>.
This translates to java.sql.Statement#setFetchSize(), i.e. the number of records to retrieve at once from the DB, so it has to be positive. E.g. 1000 or something.
From java.sql.Statement#setFetchSize javadocs:
"Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero."
Andrus
> On Mar 17, 2020, at 4:54 PM, Michael Gentry <bl...@gmail.com> wrote:
>
> Do you use Integer.MIN_VALUE for the ObjectSelect.statementFetchSize()?
>
>
> On Tue, Mar 17, 2020 at 2:14 AM Andrus Adamchik <an...@objectstyle.org>
> wrote:
>
>> Yeah, had to deal with this before. While there's no API to directly
>> access the Statement (I think we should introduce it just in case), there
>> is an alternative: add "useCursorFetch=true" URL parameter and use
>> "ObjectSelect.statementFetchSize(..)".
>>
>> Andrus
>>
>>
>>> On Mar 14, 2020, at 5:31 PM, Michael Gentry <bl...@gmail.com> wrote:
>>>
>>> I'm trying to do an iterated query (using the 4.1 RC) against MySQL and
>> it
>>> appears that the MySQL JDBC driver loads everything into memory first
>>> instead of streaming results. This results in memory-related exceptions
>>> for larger results.
>>>
>>> From searching, it appears that the MySQL JDBC driver needs the following
>>> hint to stream results:
>>>
>>> statement =
>>> connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
>>> java.sql.ResultSet.CONCUR_READ_ONLY);
>>> statement.setFetchSize(Integer.MIN_VALUE);
>>>
>>> Is there an easy way to get the iterated query to provide that hint when
>>> getting a connection? I've not found a way yet. Best I've found so far
>> is
>>> to swap out a whole slew of classes to change the DataSource that's
>>> actually returned.
>>>
>>> Thanks,
>>>
>>> mrg
>>
>>
Re: MySQL + Iterated/Streamed Query
Posted by Michael Gentry <bl...@gmail.com>.
Do you use Integer.MIN_VALUE for the ObjectSelect.statementFetchSize()?
On Tue, Mar 17, 2020 at 2:14 AM Andrus Adamchik <an...@objectstyle.org>
wrote:
> Yeah, had to deal with this before. While there's no API to directly
> access the Statement (I think we should introduce it just in case), there
> is an alternative: add "useCursorFetch=true" URL parameter and use
> "ObjectSelect.statementFetchSize(..)".
>
> Andrus
>
>
> > On Mar 14, 2020, at 5:31 PM, Michael Gentry <bl...@gmail.com> wrote:
> >
> > I'm trying to do an iterated query (using the 4.1 RC) against MySQL and
> it
> > appears that the MySQL JDBC driver loads everything into memory first
> > instead of streaming results. This results in memory-related exceptions
> > for larger results.
> >
> > From searching, it appears that the MySQL JDBC driver needs the following
> > hint to stream results:
> >
> > statement =
> > connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
> > java.sql.ResultSet.CONCUR_READ_ONLY);
> > statement.setFetchSize(Integer.MIN_VALUE);
> >
> > Is there an easy way to get the iterated query to provide that hint when
> > getting a connection? I've not found a way yet. Best I've found so far
> is
> > to swap out a whole slew of classes to change the DataSource that's
> > actually returned.
> >
> > Thanks,
> >
> > mrg
>
>
Re: MySQL + Iterated/Streamed Query
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah, had to deal with this before. While there's no API to directly access the Statement (I think we should introduce it just in case), there is an alternative: add "useCursorFetch=true" URL parameter and use "ObjectSelect.statementFetchSize(..)".
Andrus
> On Mar 14, 2020, at 5:31 PM, Michael Gentry <bl...@gmail.com> wrote:
>
> I'm trying to do an iterated query (using the 4.1 RC) against MySQL and it
> appears that the MySQL JDBC driver loads everything into memory first
> instead of streaming results. This results in memory-related exceptions
> for larger results.
>
> From searching, it appears that the MySQL JDBC driver needs the following
> hint to stream results:
>
> statement =
> connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
> java.sql.ResultSet.CONCUR_READ_ONLY);
> statement.setFetchSize(Integer.MIN_VALUE);
>
> Is there an easy way to get the iterated query to provide that hint when
> getting a connection? I've not found a way yet. Best I've found so far is
> to swap out a whole slew of classes to change the DataSource that's
> actually returned.
>
> Thanks,
>
> mrg