Are there any SQL database settings which are recommended for PI-AF databases which have large collections. I am getting timeouts when trying to access AFElement.Elements. I have already limited the tempdb file sizes to a max of 500MB.
I don't think that I would limit the size of the tempdb, since AF makes extensive use of tempdb. I would make sure that the RAM allocated to the SQL Server service is at least 60% of the size of the PIFD database. I recommend limiting the size of each AFElement.Elements collection to a few tens of thousands of elements and to avoid circular references.
by recommending limiting the size of Elements collection here you mean the number of child Elements at each level of the tree?
In other words we can have millions of Elements, but important not to have them all just as a flat list to Root - is my understanding correct?
Yes, millions of Elements are supported, with appropriate hardware resources, but it is recommended to not have a flat list with millions of Elements with the same parent element.
Understood, thanks for clarification Paul
TempDB should be large enough to support reindexing the largest index in all databases for the entire SQL Server instance. On servers with a lot of activity and many cores, it may be desirable to add additional files to tempDB. In some cases, putting TempDB on a SSD may improve your performance. It is important to benchmark before and after each change.
Retrieving data ...