I had a very similar problem (if not the same), using the "wrong" version of Postgres ODBC drivers (not from Java...).
Try with a different version of the driver... Regards, Álvaro ________________________________ From: Greg Monroe <greg.mon...@dukece.com> To: Apache Torque Users List <torque-user@db.apache.org> Sent: Wed, July 7, 2010 11:43:34 AM Subject: RE: Excessive database queries on postgresql: village metadata queries Torque has been using Village pretty much since day 1.. and AFAIK the Village has been using metadata as long. If you didn't have performance problems before, it's probably a JDBC version problem. FWIW, getting rid of Village (or highly modifying it) is a 4.0 goal. But AFAIK, the Village use of metadata is a sticky problem to tackle. It's used to determine what type of object to use in retrieving columns. Probably the correct fix is to use the *Map classes to do this and not the DB Meta data. It looks like the actual call to the ResultSet.getMetatData() is only done once per query. Plus, there is a todo item in on the PostGres JDBC website about caching the metadata rather than doing selects for each column. IMHO, it's a PostGres JDBC driver issue, since other drivers seem to cache this info. > -----Original Message----- > From: Graham Leggett [mailto:minf...@sharp.fm] > Sent: Tuesday, July 06, 2010 9:19 PM > To: torque-user@db.apache.org > Subject: Excessive database queries on postgresql: village metadata > queries > > Hi all, > > I have been trying to hunt down a performance issue with an existing > torque based system against a postgresql database. Interspersed > between each query are thousands and thousands of metadata queries, > which have slowed our database down by a few orders of magnitude. > > I have managed to trace the key problems down to > com.workingdogs.village.Column.populate(ResultSetMetaData rsmd, int > colNum, String tableName), where the following lines cause SQL queries > to be generated to the database as below: > > this.columnTypeName = rsmd.getColumnTypeName(columnNumber); > this.columnType = rsmd.getColumnType(columnNumber); > this.nullAllowed = rsmd.isNullable(columnNumber) == 1; > this.autoIncrement = rsmd.isAutoIncrement(columnNumber); > > Has anyone encountered this before and managed to work out how to stop > the metadata queries being translated into database queries? I would > expect either the JDBC driver to cache these, or if not for Village to > cache these. > > I am not 100% of the exact change that introduced this problem, it > could have been a move from torque v3.2 to torque v3.3, alternatively > it could have been an update in JDBC driver to > postgresql-8.4-701.jdbc4.jar. The problem was picked up after a large > refactoring job, so I am not 100% sure of the cause unfortunately. > > Has anyone seen this problem before? > > A sample of the queries include the following: > > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '4' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT def.adsrc > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON > (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON > (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and > a.attnum = $2 AND def.adsrc LIKE '%nextval(%' > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '4' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '5' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT def.adsrc > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON > (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON > (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and > a.attnum = $2 AND def.adsrc LIKE '%nextval(%' > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '5' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '8' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT def.adsrc > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON > (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON > (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and > a.attnum = $2 AND def.adsrc LIKE '%nextval(%' > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '8' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '9' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT def.adsrc > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON > (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON > (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and > a.attnum = $2 AND def.adsrc LIKE '%nextval(%' > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '9' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '7' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT def.adsrc > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON > (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON > (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and > a.attnum = $2 AND def.adsrc LIKE '%nextval(%' > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '7' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '10' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT def.adsrc > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON > (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON > (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and > a.attnum = $2 AND def.adsrc LIKE '%nextval(%' > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '10' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '6' > 2010-07-03 23:49:31 SAST LOG: execute <unnamed>: SELECT def.adsrc > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON > (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON > (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and > a.attnum = $2 AND def.adsrc LIKE '%nextval(%' > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $2 = '6' > > Regards, > Graham > -- > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org > For additional commands, e-mail: torque-user-h...@db.apache.org 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-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
