主页 / dba / 问题 / 98308
Accepted
drex drex
Asked: 2015-04-21 05:01:37 +0800 CST

SQL Server 避免过程参数嗅探

  • 772

我从这个过程中得到缓慢的表现

CREATE PROCEDURE MyProcedure01
  @date datetime2(7)
  AS
BEGIN
SELECT 
[Limit1].[C2] AS [C1], 
[Limit1].[mc_object] AS [mc_object], 
[Limit1].[C1] AS [C2]
FROM ( SELECT TOP (50) 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[K1] AS [mc_object], 
    1 AS [C2]
    FROM ( SELECT 
        [Extent1].[mc_object] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[BEM_EVT_FULL] AS [Extent1]
        WHERE [Extent1].[date_reception] > @date
        GROUP BY [Extent1].[mc_object]
    )  AS [GroupBy1]
)  AS [Limit1]
END

在做了一些搜索之后，我发现如果以这种方式声明变量，我们将解决参数嗅探问题。

CREATE PROCEDURE MyProcedure02
  @date datetime2(7)
  AS
BEGIN
DECLARE @myDate datetime2(7)
 SET @myDate = @date
 SELECT 
[Limit1].[C2] AS [C1], 
[Limit1].[mc_object] AS [mc_object], 
[Limit1].[C1] AS [C2]
FROM ( SELECT TOP (50) 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[K1] AS [mc_object], 
    1 AS [C2]
    FROM ( SELECT 
        [Extent1].[mc_object] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[BEM_EVT_FULL] AS [Extent1]
        WHERE [Extent1].[date_reception] > @myDate
        GROUP BY [Extent1].[mc_object]
    )  AS [GroupBy1]
)  AS [Limit1]
END

但不幸的是结果仍然很慢我能知道我是否遗漏了什么吗

sql-server entity-framework

2 个回答

  1. Best Answer

    说真的，停止使用 Entity Framework。它非常适合搭建快速概念验证的脚手架，但就生产系统而言，它是垃圾。

    你也有一个SELECT TOP 50没有订单。您希望返回结果的顺序是什么？是什么TOP？就目前而言，您可以按任何顺序获得结果

    无论如何，首先要重写您的查询

    SELECT TOP 50 
  1 AS [C2], 
  mc_object, 
  COUNT(1) AS [C1]
FROM [dbo].[BEM_EVT_FULL] 
WHERE date_reception > @myDate 
GROUP BY mc_object
ORDER BY C1 DESC; -- Added an order

    确保date_reception有一个索引包括mc_object：

    CREATE NONCLUSTERED INDEX idx_bemevtfull_datereception 
ON dbo.BEM_EVT_FULL(date_reception) 
INCLUDE (mc_object);
    • 4
    1. 在查询中直接使用 proc 参数 (@date)
    2. 如果您的表中的数据分布不均匀，当已在缓存中的计划用于当前执行时，参数嗅探可能会损害性能，该计划具有不同的参数值，并且该参数值的数据分布截然不同。比如说，5 行与 1000 行。

    克服此问题的方法之一是对 proc 内的特定查询使用选项（重新编译），该查询由于参数嗅探而导致性能问题。

    • -1

相关问题