The silver bullet for performance

[Iain D Keddie]

It's rare that you can find a small change that can make such a marked
difference, but I believe that I found one and thought I should share.

I have written a new method in DaoCPath which is used in indexing and
querying. Instead of exclusively using the separate DAOs this method
simply does an sql query that joins the link table and the main cpath
table, in one query. In effect instead of doing the following
+ get interaction from cpath table
+ get values from link table
+ get interactor from cpath table

I do the following:
+ get the interaction from the cpath table
+ get the interactor from the cpath table joined with the link table.

This accompanied by 3 database indexes, reduced our queries from just
under 80 seconds down to 3 seconds. Along side some threaded changes to
indexing, it dropped the indexing of our most troublesome dataset, from
24 hours to 2 hours!

I know it's too late for me to introduce new changes to mys setup, but I
do have a suggestion which may make your continuing code faster. The
cpath version 0.3.2 made 3 database queries per interaction, so to get
100 interactions took 300 queries. This could probably be dropped to 3
or 4 queries by using a sql "in" to get all of the interactions, then
one query joining the link table and the cpath table, collecting the
interactors. I'm afraid I haven't had the opportunity to see if this is
really possible in the current architecture, but it may be worth looking at?

I have attached the altered class. The method
"combinedExtractInteractors" can be used to replace "extractInteractors"
in PsiAssembly. The new sql is in DaoCPath.getInteractorsByInteraction.

Hopefully this will be some use to you.

Regards

Iain Keddie