db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <mark.a.g...@accenture.com>
Subject RE: Performance problem
Date Thu, 21 Oct 2004 12:26:39 GMT
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:patrick.carl@coi.de] 
Sent: Thursday, October 21, 2004 7:33 AM
To: torque-user@db.apache.org
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: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



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: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message