Solving a problem with Torque

[Graham Leggett <mi...@sharp.fm>]

Hi all,

When I do a search using torque, I am given a List of objects back,  
which I can iterate through at will.

These objects are linked with foreign key relationships to other  
objects, and so I can call getFoo() on each object in my result set,  
and torque will initiate a query and fetch the Foo object for me from  
the database. So far so good.

The problem I have in my case is that the Foo object is a status, and  
there are only a handful of possible statuses. What ends up happening  
is that the status table gets queried over and over again, one for  
each result in my original query. This takes time, and takes space.

Ideally what I'd like to have is torque be clever enough, when  
getStatus() is called, to say "hang on, I've already got a status with  
that key linked to this List or results, let me just use the one I  
have already got, and not query status again".

I am aware that torque supports caching, but I don't like caching as a  
solution, as I want the status object created within the same  
transaction as the original query.

Would it make sense for torque to be able to keep an optional index  
map for certain table relationships, and should this index map be  
present, torque first looks up the result in the index map before  
returning it to the client? And if not in the index map, an actual  
query is performed on the status table, the index map is updated, and  
the second time getStatus() is called, the object comes back from the  
index map, not the database. The nett effect is that if you have just  
4 statuses in your complete result set, your status table will only  
ever be queried 4 times, and not once for every row in the original  
result set.

Does this kind of thing make sense?

Regards,
Graham
--


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

RE: Solving a problem with Torque

[Thomas Fox <Th...@seitenbau.net>]

> When I do a search using torque, I am given a List of objects back,
> which I can iterate through at will.
>
> These objects are linked with foreign key relationships to other
> objects, and so I can call getFoo() on each object in my result set,
> and torque will initiate a query and fetch the Foo object for me from
> the database. So far so good.
>
> The problem I have in my case is that the Foo object is a status, and
> there are only a handful of possible statuses. What ends up happening
> is that the status table gets queried over and over again, one for
> each result in my original query. This takes time, and takes space.
>
> Ideally what I'd like to have is torque be clever enough, when
> getStatus() is called, to say "hang on, I've already got a status with
> that key linked to this List or results, let me just use the one I
> have already got, and not query status again".
>
> I am aware that torque supports caching, but I don't like caching as a
> solution, as I want the status object created within the same
> transaction as the original query.
>
> Would it make sense for torque to be able to keep an optional index
> map for certain table relationships, and should this index map be
> present, torque first looks up the result in the index map before
> returning it to the client? And if not in the index map, an actual
> query is performed on the status table, the index map is updated, and
> the second time getStatus() is called, the object comes back from the
> index map, not the database. The nett effect is that if you have just
> 4 statuses in your complete result set, your status table will only
> ever be queried 4 times, and not once for every row in the original
> result set.
>
> Does this kind of thing make sense?

The problem with caching is that if you update a status object you also
must update the cache, which makes it kind of complicated. It works (you
can set the useManagers option when generating) but caching has other
problems (e.g. what if someone else changes the data then your cache is
stale).

I have used a slightly different strategy in the past which does not have
this problem as the select is done manually at a defined time.
As the original object has a foreign key to the foo object, I did
- create a set of all ids of the foo objects in the object list
- query the foo peer for the objects with these ids
- go through the objects and set the matching foo object.
This method is fast because it introduces just one additional query per
list (instead of one per entry in the normal case). It is also better than
joins if the relation is not approximately 1:1 as no additional data must
be transferred (for example, if the list contains object1:status1,
object2:status1, object3:status2, object4:status2 this data will be
transferred as such for a join but with the method above the data
transferred is  object1, object2, object3, object4 for the first query and
status1,status2 for the second query which avoids transferring the
duplicate status.

Would this solve your problem ? I have thought of generating these kind of
methods, should not be too difficult.

What would also be possible is to use the doSelectJoinXXX method which
prefills one of the related tables using a join. But this does not work if
there are two or more related tables.

    Thomas


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

Re: Solving a problem with Torque

[Thomas Vandahl <tv...@apache.org>]

On 15.05.11 14:13, Graham Leggett wrote:
> I am aware that torque supports caching, but I don't like caching as a
> solution, as I want the status object created within the same
> transaction as the original query.

For status entries like these, caching is always my recommended
solution. We have hit rates of over 99% with the Torque built-in cache.

The solution you suggest is more or less the same thing that the cache
does. If you need help with the implementation, I'm happy to share my
experiences.

Bye, Thomas.

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