sql-server - 输入参数会影响 SP 性能吗？

Question

我刚刚创建了这个存储过程，它有inner join两个表：MyTable和MyTable2. 这些表中的每一个都有 5M+ 行。

显然，只有当SP 接收到 @from 和 @to 作为输入参数时，inner join才会花费 3 分钟。这是我原来的SP：

aLTER procedure usp_Weird
(      
 @from date,      
 @To date      
)      
as        
--declare @from date = cast(dateadd(day, -5, getdate()) as date)
--declare @to date = cast(dateadd(day, -1, getdate()) as date)
      
      
IF OBJECT_ID('tempdb.dbo.#daily') IS NOT NULL DROP TABLE #daily;       
      
;with cte as      
(      
 SELECT  cast( MyTable.DATETIME as date) AS DATE_Final
   FROM       
   MyTable INNER JOIN MyTable2 
   ON MyTable.DATETIME = MyTable2.DATETIME AND MyTable.Id = MyTable2.Id
  WHERE MyTable.DATETIME BETWEEN @From AND @to      
   AND MyTable2.DATETIME BETWEEN @From AND @to      
) select count(*) from cte 

以下脚本需要 3 多分钟才能完成：

declare @from date = cast(dateadd(day, -5, getdate()) as date)
declare @to date = cast(dateadd(day, -1, getdate()) as date)
exec usp_weird @from, @to;

所以我只是对输入参数进行了注释，并在 SP 中声明了它们，如下所示：

aLTER procedure usp_Weird
--(      
--@from date,      
--@To date      
--)      
as        
declare @from date = cast(dateadd(day, -5, getdate()) as date)
declare @to date = cast(dateadd(day, -1, getdate()) as date)
      
      
IF OBJECT_ID('tempdb.dbo.#daily') IS NOT NULL DROP TABLE #daily;       
      
;with cte as      
(      
 SELECT  cast( MyTable.DATETIME as date) AS DATE_Final
   FROM       
   MyTable INNER JOIN MyTable2 
   ON MyTable.DATETIME = MyTable2.DATETIME AND MyTable.Id = MyTable2.Id
  WHERE MyTable.DATETIME BETWEEN @From AND @to      
   AND MyTable2.DATETIME BETWEEN @From AND @to      
) select count(*) from cte 

以下每次运行不到一秒：

declare @from date = cast(dateadd(day, -5, getdate()) as date)
declare @to date = cast(dateadd(day, -1, getdate()) as date)

exec usp_weird; -- I'm not using @from and @to declared above

现在，如果我消除了 INNER JOIN，两个版本的 SP 都会运行得非常快，这让我相信问题在于内部连接和输入参数的组合。

我的问题是：当存储过程以日期作为参数运行时，为什么会INNER JOIN慢得多？

将日期作为参数或在 SP 中声明有什么区别？

我也下载了sp_whoisactive，结果如下：

使用输入参数执行 SP：

dd hh:mm:ss.mss: 00 00:02:41.820
wait_info: NULL
CPU: 161,821
reads: 10,713,290           

不带输入参数执行 SP：

dd hh:mm:ss.mss: 00 00:00:00.026
wait_info: (7ms)CXCONSUMER
CPU: 97
reads: 53

DATETIME更新：我添加了MyTable2的非聚集索引ID。现在两个 SP 都同样快。

但是这并不能解释为什么这种性能缺陷仅在使用参数调用 SP 时才明显。

标签： sql-servertsqlstored-procedures