What to do with queries that return large datasets?

[Kevin Meyer - KMZ <ke...@kmz.co.za>]

Hi guys,

At the moment, the SQL object store defaults to instantiating no more 
than 100 instances of any POJO (e.g. allInstances(Member.class) will 
only return up to 100 Members).

I have added the ablity to increase this maximum via the properties file 
(e.g. use: "isis.persistor.sql.maxinstances=1000").

What's the general rule here? 

Real world example: I want to generate an invoice for all NORMAL 
members for their dues this year. From a pure design point of view, I 
therefore want to use a pattern query to fetch all NORMAL members, 
then with each member, create an invoice. Neither the list of members 
nor the created invoices is ever presented to the UI.

As it stands right now, I need to increase the maxinstances value.

I don't really want to fire off a custom SQL query to create the invoice 
entries manually...

Regards,
Kevin

Re: What to do with queries that return large datasets?

[Mike Burton <mi...@mycosystems.co.uk>]

Hi Kevin,

Yes personally I wouldn't depend on fetching an entire resultset, just in case it is huge.
So yes I would alter Isis to cater for paging "where possible" 

Like you say some SQL servers may not support paging. I dont know how big a problem this would be in practice, I'd be inclined to ignore it for now / flag it for a future solution such as faking via primary key etc.

Best Regards

Mike Burton
Myco Systems Ltd
01753 893390
17 Criss Grove, Gerrards Cross, BUCKS, SL9 9HG
Registered in England No 1832705

NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.




On 23 Mar 2012, at 17:21, Kevin Meyer - KMZ wrote:

> Hi Mike,
> 
> Indeed - But paging is not currently supported by the Isis ObjectStore 
> interface..  It is something that has troubled me in the past, and I've 
> considered adding versions of the "allInstances" and "allMatches" 
> methods that include paging - but there's a whole different problem - 
> getting paging to work across all the supported SQL servers. And 
> that's a problem for isis-dev@iao.
> 
> But does this mean that you *would not* encourage an existing user 
> domain problem (that uses the Isis objectstores) to rely on fetching all 
> instances, as the object store currently stands? That you recommend 
> altering the Isis framework?
> 
> Regards,
> Kevin
> 
> 
> 
> 
> On 23 Mar 2012 at 16:04, Mike Burton wrote:
> 
>> Hi Kevin,
>> 
>> I have tended to use the paging mechanisms of HQL and JBoss Seam's
>> Application Framework, so maybe use a similar approach? I think they
>> have some kind of Proxy to fetch "a pageful" of rows. 
>> 
>> Best Regards
>> 
>> Mike Burton
>> 
> 

Re: What to do with queries that return large datasets?

[Kevin Meyer - KMZ <ke...@kmz.co.za>]

Hi Mike,

Indeed - But paging is not currently supported by the Isis ObjectStore 
interface..  It is something that has troubled me in the past, and I've 
considered adding versions of the "allInstances" and "allMatches" 
methods that include paging - but there's a whole different problem - 
getting paging to work across all the supported SQL servers. And 
that's a problem for isis-dev@iao.

But does this mean that you *would not* encourage an existing user 
domain problem (that uses the Isis objectstores) to rely on fetching all 
instances, as the object store currently stands? That you recommend 
altering the Isis framework?

Regards,
Kevin




On 23 Mar 2012 at 16:04, Mike Burton wrote:

> Hi Kevin,
> 
> I have tended to use the paging mechanisms of HQL and JBoss Seam's
> Application Framework, so maybe use a similar approach? I think they
> have some kind of Proxy to fetch "a pageful" of rows. 
> 
> Best Regards
> 
> Mike Burton
> 

Re: What to do with queries that return large datasets?

[Mike Burton <mi...@mycosystems.co.uk>]

Hi Kevin,

I have tended to use the paging mechanisms of HQL and JBoss Seam's Application Framework, so maybe use a similar approach?
I think they have some kind of Proxy to fetch "a pageful" of rows.

Best Regards

Mike Burton




On 23 Mar 2012, at 13:59, Kevin Meyer - KMZ wrote:

> Hi guys,
> 
> At the moment, the SQL object store defaults to instantiating no more 
> than 100 instances of any POJO (e.g. allInstances(Member.class) will 
> only return up to 100 Members).
> 
> I have added the ablity to increase this maximum via the properties file 
> (e.g. use: "isis.persistor.sql.maxinstances=1000").
> 
> What's the general rule here? 
> 
> Real world example: I want to generate an invoice for all NORMAL 
> members for their dues this year. From a pure design point of view, I 
> therefore want to use a pattern query to fetch all NORMAL members, 
> then with each member, create an invoice. Neither the list of members 
> nor the created invoices is ever presented to the UI.
> 
> As it stands right now, I need to increase the maxinstances value.
> 
> I don't really want to fire off a custom SQL query to create the invoice 
> entries manually...
> 
> Regards,
> Kevin
> 

Re: What to do with queries that return large datasets?

[Kevin Meyer - KMZ <ke...@kmz.co.za>]

On 25 Mar 2012 at 9:48, Mike Burton wrote:

> My reason for worrying about memory... usage was previous experience
> in other environments, and "gut feel". If Dan says its OK to return
> "all" to internal collections used by the domain, then I'm happy to
> go with his opinion. 

Memory was also my concern - but I don't know when this becomes an 
issue: 1,000 records? 10,000 records? 100,000 records?

> I take it the caller gets informed if
> isis.persistor.sql.maxinstances was exceeded, and that the remaining
> instances are not just "silently ignored"? 

Hmm - well, umm, no, actually - at the moment it silently stops 
instantiating the remaining domain objects.

Let's see if I can insert a warning... 


--
Kevin Meyer, PhD, Pr.Sci.Nat
KMZ		P.O. Box 9822, Sharon Park, South Africa.
Tel: +27 11 363 2001	Cell: +27 83 346 3045

Re: What to do with queries that return large datasets?

[Mike Burton <mi...@mycosystems.co.uk>]

Hi Kevin,

My reason for worrying about memory... usage was previous experience in other environments, and "gut feel". If Dan says its OK to return "all" to internal collections used by the domain, then I'm happy to go with his opinion. 

I take it the caller gets informed if isis.persistor.sql.maxinstances was exceeded, and that the remaining instances are not just "silently ignored"?

Best Regards

Mike Burton
(Sent from my iPhone)



On 24 Mar 2012, at 15:26, "Kevin Meyer - KMZ" <ke...@kmz.co.za> wrote:

> Mike,
> 
> You were the other person to express an opinion here - care to 
> comment again, given Dan's responses?
> 
> Regards,
> Kevin
> 
>>> Yes, I am looking at the latter - internal collections used by the domain,
>>> and not for rendering to the UI.
>>> 
>>> Just to confirm - it is your opinion that it is OK for the SQL OS to return
>>> "all" (or at least the limit imposed by "isis.persistor.sql.maxinstances")?
>>> 
>> 
>> Yes, it's ok.
>> 
>>> I was wondering about memory usage and the like, but perhaps with
>>> no real justification?
>>> 
>> 
>> I wouldn't worry about it.  If the domain object really needs to process
>> all the objects, then that's what it needs to do.
>> 
> 

Re: What to do with queries that return large datasets?

[Kevin Meyer - KMZ <ke...@kmz.co.za>]

Mike,

You were the other person to express an opinion here - care to 
comment again, given Dan's responses?

Regards,
Kevin

> > Yes, I am looking at the latter - internal collections used by the domain,
> > and not for rendering to the UI.
> >
> > Just to confirm - it is your opinion that it is OK for the SQL OS to return
> > "all" (or at least the limit imposed by "isis.persistor.sql.maxinstances")?
> >
> 
> Yes, it's ok.
> 
> > I was wondering about memory usage and the like, but perhaps with
> > no real justification?
> >
> 
> I wouldn't worry about it.  If the domain object really needs to process
> all the objects, then that's what it needs to do.
> 

Re: What to do with queries that return large datasets?

[Dan Haywood <da...@haywood-associates.co.uk>]

On 24 March 2012 14:47, Kevin Meyer - KMZ <ke...@kmz.co.za> wrote:

>
> Yes, I am looking at the latter - internal collections used by the domain,
> and not for rendering to the UI.
>
> Just to confirm - it is your opinion that it is OK for the SQL OS to return
> "all" (or at least the limit imposed by "isis.persistor.sql.maxinstances")?
>

Yes, it's ok.



>
> I was wondering about memory usage and the like, but perhaps with
> no real justification?
>

I wouldn't worry about it.  If the domain object really needs to process
all the objects, then that's what it needs to do.



>
> Yes, sure - let me create a JIRA ticket for extending the general OS
> API to support paging. This also has issues - a custom Paging module
> will be needed, and chosen at runtime, to build the associated paging
> syntax into the SQL query. But that's a discussion for isis-dev@iao.
>
OK




>
> Regards,
> Kevin
>
>
> On 24 Mar 2012 at 14:29, Dan Haywood wrote:
>
> >
> > You have the latter case, and my feeling is that the SQL OS shouldn't
> > really limit the rows.  But since the SQL OS can't really "tell" how it
> is
> > being called, it probably implies that it should always return all the
> > rows, and then (for the former case) that the viewer layer should take
> > responsibility for paging.  It is a presentation concern, after all.
> >
>
>
>

Re: What to do with queries that return large datasets?

[Kevin Meyer - KMZ <ke...@kmz.co.za>]

Hi Dan,

> Hi Kevin, First off, I think we should distinguish between results
> that are rendered in the UI, vs those that are only ever returned to
> a domain object. 


Yes, I am looking at the latter - internal collections used by the domain, 
and not for rendering to the UI.

Just to confirm - it is your opinion that it is OK for the SQL OS to return 
"all" (or at least the limit imposed by "isis.persistor.sql.maxinstances")?

I was wondering about memory usage and the like, but perhaps with 
no real justification?

Yes, sure - let me create a JIRA ticket for extending the general OS 
API to support paging. This also has issues - a custom Paging module 
will be needed, and chosen at runtime, to build the associated paging 
syntax into the SQL query. But that's a discussion for isis-dev@iao.

Regards,
Kevin


On 24 Mar 2012 at 14:29, Dan Haywood wrote:

> 
> You have the latter case, and my feeling is that the SQL OS shouldn't
> really limit the rows.  But since the SQL OS can't really "tell" how it is
> being called, it probably implies that it should always return all the
> rows, and then (for the former case) that the viewer layer should take
> responsibility for paging.  It is a presentation concern, after all.
> 

Re: What to do with queries that return large datasets?

[Dan Haywood <da...@haywood-associates.co.uk>]

Hi Kevin,
First off, I think we should distinguish between results that are rendered
in the UI, vs those that are only ever returned to a domain object.

You have the latter case, and my feeling is that the SQL OS shouldn't
really limit the rows.  But since the SQL OS can't really "tell" how it is
being called, it probably implies that it should always return all the
rows, and then (for the former case) that the viewer layer should take
responsibility for paging.  It is a presentation concern, after all.

I agree it would be worth adding some additional infrastructure into the
Object Store API, eg some additional parameters that can be used by the
viewer to request rows 1~100, 101~200 etc.

fyi, in the NO MVC .NET impl, this is done reaonably transparently by
returning an IQueryable<T>.  It's still the viewer that does the paging
though.

I suspect I'll be seeing a new JIRA ticket being raised for this...?

Dan


On 23 March 2012 13:59, Kevin Meyer - KMZ <ke...@kmz.co.za> wrote:

> Hi guys,
>
> At the moment, the SQL object store defaults to instantiating no more
> than 100 instances of any POJO (e.g. allInstances(Member.class) will
> only return up to 100 Members).
>
> I have added the ablity to increase this maximum via the properties file
> (e.g. use: "isis.persistor.sql.maxinstances=1000").
>
> What's the general rule here?
>
> Real world example: I want to generate an invoice for all NORMAL
> members for their dues this year. From a pure design point of view, I
> therefore want to use a pattern query to fetch all NORMAL members,
> then with each member, create an invoice. Neither the list of members
> nor the created invoices is ever presented to the UI.
>
> As it stands right now, I need to increase the maxinstances value.
>
> I don't really want to fire off a custom SQL query to create the invoice
> entries manually...
>
> Regards,
> Kevin
>
>