Hi, I have a question about performance querying a 7.4 database. The
orginal generated query was
SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM amaze._compound _compound0 LEFT JOIN amaze._product
_product7ON (_compound0.object_id =
_product7.compound) LEFT JOIN amaze._database_object _database_object11 ON
(_product7.reaction = _database_object11.object_id) LEFT JOIN amaze._educt _educt2 ON (_compound0.object_id =
_educt2.compound) LEFT JOIN amaze._database_object _database_object6 ON
(_educt2.reaction = _database_object6.object_id) WHERE
( _database_object6.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O' OR _database_object11.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O'
);
This on take a huge time to perform, which may come to a timeout on the
front-end application that uses the database.
So, I decided to modify manually the query like this:
SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM amaze._compound _compound0 LEFT JOIN amaze._product
_product7ON (_compound0.object_id =
_product7.compound) LEFT JOIN amaze._database_object _database_object11 ON
(_product7.reaction = _database_object11.object_id) WHERE
( _database_object11.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O'
)
UNION
SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM amaze._compound _compound0 LEFT JOIN amaze._educt
_educt2ON (_compound0.object_id =
_educt2.compound) LEFT JOIN amaze._database_object _database_object6 ON
(_educt2.reaction = _database_object6.object_id) WHERE
( _database_object6.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O'
)
This should give the same result set, but it's really faster than the
previous one, more than one thousand time faster.
Is there a reason for this huge difference of performance?
Thanks in advance.
--
Olivier Hubaut
North Bears Team
SCMBB - ULB