This was how I originally setup the workflow, however if returning 100 or more entries the search timeout is reached and the workflow is canceled. I think the timeout is at about 4-5 hours. Even if the timeout could be removed it could take a very long time for every entry referenced in the database to be returned. Then, once it is returned you have one shot at it, if there is any problems routing you will have to initiate the search all over again.

A SQL query by ID and document type should be very quick regardless of the size of the database. I am going to test soon.

If you took this approach I think you'd want to do a customer DB query that returned the next ### of results that were not folders as a multivalue token then cycle through a loop of that value and kick off another workflow for each one. 

If your system is so large that it times out I would want to have workflow working on multiple documents at once. If you did it all in one workflow you'd only have one continuing thread for it instead of 4 threads per CPU. You could use the same process of setting up a control file to store the last index value inside your table you create (I'm assuming this table would be something you created through SQL directly, not by a search in laserfiche) so you could easily add an index value to it. The trick is setting the right ### of results so that every time it kicks off it's finished the previous batch. Depending on overhead during the day you could potentially lower that ### to something smaller and continue to have it running during the day. Since you are not doing searches with this method the overhead wouldn't be as bad.

One other thing to keep in mind with this is your workflow database. You may want to go into advance server options and choose to turn off all tracking options for the actual filing workflow. It would normally record a new entry for each and every workflow that ran - which would cause the workflow db to grow enormously and potentially slow down your SQL server. I don't think you can turn this off completely but you can turn off all messages inside it so that the amount of data it writes per workflow session is very small. 

Good to know, I turned off logging. I just finished timing how long it takes to run on 1,000 entries. About 30 minutes. If we run 10 hour nightly sessions we will have everything migrated in 1 year. I would like to reduce that time down to 1 month but that would require 12X the performance.
 

1200ms out of 1400ms total per workflow time is in the lookups which reach out to an external SQL server.

I am not sure it is the external database that has the performance problem though, the lookup activity appears to have a minimum of around 500ms.

I have two lookup activities in the workflow, one does a lookup on a very large table and the other does a lookup on a table with only 20 items in it and 2 columns. In studio this query takes less than 1ms. Both lookup activites take a bit over 500ms,. This may be a an authentication and initial connection to the engine.

Edit: I am able to run 1000 simultaneous workflows but that took just as long, all the lookups hung up for some time.

Are you able to run this on weekends? A full weekend of time can help increase your timeline. 

Anyways, have you tried actually running the move activity in the workflow? This might take a lot longer than you expect in your estimates.

Also, Are you able to use the SDK? I think you might have better performance running through all these files and moving them when you take out any overhead that workflow might introduce. Maybe even using 9.1 might help you in your performance crisis.

It is interesting that you have figured out your bottleneck. You really do not want to run that many simultaneous workflows if you are using searches and SQL lookups. Can you explain how you plan to go through all the files? Can you not set up some sort of "automatic filer" workflow where you drag in a collection of files to a folder on the root and the workflow act on each one of the files within? 

How many entries are we dealing with?

I have two lookup activities in the workflow, one does a lookup on a very large table and the other does a lookup on a table with only 20 items in it and 2 columns. In studio this query takes less than 1ms. Both lookup activites take a bit over 500ms,. This may be a an authentication and initial connection to the engine.

With only 20 items in 2 columns I would think you could write a workflow process with all the possible values hard coded. It would take some time but then it wouldn't have to do this lookup. That would save quite a bit of time. You have hit the nail on the head with the 500 ms limit - that is roughly how long it takes to setup and teardown the connections to another table.  

One possibility with all of this is the fact that you can run multiple workflow servers if you need to on multiple machines. . I'm not sure if that would be compatible with what you are trying to accomplish or not as you may be running into SQL issues at that point - to truly help with this you'd need multiple copies of that third party sql db with their own server too. 

Seeing all of your posts about the time and how long it's going to take I'm wondering if there's another angle that could be used? Do you have the SDK and some coding skills? (if you can code but don't have the SDK you should consider it, it's really reasonable)

So on my original plan up above I was totally thinking of a simple look at the metadata and file it away workflow for each doc. but since you need to do lookups obviously you can't go crazy with how many it processes at once. 

I now wonder if you could pass the multivalue token that contains all of the found entries to a sub workflow #2 as an input parameter that then is used in a SDK script. That SDK script then makes one connection to your DB programatically and either moves the document to it's final place (i.e. you do all of the filing workflow functions via script) or it fills in 2 temp fields on that document and tags the document as 'ready for filing'. If using the temp field/tag option setting that tag then causes subscriber to kick off workflow #3 that actually files it away using those temp fields so there is no lookup that has to happen (and then removes the temp fields)

I would think you could get more throughput with this method as there would not be a setup and tear down of the connection to the database every entry. There would be a bit of a lag invoking the SDK but not much. You could also do a trick to have it work on more than one of these lists at once by using 

token 1 = starting entry

token 2 = start +100     ---  search - then pass to  invoke workflow #2 (don't wait)

token3 = token1+100

token4 = token2+100  ---  search - then pass to invoke workflow #2 (don't wait)

token 5 = token3+100

token6 = token4+100  --  search - then pass to invoke workflow #2 (don't wait)

This way you could have 3 different sdk scripts running at a time 

If you eliminate the lookup piece it should cycle through the filing parts of the workflows really fast. You can code all of your logic in your sdk for filing to speed it up even more but if it's complex with lots of decision trees  that you've already created it may not be worth the time since these workflows should fly. It would be faster to do this via an invoke and pass the results as input tokens but I'm not sure how you invoke with input parameters in workflow sdk. (It looks like the SDK hasn't been updated since 8.3?)

Thank you for all the updates. I did come to a similar conclusion this morning. I was able to eliminate both lookups using scripts with case statements and some pattern matches. Now that the lookups are gone I can run 1000 instances simultaneously and be done in about 2 minutes. It seems the more I run simultaneously the more routes I can squeeze into a single minute but I am not sure how to solve for the optimal amount. This is only a dual core server.