setResultTable output

Hi,


I have been using setResultTable in my searcher class to capture the results from a structure in a temp table. I observe that the order of compounds in the web interface differs slightly from the order in which the compound ids are stored in the temp table, especially when the search results are large (> 100 etc). Is there a bug that might be causing it?





Thanks!

Hi,





The ordering of rows is not defined in relational databases.





You have to use the "ORDER BY" clause if you need the rows in a particular order, e.g.:





SELECT cd_id FROM my_table ORDER BY cd_id;





Szilard

Hi Szilard,


I have been using the default ordering, which is ORDERING_BY_ID_OR_SIMILARITY (default). My question is that why does it differ on the web versus when I capture the output in a temp table. I have noticed that the ordering matches if the search result set is generally less than 50. But it seems to be out of order when the result set exceeds 100. Even when they excedd 100, the first 10-30 compounds match the order with the temp table. Towards the end, though they seem out of match.

Hi,





The ordering mode is only applies to the result retrieved in the API with


getResults() or getResult(int).





In a database table the order of the rows is always undefined.


If the order of retrieved data is sometimes identical its only a coincidence, this is a common false assumption of novice database users, e.g:





http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:30509289302999





Szilard

Hi Szilard,


I understand that the order of insertion doesn't really matter, but then in my query I am ordering it by the similarity. The API also does the same when it uses setResults(); so then why is the mismatch?


Renju

In the mentioned search mode the API orders the results


- by similarity in case of similarity search


- by cd_id in case of other search modes





Do you think the results you get from the API are not in order ?





In the result table there is absolutely no ordering of course.


(cannot be, it's a principle of all RDBMS database systems)





Szilard

As a matter of fact, the ordering of results in the temp table is correct; it is the web display that is wrong. The temp table stores the results in the decreasing order of similarity. The issue is when more than one compound has the same similarity value. The web display orders compounds randomly, if they have the same similarity value, while the temp table orders them in the increasing order of their compound ids. That's the reason that the compounds are not matching.

Hi,





Once again: there is no ordering at all in the result table, and cannot be. The order you see is produced by chance.





In the web interface we order by dissimilarity values in the case of a similarity search, the cd_id values are not considered at all.


This means that the order of structures with identical dissimilarity values is not defined.





In short: there is no error, everything works as expected.





Best regards,





Szilard

I had a quick question regarding this earlier topic. I am using setResultTable() to write the cd_id and similarity(when applicable) into a temp table. If there are multiple users running a structure search query simultaneously, then how does the API deal with writing the cd_id into the temp results table. I believe that the data into the temp table gets overwritten and if each user wants to view their result ids from the temp table, they cannot. Is that right?


Thanks!

Dear Renju,





The behaviour depends on what mode you set with setResultTableMode():


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





You can append the results to an earlier result table but probably that's not the behavior you want, so I assume you are using CREATE_OR_REPLACE_RESULT_TABLE:





http://www.chemaxon.com/jchem/doc/api/chemaxon/jchem/db/JChemSearch.html#CREATE_OR_REPLACE_RESULT_TABLE





In this case the table is dropped and re-created at each search. In a multiuser environment this would certainly cause problems.





I suggest you to use a different result table for each user (session), or even for each search. Make sure you remove these table after they are no longer needed.





Best regards,





Szilard