very long cache reaload after deleting a structure

Hi,





After deleting a molecule, we are experiencing very long cache regeneration. (about 30 seconds).


We use this static method for deletion:


UpdateHandler.deleteRows(conHandler, "Molecules", "WHERE cd_id = 100");





additional info:


jchem_3_2_11


7 000 000 molecules


after restart the loading of the cache takes 6 minutes (that is OK baceuse, we don't want to restart the server frequently)


Molecules_UL:


86 Insert:22129885


85 Deletes


84 Insert:22129884





Is it normal, or did I make mistake?





Thanks


Gabor

Hi Gabor,





Yes, this is normal behaviour.





As you can see in the update log table ("Molecules_UL") for deletes we currently do not store the individual cd_id values, as storing potentially millions of cd_id values would be inefficient.





So we must fetch all cd_id values from the database and compare them with the cache content to see what were the changes and act accordingly.


The same thing happens after a mass insert (import).





This is still a lot faster than a cache reload, though I agree it can be inconvenient for such big tables if structures are frequently deleted.





We plan to provide a method to delete individual rows and allowing much faster cache updates.





In case you happen to try to detect and delete duplicate structures, the PERFECT search mode should be used (opposed to EXACT), it's speed will


be unaffected by the deleting of rows as this "special" mode does not rely on the cache.





Best regards,





Szilard

Hi Szilard,





Since there are no frequent deletions in our database, I think we can live with it. Though we are waiting for the new feature that allows fast cache regenaration after deletion of individual rows.





Thanks for your reply.


Gabor

Hi Gabor,





Good news: we have implemented the new feature and it will be present in our next major release (5.0) which is due to be released soon.

Code:

UpdateHandler.deleteRow(ConnectionHandler conh, String tableName,int cd_id)

Szilard