我正在使用一个存储过程来返回一组关于varbinary列中的数据的行。我一生都无法弄清楚为什么当我使用sp_executesql
而不是exec
时,查询时间总是以相同的速度增加。
查询时间sp_executesql
:<1s,1s,2s,4s,8s,24s(等),直到最终问题"消失"几分钟,查询始终为~0.006s
exec
的查询时间始终为 <500 毫秒。
起初,我认为这是 Linq2Sql 的问题,但是当我们运行 Linq 在 SQL Management Studio 中生成的查询时,我们得到了相同的结果。
这是sproc的正文(严格审查)
请注意,未拉取varbinary
列
select DF.FileID,
DF.DerpFkGuid,
DF.DerpName,
DF.[FileName],
DF.FileSize as 'FileSizeBytes',
ISNULL(DFA.File_Size_Bytes_String,'Unknown') as 'FileSizeFriendly',
CONVERT(nvarchar(30),DF.FoundDate,120) as 'FileDateUploaded',
CONVERT(nvarchar(30),DF.FileDateCreated,120) as 'FileDateCreated',
CONVERT(nvarchar(30),DF.FileDateLastModified,120) as 'FileDateModified',
CASE WHEN ISNULL(dfa.Derp_ID,'00000000-0000-0000-0000-000000000000')='00000000-0000-0000-0000-000000000000' THEN 0 ELSE 1 END as 'AttachedToDerp',
ISNULL(dfa.Derp_ID,'{00000000-0000-0000-0000-000000000000}') as 'Derp_ID'
from DerpFiles DF
inner join DFDerpDerp DFdd on DFdd.DerpName = DF.DerpName
left outer join Derp_Files_Attachments dfa on dfa.FileID = DF.FileID
where WR.UserName = @UserName and DF.DuplicateFileDetected=0
这是持续运行<500 毫秒的代码
USE [DerpDatabase]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[DerpSproc]
@UserName = N'derp',
@DomainName = N'derp'
SELECT 'Return Value' = @return_value
GO
这是运行 1 秒、2 秒、4秒、8 秒、24 秒等的代码
declare @p5 int
set @p5=0
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[DerpSproc]
@UserName = @p0,
@DomainName = @p1',
N'@p0 nvarchar(4000),
@p1 nvarchar(4000),
@RETURN_VALUE int output',
@p0=N'derp',
@p1=N'derp',
@RETURN_VALUE=@p5 output
select @p5
据我所知,调用 sprocs 的 2 种方式本质上是相同的。唯一的区别是sp_executesql
与exec
看起来exec
将产生相同的增加查询时间,而末尾没有go
关键字。我无法确定,因为我不知道当我尝试添加go
时查询是否恰好发生在缓存中?
这可能是参数嗅探问题。有时,当您将参数传递到过程中时,SQL 很难弄清楚如何有效地使用它们。尝试在过程中将参数设置为局部变量,然后在 where 子句中使用局部变量。
参数嗅探
我们通过将 VARBINARY
字段从文件表移动到其自己的专用表来解决我们的问题。我们无法在文件表和元数据表之间添加适当的关系,而不会使查询时间达到顶峰。
仅当我们将查询作为存储过程执行时,才会发生这种情况。当我们对过滤器执行与具有 WHERE
子句的视图完全相同的查询时,不会发生这种情况。我们温和的理论是,SQL Server 2012 有一个错误(?),它会导致我们联接的表中的VARBINARY
字段在我们的查询中被读取,即使它不包含在我们的 SELECT
语句中。