You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Ilya Lazarev <gl...@gmail.com> on 2009/12/04 17:41:52 UTC
Selecting with an offset / paging
Hello,
I have a webapp that accesses an Oralce DB to retrieve records and do paging
over the entire resultset (an expression is used that spans multiple
tables). Recently when the number of results in total adds up to over 30000,
the performance of the select query has dropped to unusable levels (30+
seconds per query). The webapp never returns all 30000 rows so it's OK for
the time being, but it certainly needs to be addressed.I wanted to do a
select with a *"where rownum > a and rownum < b" *type query, but so far
have not seen a way to do that. Does anyone have any tips how to do this? My
other option is to rewrite the retrieval using SQLTemplate and calculate the
dynamic sql based on parameters by hand, correct? Will the resultset contain
objects with their relationships that way (ie.
((TableA)result.get(0)).getToTableB() ?) or flat data rows?
Thanks!
Re: Selecting with an offset / paging
Posted by Andrus Adamchik <an...@objectstyle.org>.
Also Cayenne 3.0 supports offsets for all types of queries:
http://cayenne.apache.org/doc/api/org/apache/cayenne/query/SelectQuery.html#setFetchOffset(int)
Andrus
On Dec 4, 2009, at 7:12 PM, Michael Gentry wrote:
> Hi Ilya,
>
> Are you at least using a paginated query instead of a regular query?
>
> http://cayenne.apache.org/doc12/paginated-queries.html
>
> mrg
>
>
> On Fri, Dec 4, 2009 at 11:42 AM, Ilya Lazarev <gl...@gmail.com>
> wrote:
>> using 1.2, forgot to mention
>>
>> On Fri, Dec 4, 2009 at 5:41 PM, Ilya Lazarev <gl...@gmail.com>
>> wrote:
>>
>>> Hello,
>>>
>>> I have a webapp that accesses an Oralce DB to retrieve records and
>>> do
>>> paging over the entire resultset (an expression is used that spans
>>> multiple
>>> tables). Recently when the number of results in total adds up to
>>> over 30000,
>>> the performance of the select query has dropped to unusable levels
>>> (30+
>>> seconds per query). The webapp never returns all 30000 rows so
>>> it's OK for
>>> the time being, but it certainly needs to be addressed.I wanted to
>>> do a
>>> select with a *"where rownum > a and rownum < b" *type query, but
>>> so far
>>> have not seen a way to do that. Does anyone have any tips how to
>>> do this? My
>>> other option is to rewrite the retrieval using SQLTemplate and
>>> calculate the
>>> dynamic sql based on parameters by hand, correct? Will the
>>> resultset contain
>>> objects with their relationships that way (ie.
>>> ((TableA)result.get(0)).getToTableB() ?) or flat data rows?
>>>
>>> Thanks!
>>>
>>
>
Re: Selecting with an offset / paging
Posted by Ilya Lazarev <gl...@gmail.com>.
Wasn't using paginated queries before. That's something worth trying,
thanks!
On Fri, Dec 4, 2009 at 6:12 PM, Michael Gentry <mg...@masslight.net>wrote:
> Hi Ilya,
>
> Are you at least using a paginated query instead of a regular query?
>
> http://cayenne.apache.org/doc12/paginated-queries.html
>
> mrg
>
>
> On Fri, Dec 4, 2009 at 11:42 AM, Ilya Lazarev <gl...@gmail.com> wrote:
> > using 1.2, forgot to mention
> >
> > On Fri, Dec 4, 2009 at 5:41 PM, Ilya Lazarev <gl...@gmail.com> wrote:
> >
> >> Hello,
> >>
> >> I have a webapp that accesses an Oralce DB to retrieve records and do
> >> paging over the entire resultset (an expression is used that spans
> multiple
> >> tables). Recently when the number of results in total adds up to over
> 30000,
> >> the performance of the select query has dropped to unusable levels (30+
> >> seconds per query). The webapp never returns all 30000 rows so it's OK
> for
> >> the time being, but it certainly needs to be addressed.I wanted to do a
> >> select with a *"where rownum > a and rownum < b" *type query, but so far
> >> have not seen a way to do that. Does anyone have any tips how to do
> this? My
> >> other option is to rewrite the retrieval using SQLTemplate and calculate
> the
> >> dynamic sql based on parameters by hand, correct? Will the resultset
> contain
> >> objects with their relationships that way (ie.
> >> ((TableA)result.get(0)).getToTableB() ?) or flat data rows?
> >>
> >> Thanks!
> >>
> >
>
Re: Selecting with an offset / paging
Posted by Michael Gentry <mg...@masslight.net>.
Hi Ilya,
Are you at least using a paginated query instead of a regular query?
http://cayenne.apache.org/doc12/paginated-queries.html
mrg
On Fri, Dec 4, 2009 at 11:42 AM, Ilya Lazarev <gl...@gmail.com> wrote:
> using 1.2, forgot to mention
>
> On Fri, Dec 4, 2009 at 5:41 PM, Ilya Lazarev <gl...@gmail.com> wrote:
>
>> Hello,
>>
>> I have a webapp that accesses an Oralce DB to retrieve records and do
>> paging over the entire resultset (an expression is used that spans multiple
>> tables). Recently when the number of results in total adds up to over 30000,
>> the performance of the select query has dropped to unusable levels (30+
>> seconds per query). The webapp never returns all 30000 rows so it's OK for
>> the time being, but it certainly needs to be addressed.I wanted to do a
>> select with a *"where rownum > a and rownum < b" *type query, but so far
>> have not seen a way to do that. Does anyone have any tips how to do this? My
>> other option is to rewrite the retrieval using SQLTemplate and calculate the
>> dynamic sql based on parameters by hand, correct? Will the resultset contain
>> objects with their relationships that way (ie.
>> ((TableA)result.get(0)).getToTableB() ?) or flat data rows?
>>
>> Thanks!
>>
>
Re: Selecting with an offset / paging
Posted by Ilya Lazarev <gl...@gmail.com>.
using 1.2, forgot to mention
On Fri, Dec 4, 2009 at 5:41 PM, Ilya Lazarev <gl...@gmail.com> wrote:
> Hello,
>
> I have a webapp that accesses an Oralce DB to retrieve records and do
> paging over the entire resultset (an expression is used that spans multiple
> tables). Recently when the number of results in total adds up to over 30000,
> the performance of the select query has dropped to unusable levels (30+
> seconds per query). The webapp never returns all 30000 rows so it's OK for
> the time being, but it certainly needs to be addressed.I wanted to do a
> select with a *"where rownum > a and rownum < b" *type query, but so far
> have not seen a way to do that. Does anyone have any tips how to do this? My
> other option is to rewrite the retrieval using SQLTemplate and calculate the
> dynamic sql based on parameters by hand, correct? Will the resultset contain
> objects with their relationships that way (ie.
> ((TableA)result.get(0)).getToTableB() ?) or flat data rows?
>
> Thanks!
>