Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: pass (herse.apache.org: domain of greg.monroe@dukece.com
 designates 152.3.166.24 as permitted sender)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Joins and performance
Date: Fri, 1 Jun 2007 12:29:54 -0400
Message-ID: <8F5843B903F59D4C8C6806BB49A39119034E72FD@dukece-mail3.dukece.com>
In-Reply-To: 
 <C3339485BCFFE44299CE192B46142F2E3C0656@brussels.office.pagelink.nl>
Thread-Topic: Joins and performance
Thread-Index: AcXj6bk3fKUPuU0tEdq7qwBgCBBTdXAX898gAAHVLPAAAWDu4AAAo6sAAAOM6bA=
References: 
 <C3339485BCFFE44299CE192B46142F2E3C0656@brussels.office.pagelink.nl>
From: "Greg Monroe" <Greg.Monroe@DukeCE.com>
To: "Apache Torque Users List" <torque-user@db.apache.org>

> -----Original Message-----
> From: Robert Kromkamp [mailto:r.kromkamp@pagelink.nl]=20
> Sent: Friday, June 01, 2007 10:29 AM
>=20
> Thanks for you reply! I've used views before within Torque.=20
> But in that case I've problems with inserts/update=20
> statements. How do you handle these?

Same way you do with normal SQL.. avoid them in any complex=20
view. :D  I tend to use views for reporting / master list
sorts of things, e.g. display a list of employees sorted
by school and building.

If information needs to be updated, it should be in the
record detail.  E.g., the employee's info or the building
info.  If you're down to dealing with a single record and
it's related tables, there is no need to optimize.

Occationally, if I think another programmer might get=20
confused by a Torque view object, I will override the
set methods to make them private.  This lets folks know
that this object is "special".

One other trick that is sometimes possible is to include
the primary key of the joined records and define them as
foreign keys in the "view" definition.  That way Torque will
generate the get related object(s) methods.  E.g., include
the employee id as part of the view and in the XML define it=20
as a FK to the employee table.  Then if you have a "view"=20
object, you can easily get the true employee record object=20
(and use this for updates).

>=20
> Regards,
> Robert
> =20
>=20
> -----Original Message-----
> From: Greg Monroe [mailto:Greg.Monroe@DukeCE.com]
> Sent: Friday, June 01, 2007 4:15 PM
> To: Apache Torque Users List
> Subject: RE: Joins and performance
>=20
> If this is a frequent/heavily used query, another way to deal=20
> with this is to create a view and a matching Torque object. =20
> There is a table option that suppresses the SQL generation. =20
> You can then just use the Peer/Record object as like it was a=20
> normal table.
>=20
> Note you may want to pay close attention to the indices=20
> requirements of the underlying table.  If commonly used=20
> columns in a large join are not index, it can be a BIG=20
> performance hit.
>=20
> > -----Original Message-----
> > From: Vitzethum, Daniel [mailto:Daniel.Vitzethum@gigatronik.com]
> > Sent: Friday, June 01, 2007 9:40 AM
> > To: Apache Torque Users List
> > Subject: AW: Joins and performance
> >=20
> > Hi Robert,
> >=20
> > > If I've 100 companies (which are schools) and 200=20
> buildings, I will=20
> > > executes 100 * 200 queries to get all the employees.
> >=20
> > be sure to avoid this... sounds like a killer ;-)
> >=20
> > > In low level SQL
> > > this query can be combined in one query. Is there a way to handle=20
> > > this within Torque?
> >=20
> > In Torque, you can use the Criteria.addJoin() method to link two=20
> > tables.
> >=20
> >=20
> > Using that feature, you cannot use the Peer.doSelect() method, but=20
> > have to use Peer.doSelectVillageRecords(), which will return all=20
> > columns of all joined tables in a village Record object,=20
> with no big=20
> > difference to plain JDBC in handling. If you need or want=20
> Torque data=20
> > objects, you have to extract them one by one from the result set.
> >=20
> > For a project of us, we wrote a helper class named JoinHelper that=20
> > does this business for you AND returns an tree of Torque=20
> objects. In=20
> > your case, it would return COMPANY objects that can be queried for=20
> > their BUILDINGs, which again know their EMPLOYEEs.
> >=20
> > Please search the thread about two years ago in Torque=20
> archive and the
>=20
> > discussion about. JoinHelper massively uses reflection, but=20
> is tested=20
> > quite well and in production. If you have more detailled=20
> questions...
> > you're welcome.
> >=20
> >=20
> > Daniel
> >=20
> >=20
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
> >=20
> >=20
>=20
> Duke CE Privacy Statement
> Please be advised that this e-mail and any files transmitted=20
> with it are confidential communication or may otherwise be=20
> privileged or confidential and are intended solely for the=20
> individual or entity to whom they are addressed.  If you are=20
> not the intended recipient you may not rely on the contents=20
> of this email or any attachments, and we ask that you  please=20
> not read, copy or retransmit this communication, but reply to=20
> the sender and destroy the email, its contents, and all=20
> copies thereof immediately.  Any unauthorized dissemination,=20
> distribution or copying of this communication is strictly prohibited.
>=20
>=20
>=20
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>=20
>=20
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>=20
>=20

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