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: neutral (athena.apache.org: local policy)
Message-ID: <48E4C54D.3040504@backstagetech.com.au>
Date: Thu, 02 Oct 2008 22:57:49 +1000
From: Scott Eade <seade@backstagetech.com.au>
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
To: Apache Torque Users List <torque-user@db.apache.org>
Subject: Re: Will migrating from 3.0 to 3.3 help me?
References: <c6753c3a0809292015r5d474be0l3677446aaf4c15ab@mail.gmail.com>
 <48E3091E.3050502@backstagetech.com.au>
In-Reply-To: <48E3091E.3050502@backstagetech.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Scott Eade wrote:
> Richard Walker wrote:
>> The first thing I noticed was the huge difference in performance
>> using different versions of JDBC drivers.  Older versions
>> (e.g., pg74.216.jdbc3.jar) work well; newer ones (e.g.,
>> one I tried for PostgreSQL 8.2, postgresql-8.2-506.jdbc3.jar) don't.
>> The difference is staggering - a set of queries that takes 10 seconds
>> with an old driver takes over 90 seconds with a newer
>> driver.
>>
>> I ran a trace on the code and found out what's going on - the Column
>> class in the village library does lots of requests on metadata.
>> In the old JDBC driver, these calls are not implemented;
>> in the newer drivers, they are, and there are now
>> 12 - yes, twelve! - extra database queries being generated
>> for each original query.
>>
>> I notice a Sybase user has already reported this at:
>> http://issues.apache.org/jira/browse/TORQUE-36
>
> Given that the the queries must have been working prior to upgrading 
> the the JDBC driver it would seem that the metadata is not actually 
> needed for query execution.
>
> How about you make yourself a copy of village.jar with the offending 
> lines in the Column class commented out and see if your queries still 
> execute correctly.  Really this would just be the opposite of having 
> the stub methods in the old JDBC driver.  Provided your application is 
> not accessing the metadata present in the Column class (directly or 
> indirectly) this should be a valid approach.
I have been using postgresql-8.1-408.jdbc3.jar for some time and just 
the other day upgraded to postgresql-8.3-603.jdbc4.jar (the server 
itself is 8.1), so the issue raised here gained my attention.

I set postgres to log *all* queries and yes, there is an awful lot of 
activity going on.  The level of activity is pretty similar between the 
8.1 and 8.3 JDBC driver versions.

If I drop back to the 7.4 driver you mention (whose jar file is ~200K 
vs. ~400K for the newer ones) the activity drops considerably.

I am not telling you anything you don't already know, but it sure is 
interesting to see this myself.

Looking at torque, the only references to com.workingdogs.village.Column 
(directly and via c.w.v.Schema) are in org.apache.torque.util.BasePeer.  
Usage would appear to be limited to retrieving the name of the column 
(via Column) and the number of columns (via Schema).

So it looks to me like we should look at producing a version of Village 
that skips retrieving unnecessary metadata.  For people using JDBC 
drivers that implement the metadata retrieval methods this will result 
in a performance improvement significantly larger than any other 
refactoring we might be considering for 4.0 and potentially this could 
be substituted into to a 3.3.1 release with just an updated pom.

I have a couple of dozen other things on my plate right now, but if 
someone else cares enough to verify my analysis I don't think it will be 
too difficult to achieve something positive out of this.

Scott

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