如何防止Entity Framework在SQL Server中生成效率低下的查询？ [英] How can I keep Entity Framework from generating inefficient queries in SQL Server?

问题描述

我有一个基于视图的EF 4.0中定义的实体。该视图位于包含约1800万行数据的表上。我选择了实体的4个确定性属性作为该实体的复合实体键。我已经通过OData WCF数据服务公开了包含此视图的模型的访问。 WCF数据服务设置为限制结果，例如

I have an entity defined in an EF 4.0 that is based on a view. The view lays over a table with about 18 million rows of data. I have selected the 4 deterministic properties of the entity as a compound Entity key for this entity. I have exposed access to the model containing this view via an OData WCF data service. The WCF Data service is set to limit results like so

config.SetEntitySetPageSize("*", 100)

当我对此视图进行基本查询时：

When I make a basic query against this view:

var fcbs = this.iBenchmarkCostContext.FtCostBenchmarks.ToArray();

这是我在配置工具中看到的查询：

This is the query that I see in my profiling tool:

SELECT TOP (100) [Extent1].[MonthBeginDt]                     AS [MonthBeginDt],
                 [Extent1].[dmCostBenchmarkKey]               AS [dmCostBenchmarkKey],
                 [Extent1].[dmProductKey]                     AS [dmProductKey],
                 [Extent1].[IsImputedFlg]                     AS [IsImputedFlg],
                 [Extent1].[ProjectedCopayPerRxAmt]           AS [ProjectedCopayPerRxAmt],
                 [Extent1].[ProjectedPricePerQtyAmt]          AS [ProjectedPricePerQtyAmt],
                 [Extent1].[ProjectedQtyPerRxQty]             AS [ProjectedQtyPerRxQty],
                 [Extent1].[ProjectedRxCnt]                   AS [ProjectedRxCnt],
                 [Extent1].[AvgPriceRxAvgPriceQtyDenominator] AS [AvgPriceRxAvgPriceQtyDenominator],
                 [Extent1].[AvgQtyDenominator]                AS [AvgQtyDenominator],
                 [Extent1].[AvgCopayDenominator]              AS [AvgCopayDenominator],
                 [Extent1].[ProjectedTotalCostAmt]            AS [ProjectedTotalCostAmt],
                 [Extent1].[AllowedRxCnt]                     AS [AllowedRxCnt],
                 [Extent1].[CopayRxCnt]                       AS [CopayRxCnt],
                 [Extent1].[TotalAllowedAmt]                  AS [TotalAllowedAmt],
                 [Extent1].[TotalCopayAmt]                    AS [TotalCopayAmt],
                 [Extent1].[TotalCostPerUnitAmt]              AS [TotalCostPerUnitAmt],
                 [Extent1].[TotalUnitQty]                     AS [TotalUnitQty],
                 [Extent1].[RC]                               AS [RC]
FROM   (SELECT [ftCostBenchmark].[MonthBeginDt]                     AS [MonthBeginDt],
               [ftCostBenchmark].[dmCostBenchmarkKey]               AS [dmCostBenchmarkKey],
               [ftCostBenchmark].[dmProductKey]                     AS [dmProductKey],
               [ftCostBenchmark].[IsImputedFlg]                     AS [IsImputedFlg],
               [ftCostBenchmark].[ProjectedCopayPerRxAmt]           AS [ProjectedCopayPerRxAmt],
               [ftCostBenchmark].[ProjectedPricePerQtyAmt]          AS [ProjectedPricePerQtyAmt],
               [ftCostBenchmark].[ProjectedQtyPerRxQty]             AS [ProjectedQtyPerRxQty],
               [ftCostBenchmark].[ProjectedRxCnt]                   AS [ProjectedRxCnt],
               [ftCostBenchmark].[AvgPriceRxAvgPriceQtyDenominator] AS [AvgPriceRxAvgPriceQtyDenominator],
               [ftCostBenchmark].[AvgQtyDenominator]                AS [AvgQtyDenominator],
               [ftCostBenchmark].[AvgCopayDenominator]              AS [AvgCopayDenominator],
               [ftCostBenchmark].[ProjectedTotalCostAmt]            AS [ProjectedTotalCostAmt],
               [ftCostBenchmark].[AllowedRxCnt]                     AS [AllowedRxCnt],
               [ftCostBenchmark].[CopayRxCnt]                       AS [CopayRxCnt],
               [ftCostBenchmark].[TotalAllowedAmt]                  AS [TotalAllowedAmt],
               [ftCostBenchmark].[TotalCopayAmt]                    AS [TotalCopayAmt],
               [ftCostBenchmark].[TotalCostPerUnitAmt]              AS [TotalCostPerUnitAmt],
               [ftCostBenchmark].[TotalUnitQty]                     AS [TotalUnitQty],
               [ftCostBenchmark].[RC]                               AS [RC]
        FROM   [dbo].[ftCostBenchmark] AS [ftCostBenchmark]) AS [Extent1]
ORDER  BY [Extent1].[MonthBeginDt] ASC,
          [Extent1].[dmCostBenchmarkKey] ASC,
          [Extent1].[dmProductKey] ASC,
          [Extent1].[IsImputedFlg] ASC

虽然我没有明确要求任何订购，将添加er by子句，其中包括为该实体定义的复合实体关键字中包含的字段。执行此查询需要花费大量时间，并在数据库上生成页锁。在SQL Server环境中从查询中删除 ORDER BY 会在不到一毫秒的时间内返回结果。

Although I have not explicitly requested any ordering, an order by clause is added that includes the fields included in the compound entity key defined for the Entity. Execution of this query takes an inordinate amount of time and generates page locks on the database. Removal of the ORDER BY from the query in the SQL Server environment returns results in less than a milisecond.

因此我的问题是：

如何阻止EF将order by子句添加到查询中？

How can I stop EF from adding that order by clause to the query?

推荐答案

如果'order by'子句中各列的名称和顺序一致，则用户可能希望获得此结果。

If there is consistency in the names and order of the columns in the 'order by' clause, there is likely desirability for this result by the users.

只需在表上放置索引，或修改主键，以便可以在检索中使用索引。

Just put an index on the table, or modify the primary key, so that index can be used in the retrieval.

create index x on [dbo].[ftCostBenchmark]
                      ([MonthBeginDt],
                       [dmCostBenchmarkKey],
                       [dmProductKey],
                       [IsImputedFlg])
                  include
                       (existing-primarykey-on-the-table)

奇怪的是，查询中没有where子句。如果您为简洁起见修剪了一个子句，请将这些字段放在索引的第一位。

Odd that there is no where clause on the query. If you trimmed a where-clause for brevity, put those fields first in the index.

这篇关于如何防止Entity Framework在SQL Server中生成效率低下的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！