Since starting AF SDK development a few months ago I have been generally happy with the SDK, it's performance, and especially the thorough documentation. However, I am now hitting some prohibitively serious performance issues when using the FindElements method and other related methods. The searches I am trying to run in code typically target a group of 2,000 - 3,000 elements and I have tried to search by template, category, and by filtering on name, and in the majority of cases these call times out (after 10 mins).
I am profiling the calls made to the SQL Server and have identified the following stored procedures as being the cause of the time-outs:
Looking at the T-SQL in these procedures they seem to build up complex queries in dynamic SQL and then execute them. Watching the activity on the SQL Server the process is spending most of it's time in a "Suspended" state with a wait type of "SLEEP_TASK" which I am not familiar with and which Microsoft have not documented particularly well.
Has anyone else experienced similar problems?
Our AF versions are as follows:
Server:184.108.40.20624; Database:220.127.116.1124 (We have the AF Service and the PIFD database on separate servers)