Working with large external lists using BCS

Question

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?

Russell · Accepted Answer · 2011-12-21 10:45:32Z

I would advise doing any joins/where statements in a SQL Stored procedure and using views to make your query faster. Keep the code inside your web part as simple as possible and toward displaying the data, rather than manipulating it.

While the Linq to SQL will create a SQL query, it may not be optimised to perform well. At least by putting a stored procedure in the middle, you have control over the performance of the query.

Depending on your requirements, you can create an external list pointing to your stored procedure, and pass it parameters to filter as required. This may simplify your application implementation.

justforkix09 · Accepted Answer · 2011-12-21 12:35:53Z

Two things here which are affecting performance adversely.

BCS is service application, therefore some WCF service calls are involved
SPList.Items loads all the items of the list before your list before LINQ filters it. So this approach should not be used on large lists. Using CAML will be better.

Given your scenario I think it's best you query the database directly through custom code (writing Stored procedures for filtering and returning data would be best). BCS in your case is unnecessary overhead unless you have more reasons for having the data in list format.

Joachim Rosskopf · Accepted Answer · 2011-12-30 19:48:11Z

I think the way to achieve a huge performance gain is not use the SPList and LINQ to the external list, but to define either a SpecificFinder-method for the corresponding SQL-query or to add an Filter to the Finder-method.

Then use the direct access to the BCS Metadata-Repository like described Browsing the Metadata Repository and Executing Methods and Filters on MSDN. The documentation is for MOSS 2007 but also applies for SP 2010.

To call a SpecificFinder use something like this:

NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance advWorksIns = sysInstances["AdventureWorksSampleInstance"];
Entity prodEntity = advWorksIns.GetEntities()["Product"];

//Find Product with ProductID = 1
IEntityInstance entityInstance = prodEntity.FindSpecific(1, advWorksIns);
foreach (Field f in prodEntity.GetSpecificFinderView().Fields)
{
    Console.Write(entityInstance[f]);
}

Sachin · Accepted Answer · 2011-12-20 16:08:22Z

I have dealt with these type of situations many a times during my public speakings and on forums and I would like reiterate the same thing here and that is Sharepoint is not a relational database for high performance data transactions. Partly because of the simplicity with which an end user can create a list with all types of columns and start filling it with data, people believe that Sharepoint stores its data just like the SQL Server database. However, believe me in most of the cases wherein the data goes into TB, it is best advised to go the old favourite way of writing sql stored procedures or display data using optimised queries from sql. If you dig deeper into the Sharepoint database you will find that all the data in Sharepoint is stored in a single table and indices are stored in another table which is then joined to the main table. Mind my words...its not RDBMS. The beauty of working with Sharepoint is that you are not bound to content database, you an use your own sql server or any database for that matter.

Thanks, Sachin

But the data that the OP is talking about is stored in a relational DBMS (with indexes etc). The OP is having trouble in the business layer in manipulating the data. This point there is still no SharePoint involvement. In this case SharePoint is purely the visual aspect of the application. — Russell
– Russell, Commented Dec 21, 2011 at 10:41

Stack Exchange Network

Working with large external lists using BCS

4 Answers 4

Your Answer

Hot Network Questions

Working with large external lists using BCS

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions