Re: Will migrating from 3.0 to 3.3 help me?

[Scott Eade <se...@backstagetech.com.au>]

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.

Scott

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

Re: Will migrating from 3.0 to 3.3 help me?

[Richard Walker <wa...@gmail.com>]

>> 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've traced the extra queries to these two lines in Column.java:

        this.nullAllowed = rsmd.isNullable(columnNumber) == 1;
        this.autoIncrement = rsmd.isAutoIncrement(columnNumber);

I checked the source of the old driver; it hard-codes the
results of those queries, so this.nullAllowed and this.autoIncrement
are always set to false.

So I made this modification:

        this.nullAllowed = false; // rsmd.isNullable(columnNumber) == 1;
        this.autoIncrement = false; // rsmd.isAutoIncrement(columnNumber);

And it works!  No more extra queries.

Problem solved - for me, anyway.
Thanks for the hints.

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

Re: Village meta data calls (Was: Will migrating from 3.0 to 3.3 help me?)

[Richard Walker <wa...@gmail.com>]

On Fri, Oct 3, 2008 at 1:54 AM, Greg Monroe <Gr...@dukece.com> wrote:

> If we don't want to lose this functionality, we still need to do some
> checking as to what the underlying column type is.  I'm thinking that
> this information should get added to the ColumnMap object and the
> initialization routines changed to make the call to get the Meta
> data.  This would cache the meta data, cut the meta data requests to
> just a few, and still maintain the underlying Village flexibility.

When using the old PG driver there are a small number
of DB queries to get type name information for the built-in
PG types - and the results are cached.
In the newer driver the cache is initialized with type
names for these built-in types, so the handful of type name
queries I was seeing in the old driver are completely gone when
using the newer driver.

So I didn't need to comment out the calls that get column type
name information as these were not causing my "problem".
See my previous message for the two lines that were
generating all the extra DB queries.

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

Village meta data calls (Was: Will migrating from 3.0 to 3.3 help me?)

[Greg Monroe <Gr...@DukeCE.com>]

I'm in agreement that we need to fix this... but on the other hand, 
it's been part of Torque since day one and the performance hit hasn't
motivated anyone into fixing yet... 8).

A couple of things to note about the meta data calls (from the memory 
of wading thru the Village code in that past and may not be fully 
accurate...).  

First, although Torque may not use the Column object directly, the 
Village internals for doing updates and inserts use it a lot.  Torque 
currently uses Village calls to do these actions.

Second, we may not want/be able to eliminate all calls to the meta-data.
If my memory is correct, Village checks the meta data in order to 
properly convert the supplied value into the format the database 
expects.  

IMHO, I think this has been secretly helpful in automatically mapping
between Torque's XML schema and the native columns.  Especially when
the tables have not been created via Torque.  It means you just have 
to find a data type that's "close" to the native one and then Village
and JDBC maps it correctly to and from Torque objects.

If we don't want to lose this functionality, we still need to do some
checking as to what the underlying column type is.  I'm thinking that
this information should get added to the ColumnMap object and the 
initialization routines changed to make the call to get the Meta
data.  This would cache the meta data, cut the meta data requests to
just a few, and still maintain the underlying Village flexibility.

Just some quick thoughts on this.  Take them or leave them.

Greg

> -----Original Message-----
> From: Scott Eade [mailto:seade@backstagetech.com.au]
> 
> 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
> 
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.

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

Re: Will migrating from 3.0 to 3.3 help me?

[Scott Eade <se...@backstagetech.com.au>]

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