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
