0

I am calling SQL Server 10 from Entity Framework in C# and want to get a query hint into the request. The database has indexes which operated normally from SQL run in Management Studio, but when calling the command from C# using Entity Framework in Visual Studio, the query planner chooses a full scan when there is already an index.

I am creating dynamic predicates to request data in the following form:

data.attributeText = data.vegaDB.attributeText.AsExpandable().Where(parentPredicate.Compile()).ToList();

where parentPredicate is dynamically generated equivalent of: 

(parentID = p1) AND (attributeName = 'name OR ... ')

From which the SQL Server query plan generates:

SELECT 
[Extent1].[attributeID] AS [attributeID],  
[Extent1].[parentID] AS [parentID],  
[Extent1].[typeID] AS [typeID],  
[Extent1].[attributeName] AS [attributeName],  
[Extent1].[attributeData] AS [attributeData]  
FROM [dbo].[attributeText] AS [Extent1]

So replacing the [Extent1] with the index [IX_parentID], which the direct sql call uses, by some extra command which does a query hint in the initial c# call would seem the solution. I have had a look around but no success yet. Any idea how to frame the question?

Do u think this is the right solution?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Simon
  • 1
  • 2
  • 4
  • 2
    If the query optimizer picks a full table scan, it might have a good reason to do so; the table might be too small to matter, the index might not be selective enough, statistics might be out of date...... I would investigate the **why** rather than just bluntly forcing a query hint..... – marc_s Mar 01 '11 at 17:38
  • I have run query analyser as suggested in http://stackoverflow.com/questions/117301/how-does-including-a-sql-index-hint-affect-query-performance and it was happy with the setup – Simon Mar 01 '11 at 17:39
  • the sql command will choose a full scan instead of the index if the index will not improve the search method more than 30% – Majd Mar 01 '11 at 17:47

2 Answers2

0

Try updating statistics for the related tables in your database, if statistics are outdated non-optimal query plans are likely to be used for all queries.

Mahol25
  • 3,131
  • 2
  • 22
  • 20
0

Run an SQL trace to find out what SQL query is actually being generated for this statement.

Does your predicate or an equivalent actually appear in the query as seen by SQL Server?

If it does appear, then you need to run that query through the Index Tuning wizard or similar.

If not, that's your problem - that would mean that Entity Framework is loading the entire table into memory and applying the predicate itself.

Updated I am pretty sure that this last is exactly what is happening: The AsExpandable() is failing to translate your predicate to SQL, so it is generating code to read the entire table, then applying the predicate to the returned data.

The solution is to stop using AsExpandable and use AsQueryable instead. The only reason to use AsExpandable is if AsQueryable doesn't offer the functionality you need, and I don't think that's the case here.

Ben
  • 34,935
  • 6
  • 74
  • 113
  • you are right in that AsExpandable is pulling up the table into memory, my misunderstanding. I have changed to AsQueryable and the memory usage is much less, although it still doesn't pick up the index. I have tried using a dummy dynamic predicate to ensure there is no coding bugs and to simplify the query to the following: parentPredicate = (p => p.parentID.Equals(100000)); data.attributeText = data.vegaDB.attributeText.AsQueryable().Where(parentPredicate.Compile()).ToList(­); SQL trace is still calling SELECT [Extent1].[attributeID] AS [attributeID], etc as above – Simon Mar 15 '11 at 18:55
  • So there is still no "where" clause in the query as generated? – Ben Mar 15 '11 at 21:10
  • Ben, thanks for your help on this, much appreitated. Needed to switch to AsQueryable and from LinqKit to [this dynamic predicate system](http://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/) as seems entity framework has issues when the .invoke() call used when LinqKit is built by EF using .compile(). So when the call is altered it works great: data.attributeText = data.vegaDB.attributeText.AsQueryable().Where(parentPredicate).ToList(); ie drop the .Compile(). I have no idea what is going on under the hood with all of this, but it works! – Simon Mar 16 '11 at 08:55