Good day everyone
I’ve been looking to find what has changed in LF 10.1 as compared to LF 9.2, in the way searches are performed within the database. I know the tables searchresult0 … searchresult9 have been replaced by one tempresult table, but there are other things.
Last Saturday I shutdown the LF 9.2 services, and upgraded to LF 10.1. All went well, and I was able to search for documents using template fields or otherwise without problems (from the client, WebLink or the in-house app we programmed with the SDK).
But as soon as I started the WorkFlow services, things started to go mad. At this time I don’t know what workflows were executing, but what I do know is that a stored procedure (inside the database’s LaserFiche repository schema) was being executed by a number of client processes (the client was lfs.exe). The procedure in question is named search_entry_in_subfolders. It receives 4 parameters, one of which being a TOCID representing a “position” in the LF folders tree. For us this was the top-level folder of all the invoices (all years, all customers, etc) and this folder comprises about 2 million invoices. So the procedure search_entry_in_subfolders was loading all of the TOCID’s of all the invoices into the tempresult table. And I was seeing many (about 10) processes doing the same thing at the same moment.
The effect of this was
- lots of CPU involved on the db server, with each process using 100% of one CPU (we have 4 CPUs...) so that exceeded the server’s capacity as each would take 5-6 minutes to complete.
- lots of transactions logs created as the tempresult table was growing a lot with millions of records inserted and eventually deleted (this is a temporary table). This resulted in a few hundreds redo log switches per hour compared to an average of just 10 normally.
So obviously, something has changed in LF 10. I found out the procedure search_entry_in_subfolders already existed in version 9, but was NEVER used. I know, because I do not experience the problems stated above, but also because since I reverted back to version 9 and added some code to the procedure for it to write in a log whenever it’s executed… it has written nothing. I could just as well drop the procedure and LaserFiche / WorkFlow would continue without knowing.
So I’d like to know what type of search, or what activity in a LaserFiche WorkFlow gets this procedure to execute (and why, maybe) as I will retry the upgrade and would very much like to avoid this same situation. Maybe we have to change the way some WorkFlow process does things. I don’t know what to look for as we clearly don’t have any search in there that would intentionally return all of the invoices. We search for specific documents or folders to take a decision (create or replace or delete) but why would we search all of the invoices ?
So if you have more information about search_entry_in_subfolders, when it is called and why this behavior is changed from LaserFiche 9, I’m interested to know. If we know what triggers it we may be able to change the WorkFlows beforehand and avoid trouble on the day we do the upgrade to 10.1 again. Certainly, search_entry_in_subfolders is tied to some specific activity that we can look for in our WorkFlow designs.
BTW : There is a "sister" procedure that's also executed whenever this one is, and the name is TRY_search_entry_in_Subfolders. This last one is new to LF 10.1. No trace of it in 9.2.Thank you