I am creating a custom web part which is supposed to return results from an External Content Type(ECT) List which contains almost 40,000 records. There are multiple ways the items can be searched and all of that works fine except it is very slow.
The search is performed by using a specific SPQuery on the ECT List but it seems to take a consistent 20 seconds per query, even when returning just a few results.
Upon further investigation(through SQL Profiler) it seems as if the entire list is being queried each time then sifted through with SPQuery even with specific CAML queries. Is there any way to get around returning the entire list, perhaps accessing filters defined in the ECT list when it is created?
P.S. using SharePoint 2010, SharePoint Design 2010, Visual Studio 2010, C#, in a Visual Web Part
Thanks in advance for any help!