Performance issue in multi-term query including structure

When we are writing SQL statement for multi-terms query, if including chemical structure in the search terms, we found that the speed would be much slower than the situation without chemical structure in the search terms. Please refer to the following codes:

select acd1_structure.cd_id from ACD2D_MOLTABLE, ACD1_STRUCTURE where ACD2D_MOLTABLE.MDLNUMBER=ACD1_STRUCTURE.MDLNUMBER and ACD2D_MOLTABLE.RULE5 >= 1  AND ACD1_STRUCTURE.CD_MOLWEIGHT >= 3000  AND jc_contains(cd_smiles, 'C1=CC=CC=C1') = 1

We have discussed this issue with Ferenc in details and changed the statements.

You have basically two options for improving performance:

1.

Using jc_compare with the filterQuery option:

 

SELECT cd_id FROM acd1_structure WHERE jc_compare(cd_smiles, 'C1=CC=CC=C1', 'sep=~ t:s~filterQuery:SELECT acd1_structure.cd_id FROM acd2d_moltable, acd1_structure WHERE acd2d_moltable.mdlnumber=acd1_structure.mdlnumber and
acd2d_moltable.rule5 >= 1 AND acd1_structure.cd_molweight >= 3000') = 1

2.

Turn on cost estimation support for the Oracle optimizer: http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#opti .