Inefficient Query Generated when Paging is Enabled on Entity Collection

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

Answer 1

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]