I have a database that I want to display the content in SharePoint 2010. I am not displaying the content in a list but am using a custom WebPart.
The database is not updated any more as it is historical data. The web part is just a way to query and view the data.
My initial idea was to connect to the DB via BCS. Expose the content as a list. Then query the list in my web part to return data. The problem is that it is slow (15-20 seconds). One of the tables contains ~2,000,000 items. If I query the DB directly the results return in less than a second.
I have tried quering the data using:
SPlist CustomerList = Web.TryGetList("Customers"); var FindCustomer = from SPListItem Item in CustomersList.Items where Item["Orders"] as int == 5 select Item;
This of course is loading the whole list into memory then filtering it there. Would a CAML query reduce my times?
Or in this scenario would I be better reading the DB directly then trying to go through the SharePoint object model?
Are there any best practise with working with large external lists?