It seems to me that you would want to know what the RDBMS optimizer is doing when you execute your SQL from both your program and when connected directly to the database. Consider trying to determine how hs can supply you these query plans via session tracing.
-----Original Message----- From: Patrick Carl [mailto:[EMAIL PROTECTED] Sent: Thursday, October 21, 2004 7:33 AM To: [EMAIL PROTECTED] Subject: Performance problem Hi there, I am using Torque and like it very much. But now I have a performance problem. I am searching for documents depending on their related attributes. A document can have 0 to many attributes. The table strucuture is defined as: ... <table name="document" description="Represents a Document"> <column name="id" type="INTEGER" primaryKey="true" autoIncrement="true" required="true"/> <column name="archive" type="VARCHAR" size="20" javaName="archive" description="source archive"/> <column name="archiveid" type="VARCHAR" size="20" javaName="archiveId" description="ID in the source archive"/> <column name="name" type="VARCHAR" size="255" description="name"/></table> <table name="attribute" description="Represents an attribute of a document"> <column name="id" autoIncrement="true" type="INTEGER" primaryKey="true" required="true"/> <column name="docid" javaName="docId" type="INTEGER" description="Related document"/> <column name="configid" javaName="configId" type="INTEGER" description="Related attrconfig"/> <column name="value" type="VARCHAR" size="255"/> <column name="valuedouble" type="DOUBLE" javaName="valueAsDouble"/> <column name="valuelong" type="BIGINT" javaName="valueAsLong"/> <column name="valuesearch" type="VARCHAR" size="255" javaName="searchValue" description="special search value"/> <column name="language" type="VARCHAR" size="15" description="language of this attribute"/> <foreign-key foreignTable="document" name="fk_attr_doc" onDelete="cascade"> <reference local="docid" foreign="id" /> </foreign-key> <foreign-key foreignTable="attrconfig" name="fk_attr_cfg"> <reference local="configid" foreign="id"/> </foreign-key> <index name="idx_atr_doc"> <index-column name="docid"/> <index-column name="language"/> </index> <index name="idx_atr_doc_lang_search"> <index-column name="docid"/> <index-column name="valuesearch"/> <index-column name="language"/> </index> <index name="idx_atr_doc_lang_search"> <index-column name="docid"/> <index-column name="valuesearch"/> <index-column name="language"/> </index> </table> .... My code looks like: Criteria crit = new Criteria(); crit.setIgnoreCase(true); crit.setDistinct(); crit.addJoin(DocumentPeer.ID, AttributePeer.DOCID); crit.add(AttributePeer.CONFIGID, 17); crit.add(AttributePeer.VALUESEARCH, (Object) searchPattern, Criteria.LIKE); crit.add(AttributePeer.LANGUAGE, "en"); List res = DocumentPeer.doSelect(crit); Such a search takes about 50 seconds.Performance gets worse the more attributes and documents are inserted. When executing the following SQL directly at the database the result is received in much less than one second. I am using Hypersonic 1.7.2. Is there any advice you can give me? Thanks in advance! Patrick --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
