RE: CountHelper and criteria.clone()
The Criteria clone method comes from its Hashtable superclass. The problem
is probably because Hashtable is doing a shallow copy and the underlying
objects are the same. FWIW, there is a JIRA wish list item for this to be
redone as a deep copy. But this probably will be a 4.0 item.
In general, it's best to create or .clear() Criteria objects for each
action. This is usually not a big coding / overhead issue.
However, if you have complex queries that are done frequently, you can
look at using the Criteria.Criterion subclass to create a reusable
query definition. See the Using Criterion... section in the docs at:
http://db.apache.org/torque/releases/torque-3.3/runtime/reference/read-from-db.html
FYI - I have to put a plug in for the SummaryHelper class that is in the 3.x
CVS Head but not in a release yet. It can be used instead of the CountHelper
class and has a lot more functionality. Here's the Javadoc in the 4.0 test
site (same as in the 3.3. tree).
http://db.apache.org/torque/releases/torque-4.0/documentation/modules/runtime/apidocs/org/apache/torque/util/SummaryHelper.html
> -Original Message-
> From: Luca Ciocci [mailto:l.cio...@askweb.it]
> Sent: Tuesday, July 06, 2010 11:52 AM
> To: torque-user@db.apache.org
> Subject: CountHelper and criteria.clone()
>
> Hello,
> I discover the CountHelper object and I think that could be very
> usefull, but I have seen that it modify the original criteria object
> adding a select count(*) to the query.
>
> So my question is: exist a way to real copy the criteria object before
> use the CountHelper (or exists another trick?)
>
> A simple code like this not work:
>
> List list=new LinkedList();
> Criteria criteria=new Criteria();
> criteria.add(MyTablePeer.MyColumn,'MyValue'); //Adding a simple column
>
> Criteria criteriacount=(Criteria)criteria.clone(); //Important
> CountHelper counthelper=new CountHelper(criteriacount);
> int recordcount=counthelper.count();
>
> if (recordcount<1000) {
> list=MyTablePeer.doSelect(criteria);
> //This instruction fail and throw DataSetException
> }
>
>
> Thanks in advance.
> Luca
>
>
> --
> Ing. Luca Ciocci
> ASK srl
> tel 059 271417
> fax 059 3683560
>
>
> -
> 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.et
-
To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org
For additional commands, e-mail: torque-user-h...@db.apache.org
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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : SELECT attnotnull > FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 > 2010-07-03 23:49:31 SAST DETAIL: parameters: $1 = '29427', $
Re: Excessive database queries on postgresql: village metadata queries
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 To: Apache Torque Users List 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 d
RE: Excessive database queries on postgresql: village metadata queries
> ... > 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 remember dimly using a patched version of village which caches the metadata (per connection, if i remember correctly). I do not know whether this was a public version or whether this was a private hack. Anyway, unfortunately I did not find the patched version but if I remember correctly it was only a few lines of code. Thomas - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
Re: Excessive database queries on postgresql: village metadata queries
On 07.07.10 19:15, Thomas Fischer wrote: >> ... >> 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 remember dimly using a patched version of village which caches the > metadata (per connection, if i remember correctly). I do not know whether > this was a public version or whether this was a private hack. Anyway, > unfortunately I did not find the patched version but if I remember > correctly it was only a few lines of code. I was planning to do a patch release of Village anyway to have TORQUE-133 and some old Oracle BLOB-bug fixed. If you cared to dig up the patch, we could include it in a 3.3.1 release. Bye, Thomas. - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
Re: Excessive database queries on postgresql: village metadata queries
On 07 Jul 2010, at 4:43 PM, Greg Monroe wrote: 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. I've just methodically tried the postgresql 8.4 jdbc4 driver, then 8.4 jdbc3, then 8.3 jdbc4, and 8.3 jdbc3, then finally 8.2 jdbc4, and all of them cause a storm of metadata queries (I have a database with many tables, and many relationships between tables). I am beginning to suspect that the problem was introduced when torque was changed from v3.2 to v3.3. Regards, Graham -- - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
