locked
Inefficient Query Generated when Paging is Enabled on Entity Collection RRS feed

  • Question

  • I have the following set in my WCF Data Service (June CTP)

    config.SetEntitySetPageSize("WorkItems", 500);
    config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
     
    WorkItems is a table with a primary key WorkItemId, and a nvarchar(255) column OwnerName. There is an index on OwnerName.
     If I turn paging on the WorkItems collection, and execute the following query:
     
    http://localhost:6000/WorkItems?$filter=startswith(OwnerName,'JO')
    the query that is generated looks like:
     
    SELECT * from WorkItem
     WHERE [Extent1].[OwnerName] LIKE N'JO%'
     ORDER BY [Extent1].[WorkItemId] ASC
     
    Which, because it includes the WorkItemId in the ORDER BY clause, forces SqlServer to ignore the index on the OwnerName.
     
    Even if I execute a query with an orderby in it (http://localhost:6000/WorkItems?orderby=OwnerName&$filter=startswith(OwnerName,'J')), I still get the same inefficient query generated.
     
    Is there a way to control how the paging is executed so it produces more efficient Sql queries?
     
    Thanks in advance,
     
    Jonathan


    Thursday, July 14, 2011 2:37 PM

All replies

  • Hi,

    My contact in the EF team tells me that EF has no way to influence the decision process of the SQL Query Optimizer, so I don't think there's a way to "fix" this. It might be due to table statistics, or something like that. But I'm no SQL expert, so I'm just guessing.

    Maybe you can try to post this question to EF or SQL Server forum to get more ideas.

    From WCF DS point of view, we just generate a LINQ query like:

    WorkItems.Where(e => e.OwnerName.StartsWith("JO")).OrderBy(e.WorkItemId).Top(500)

    and execute it.

    Thanks,


    Vitek Karas [MSFT]
    Tuesday, July 19, 2011 5:33 PM
    Moderator