BDC lookup times out

Question

I am trying to implement a BDC lookup in a silverlight application connecting to SharePoint via WCF. I have the lookup query working using the FindFiltered method, but when we remove the throttling at 100 rows (to allow all 5500 rows to return) the query times out.

When I run the query (which I see via the sql profiler) against SQL directly the results come back in less than a second. Why does this take more than 60 seconds via a BDC query? Is there any way to speed it up? Or do I need to bypass BDC and just query the sql directly?

Here is my query goodness:

var entity = catalog.GetEntity(bdcField.EntityNamespace, bdcField.EntityName);
ILobSystemInstance lob = entity.GetLobSystem().GetLobSystemInstances()[0].Value;

var findFilters = entity.GetDefaultFinderFilters();
var filtered = entity.FindFiltered(findFilters, lob);
var dt = new DataTable();

IFieldCollection fields = entity.GetDefaultSpecificFinderView().Fields;
foreach (var fld in fields)
    dt.Columns.Add(fld.Name, Type.GetType(fld.TypeDescriptor.InterpretedTypeName));

while (filtered.MoveNext())
{
    var newRow = dt.NewRow();
    foreach (var f in fields)
        newRow[f.Name] = filtered.Current[f.Name];

    dt.Rows.Add(newRow);
}

Answer 1

You mentioned that you have upped the row limit throttle, but have you also looked at the max WCF data throttle? By default that is set at 3MB (i think). You might be hitting that?

You can use Set-SPBusinessDataCatalogThrottleConfig to change those throttles ... but be aware they were set at those defaults for a reason :)