alternate of looping on more than 5000 cd_id

I am tryng to loop through a resultset with more than 5000 cd_id (the number of cd_id is dynamic)and it is taking a lot of time. Is there any other alternative like a Jchem api which I can call and pass the resultset with cd_id for looping.

 

 

 

 

Hi,

 

I'm afraid if reading from a ResultSet is slow, it would also be slow inside out API (or any other).

 

Some general tips:

 

1 . Use ResultSet.setFetchSize wit a reasonably high number (e.g 1000):

http://download.oracle.com/javase/1.5.0/docs/api/java/sql/ResultSet.html#setFetchSize(int)

2. Instead of SELECT * specify the fields in you query if you don't need all

3. Getting a field by order instead of name should be faster, e.g:

http://download.oracle.com/javase/1.5.0/docs/api/java/sql/ResultSet.html#getObject(int)

instead of

http://download.oracle.com/javase/1.5.0/docs/api/java/sql/ResultSet.html#getObject(java.lang.String)

 

Thank you for the information. I think i havnt explained my question clearly.

I am implementing substructure search.I have used Jchem search method to do a sub structure search against the structure table, This method returns all the Cd_id's that match the structure drawn. I have more tables releated to the structure table. I have  to get information from these tables for the cd_id's that match my structure. Please advise how this can be done? Thanks again!

Hi,

Though I'm still not sure if I understand the problem fully, I assume the following:

1. You execute a search with JChemSearch which returns cd_id values and -  I guess no speed problem here ?

2. You execute SQL queries to fetch related data for each result, e.g. 5000 SELECTS -  and this is slow ? 

 

For #2 I would recommend re-using a PreparedStatement 5000 times instead of executing 5000 different Statements :

http://download.oracle.com/javase/1.5.0/docs/api/java/sql/PreparedStatement.html

 

We do not have any special API for this.

 

Best,

Szilard

 

 

 

 

Thanks for quick response.

After I draw a structure in Marvin sketch and do a Jchem search I am getting an array of cd_id. I have to find how supplier have how many compounds from the array for that I have to loop through the array of cd_id and find the matching cd_id & supplier_id from a table with million records.

So if i get 5000 cd_id in array so instead of looping through the array of cd_id and table(million records)  is there any other way to do?

 

 

If you only want to get back structures for a specific supplier you may use filter query:

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/sss/search/JChemSearchOptions.html#setFilterQuery(java.lang.String)

 Make sure you have an index on the suppliers.

 

Otherwise you might try creating a temporary result table, and executing a join on that:

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/jchem/db/JChemSearch.html#setResultTableMode(int)

 

Best,

 

Szilard