alternate of looping on more than 5000 cd_id

User 447e36cf40

13-01-2011 16:02:08

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.


 


 


 


 

ChemAxon 9c0afc9aaf

13-01-2011 17:17:09

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)


 

User 447e36cf40

14-01-2011 19:31:16

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!

ChemAxon 9c0afc9aaf

14-01-2011 20:03:17

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


 


 


 


 

User 447e36cf40

18-01-2011 18:43:39

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?


 


 

ChemAxon 9c0afc9aaf

18-01-2011 21:16:26

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