Torque performance problems

[Chris Felaco <cf...@netscape.net>]

Hi,

I've been using Torque in my application for over a year now and I've just discovered that every time an OM object is saved, the record is first SELECT'ed.  I can't fathom a reason why this would be necessary or deemed a good practice.  It may be acceptable when updating single records, but when doUpdate() is used to affect multiple records, the performance cost is totally unacceptable.

Another problem is that doUpdate doesn't report back how many records were updated.  There's a check in the code which logs an error if a single record was expected and MORE than 1 record was updated, but there's no warning if NO records were updated.  This can lead to insidious bugs in Torque if the user messes with the "new" property of their OM objects.  If the user calls setNew(false) on an OM object that has not been saved yet, the update will silently do nothing.  Yes, that is a user error, but I think it might also be possible for Torque itself to erroneously setNew(false) and cause this to happen.

Our DBA consultants have been complaining for a long time about the number of queries filling the SQL cache in our Oracle DB.  The problem is that Torque uses Statements instead of PreparedStatements for all queries generated by Criteria.  Thus every SQL statement is unique and requires parsing by the SQL engine.  At a minimum, any time you perform retrieveByPK, a PreparedStatment should be used so that a distinct query isn't generated every time.  Random queries generated by Criteria are less frequent and concern me less.

But, the other problem with generating SQL strings instead of using PreparedStatements is that it requires conversion of every Java type to a string representation which is error-prone and inefficient.  We have run into a problem when we use a VARCHAR field with a numeric value.  We have a legacy table where the primary key is a VARCHAR2 column, but in practice the values are always numbers.  Thus in the code we want to use Number objects to enforce the data integrity.  The problem is that Torque will blindly create a WHERE clause of the form "order_number = 1234" instead of "order_number = '1234'" because it does not take into consideration the actual data type of the column.  The result is that queries must do FULL TABLE scans even though we're selecting a single record by the primary key value.  (When you supply a number constant for a VARCHAR2 field, the SQL engine has to convert every single VARCHAR2 key value to a NUMBER in order to make the comparison properly.)  For now we've worked around the problem by converting the Number to a String in the Peer classes that setup the Criteria, but this is not the optimal solution.  Using PreparedStatements for all SELECTs would solve this problem entirely, and give the added benefit of handling other SQL types.

Another improvement would be if the Torque objects only updated the columns that were actually modified.  A simple BitSet could be used to keep track of the dirty properties, and then the Criteria for update could be generated using only the dirty columns.  This can easily be added to the Peer template.

I don't understand what the point is in using Village for Torque.  It seems to me that Torque has to bend over backwards to make things work with Village and would be better served by using the JDBC API directly.  Village appears to be a good tool for developers who were previously doing things directly with JDBC, but not for the developers who have graduated to Torque.  Torque is very inefficient when doing selects because it has to convert every Village Record into a Torque object.  It would be much faster to simply use the ResultSet directly.  The only valuable part of Vlillage that I can see is the "Value" class which handles conversion to/from Java native types to SQL native types automatically.  This can easily be extracted into a more reusable form.  I don't think we should drag along the whole Village API just for that.  The Torque code would be much easier to maintain if it didn't use yet-another API.  Besides, it's very difficult for developers to even find info on Village on the Net.

I will be submitting a patch to fix the doUpdate method, since I consider that the biggest problem.  I may also start working on updating the doPSSelectMethods so that they can be used instead of the existing implementation.

- Chris



__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp 

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/

Re: Torque performance problems

[Stephen Haberman <st...@beachead.com>]

On Tue, Jan 14, 2003 at 09:11:18AM +0000, Henning P. Schmiedehausen wrote:
> Where Torque _really_ shines is the auto-generation of the Peer/Object
> classes from an XML file, which makes it easy to use. Everything else
> is pretty much refactorable...

Agreed; you're recounting of Martin's approach to Torque sounds
great. I didn't know there were active/in-active plans for fixing it
up; it'd be awesome to see those three points implemented.

As far as using the player persistence layer, I'd generally prefer
something with a bigger community, e.g. OJB, but if it is
technically sound and gets the job done, I'd give it a +1.

- Stephen

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: Torque performance problems

["Henning P. Schmiedehausen" <hp...@intermeta.de>]

Stephen Haberman <st...@beachead.com> writes:

>maintainable code), Torque would require either:

>- Huge amounts of evolution to incrementally fix/refactor all of the
>  relatively messy issues with it (and no unit tests to help you go
>by). To me this would result in such a bandaged mess as to make it
>not worthwhile.

>Or:

>- A complete revolution starting with a new architecture complete
>with the stuff like PreparedStatements, etc., and filling in the
>gaps with rewritten and/or refactored existing code.

Martins' idea seemed better to me:

- Separate the template based engine which creates java classes from
  templates from the actual persistence code.

- Scrap the persistence code and replace it with OJB (or something
  else; I have a 90% ready implementation based on player
  (http://player.sourceforge.net) which uses really lightweight
  objects and a pluggable selection / criteria code but I never really
  went to finish it. Any takers? :-) But I guess, there are about a bazillon
  self-rolled persistence layers out there).

- Maybe: Refactor the templating engine to use something like JSL or 
  XSLT to translate the templates into class files.

Where Torque _really_ shines is the auto-generation of the Peer/Object
classes from an XML file, which makes it easy to use. Everything else
is pretty much refactorable...

	Regards
		Henning

-- 
Dipl.-Inf. (Univ.) Henning P. Schmiedehausen       -- Geschaeftsfuehrer
INTERMETA - Gesellschaft fuer Mehrwertdienste mbH     hps@intermeta.de

Am Schwabachgrund 22  Fon.: 09131 / 50654-0   info@intermeta.de
D-91054 Buckenhof     Fax.: 09131 / 50654-20   

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: Torque performance problems

[Stephen Haberman <st...@beachead.com>]

On Mon, Jan 13, 2003 at 01:31:31PM -0500, Chris Felaco wrote:
[snip summary of a handful of Torque's current problems]

Torque initially lured me to Turbine as the concept is great, but
the implementation is bad. I agree with everything you mentioned,
especially the Village point, and I was all fired up to fix such
things when I came into Turbine.

Since then, I've learned a lot both about myself (as per Dan's
recent post, I find myself biased towards revolutionism), and the
Torque code base. Basically, to be elegant (e.g. good, efficient,
maintainable code), Torque would require either:

- Huge amounts of evolution to incrementally fix/refactor all of the
  relatively messy issues with it (and no unit tests to help you go
by). To me this would result in such a bandaged mess as to make it
not worthwhile.

Or:

- A complete revolution starting with a new architecture complete
with the stuff like PreparedStatements, etc., and filling in the
gaps with rewritten and/or refactored existing code.

Being a revolutionist, I favor the latter. But then you end up
merely duplicating the work of already great persistance projects
like OJB and Hibernate.

Evolutionists might say Torque is salvagable as is; they could very
well be right. In fact, I hope they are, as I use it in an
application, and fairly successfully at that.

To me, the solution to the Torque problem is to make/take a generic
Java code generator, have it read in the existing Torque schemas,
and create a generic, _persistence-less_, object model with the
save()/etc. methods just being hooks into OJB to let it handle the
persistance and all the lovely issues that come with it. Then all of
the dodgy, hard-to-maintain (for me anyway) persistance code comes
out of Torque and only elegance is left (well, ideally :-).

(Sorry to be so pessimistic...if you've got the skills and time to
tackle Torque and making a dent in the bugs/issues/improvements it
needs, then please do so...lots of people will thank you, myself
included.)

- Stephen

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>