Returning Large Query Results Into Collections

As a rule of thumb, performance implications for loading collections and performing lookups will become more evident if you have collections with more than 1000 members - but there is hope. 

If you experience a MaxCycleCount error - the Timeout and Max Cycle Count settings can be increased in the Execution Settings tab on the top level RuleApp node in irAuthor.  This may be all you need if you don't mind your execution being a long running process.  

Other options to remedy:

• For large collections - change the collection type from Entity to Complex.  It is the creation of thousands of entity contexts that is taking up the majority of the time.  This is a good solution when you are loading a collection for aggregate and lookup purposes.  Not so good for collections that need member level rules associated to them
• Load the collection from the SDK vs using SQLToCollection - while the versatility of SQLToCollection is a big boost, once you get into 1000s of collection members you may need to consider leveraging the external architecture to do some of the heavy lifting.  Loading a collection from the SDK along with the rest of the entity state is an order of magnitude quicker than the SQLToCollection.  You also have options to interrogate ruleapp metadata if the query needs to be more dynamic in nature.
• Another possible cause for the extended time for SQLToCollection is if you have rules firing for each member that is being loaded.  If that is the case, you want to make those rules as efficient as possible.