Timeout for fetching data?

[Tobias Trelle <to...@codecentric.de>]

Is there a (n Open)JPA timeout for fetching large data sets?

Let's say I have a query that selects all rows from a table holding 2.4
million rows. The SELECT itself takes only about 30ms, but fetching the data
over the wire takes about 2 minutes in our test environment.

I did some tests and learned that the standard JPA query timeout configured
by a query hint ...

   query.setHint("javax.persistence.query.timeout", new Integer(3000));

... (which calls Statement#setQueryTimeout on the JDCB layer) is a timeout
that applies to the processing time to compute the set of rows to be
fetched. This is very fast for simple SELECTs. The time needed for fetching
the data is not covered by this timeout.

We want to have a timeout for the overall execution of a query (computing
the data set + fetching the data over the wire). Is there some configuration
for the latter? Will JTA provide something in that direction?

We are using OpenJPA 2.1.1 w/ DB2 V9 and a non-JTA datasource.

TIA,
Tobias

--
View this message in context: http://openjpa.208410.n2.nabble.com/Timeout-for-fetching-data-tp6954428p6954428.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Timeout for fetching data?

[Kevin Sutter <kw...@gmail.com>]

Hi Tobias,
Sorry, I'm not aware of anything in OpenJPA that would provide this type of
"processing timeout" capability.  (I always say "not aware" since there may
be some hidden gem in this open-source project that hasn't been surfaced
until the request is made...).  The only other alternative that I thought
of was setting a transaction timeout, but you stated that you are using
non-jta-data-source, so you probably don't have access to a JTA transaction
for this timeout setting.  The idea there would be to enclose your SELECT
processing within a JTA transaction that has a timeout associated with it.
The goal is to get to the JTA commit/rollback before the transaction times
out.  Are you executing within an app server that could provide this type
of functionality?

Other than that, it sounds like a nice feature request, if you care to open
a JIRA.

Thanks,
Kevin

On Wed, Nov 2, 2011 at 3:37 AM, Tobias Trelle
<to...@codecentric.de>wrote:

> Is there a (n Open)JPA timeout for fetching large data sets?
>
> Let's say I have a query that selects all rows from a table holding 2.4
> million rows. The SELECT itself takes only about 30ms, but fetching the
> data
> over the wire takes about 2 minutes in our test environment.
>
> I did some tests and learned that the standard JPA query timeout configured
> by a query hint ...
>
>   query.setHint("javax.persistence.query.timeout", new Integer(3000));
>
> ... (which calls Statement#setQueryTimeout on the JDCB layer) is a timeout
> that applies to the processing time to compute the set of rows to be
> fetched. This is very fast for simple SELECTs. The time needed for fetching
> the data is not covered by this timeout.
>
> We want to have a timeout for the overall execution of a query (computing
> the data set + fetching the data over the wire). Is there some
> configuration
> for the latter? Will JTA provide something in that direction?
>
> We are using OpenJPA 2.1.1 w/ DB2 V9 and a non-JTA datasource.
>
> TIA,
> Tobias
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Timeout-for-fetching-data-tp6954428p6954428.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>