Performance problem

[Patrick Carl <pa...@coi.de>]

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