强制提示从实体框架索引到 SQL Server
我正在 C# 中从实体框架调用 SQL Server 10，并希望在请求中获取查询提示。数据库具有在 Management Studio 中运行的 SQL 中正常运行的索引，但当使用 Visual Studio 中的实体框架从 C# 调用命令时，查询计划程序会在已有索引时选择完整扫描。
我正在创建动态谓词来请求以下形式的数据：
data.attributeText = data.vegaDB.attributeText.AsExpandable().Where(parentPredicate.Compile()).ToList();
其中 parentPredicate 是动态生成的，相当于：
(parentID = p1) AND (attributeName = 'name OR ... ')
SQL Server 查询计划从中生成：
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]
因此替换 [Extent1]使用直接 sql 调用使用的索引 [IX_parentID]，通过一些额外的命令在初始 c# 调用中执行查询提示似乎是解决方案。我环顾四周但还没有成功。知道如何提出这个问题吗？
您认为这是正确的解决方案吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
运行 SQL 跟踪以找出该语句实际生成的 SQL 查询。
正如 SQL Server 所见，您的谓词或等效谓词实际上出现在查询中吗？
如果确实出现，那么您需要通过索引调整向导或类似向导运行该查询。
如果没有，那就是你的问题 - 这意味着实体框架正在将整个表加载到内存中并应用谓词本身。
已更新 我非常确定最后这正是发生的情况：AsExpandable() 无法将您的谓词转换为 SQL，因此它会生成代码来读取整个表，然后将谓词应用到返回的数据。
解决方案是停止使用 AsExpandable 并使用 AsQueryable 代替。使用 AsExpandable 的唯一原因是 AsQueryable 不能提供您需要的功能，而我认为这里不是这种情况。
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.
尝试更新数据库中相关表的统计信息，如果统计信息已过时，则所有查询都可能使用非最佳查询计划。
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.