SQL Server：存储过程变得非常慢，原始SQL查询仍然非常快
孔波
问题内容：
问题答案：
我们正在为一个奇怪的问题而苦苦挣扎：当原始SQL执行得相当快时，存储过程将变得极其缓慢。
我们有
- SQL Server 2008 R2 Express Edition SP1 10.50.2500.0，上面有多个数据库。
- 一个数据库（大小约为747Mb）
- 一个存储过程，该过程采用不同的参数，并且确实从数据库中的多个表中进行选择。
代码：
ALTER Procedure [dbo].[spGetMovieShortDataList](
@MediaID int = null,
@Rfa nvarchar(8) = null,
@LicenseWindow nvarchar(8) = null,
@OwnerID uniqueidentifier = null,
@LicenseType nvarchar(max) = null,
@PriceGroupID uniqueidentifier = null,
@Format nvarchar(max) = null,
@GenreID uniqueidentifier = null,
@Title nvarchar(max) = null,
@Actor nvarchar(max) = null,
@ProductionCountryID uniqueidentifier = null,
@DontReturnMoviesWithNoLicense bit = 0,
@DontReturnNotReadyMovies bit = 0,
@take int = 10,
@skip int = 0,
@order nvarchar(max) = null,
@asc bit = 1)
as
begin
declare @SQLString nvarchar(max);
declare @ascending nvarchar(5);
declare @ParmDefinition nvarchar(max);
set @ParmDefinition = '@MediaID int,
declare @now DateTime;
declare @Rfa nvarchar(8),
@LicenseWindow nvarchar(8),
@OwnerID uniqueidentifier,
@LicenseType nvarchar(max),
@PriceGroupID uniqueidentifier,
@Format nvarchar(max),
@GenreID uniqueidentifier,
@Title nvarchar(max),
@Actor nvarchar(max),
@ProductionCountryID uniqueidentifier,
@DontReturnMoviesWithNoLicense bit = 0,
@DontReturnNotReadyMovies bit = 0,
@take int,
@skip int,
@now DateTime';
set @ascending = case when @asc = 1 then 'ASC' else 'DESC' end
set @now = GetDate();
set @SQLString = 'SELECT distinct m.ID, m.EpisodNo, m.MediaID, p.Dubbed, pf.Format, t.OriginalTitle into #temp
FROM Media m
inner join Asset a1 on m.ID=a1.ID
inner join Asset a2 on a1.ParentID=a2.ID
inner join Asset a3 on a2.ParentID=a3.ID
inner join Title t on t.ID = a3.ID
inner join Product p on a2.ID = p.ID
left join AssetReady ar on ar.AssetID = a1.ID
left join License l on l.ProductID=p.ID
left join ProductFormat pf on pf.ID = p.Format '
+ CASE WHEN @PriceGroupID IS NOT NULL THEN
'left join LicenseToPriceGroup lpg on lpg.LicenseID = l.ID ' ELSE '' END
+ CASE WHEN @Title IS NOT NULL THEN
'left join LanguageAsset la on la.AssetID = m.ID ' ELSE '' END
+ CASE WHEN @LicenseType IS NOT NULL THEN
'left join LicenseType lt on lt.ID=l.LicenseTypeID ' ELSE '' END
+ CASE WHEN @Actor IS NOT NULL THEN
'left join Cast c on c.AssetID = a1.ID ' ELSE '' END
+ CASE WHEN @GenreID IS NOT NULL THEN
'left join ListToCountryToAsset lca on lca.AssetID=a1.ID ' ELSE '' END
+ CASE WHEN @ProductionCountryID IS NOT NULL THEN
'left join ProductionCountryToAsset pca on pca.AssetID=t.ID ' ELSE '' END
+
'where (
1 = case
when @Rfa = ''All'' then 1
when @Rfa = ''Ready'' then ar.Rfa
when @Rfa = ''NotReady'' and (l.TbaWindowStart is null OR l.TbaWindowStart = 0) and ar.Rfa = 0 and ar.SkipRfa = 0 then 1
when @Rfa = ''Skipped'' and ar.SkipRfa = 1 then 1
end) '
+
CASE WHEN @LicenseWindow IS NOT NULL THEN
'AND
1 = (case
when (@LicenseWindow = 1 And (l.WindowEnd < @now and l.TbaWindowEnd = 0)) then 1
when (@LicenseWindow = 2 And (l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now))) then 1
when (@LicenseWindow = 4 And ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now))) then 1
when (@LicenseWindow = 3 And ((l.WindowEnd < @now and l.TbaWindowEnd = 0) or (l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1
when (@LicenseWindow = 5 And ((l.WindowEnd < @now and l.TbaWindowEnd = 0) or ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1
when (@LicenseWindow = 6 And ((l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)) or ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1
when ((@LicenseWindow = 7 Or @LicenseWindow = 0) And ((l.WindowEnd < @now and l.TbaWindowEnd = 0) or (l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)) or ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1
end) ' ELSE '' END
+ CASE WHEN @OwnerID IS NOT NULL THEN
'AND (l.OwnerID = @OwnerID) ' ELSE '' END
+ CASE WHEN @MediaID IS NOT NULL THEN
'AND (m.MediaID = @MediaID) ' ELSE '' END
+ CASE WHEN @LicenseType IS NOT NULL THEN
'AND (lt.Name = @LicenseType) ' ELSE '' END
+ CASE WHEN @PriceGroupID IS NOT NULL THEN
'AND (lpg.PriceGroupID = @PriceGroupID) ' ELSE '' END
+ CASE WHEN @Format IS NOT NULL THEN
'AND (pf.Format = @Format) ' ELSE '' END
+ CASE WHEN @GenreID IS NOT NULL THEN
'AND (lca.ListID = @GenreID) ' ELSE '' END
+ CASE WHEN @DontReturnMoviesWithNoLicense = 1 THEN
'AND (l.ID is not null) ' ELSE '' END
+ CASE WHEN @Title IS NOT NULL THEN
'AND (t.OriginalTitle like N''%' + @Title + '%'' OR la.LocalTitle like N''%' + @Title + '%'') ' ELSE '' END
+ CASE WHEN @Actor IS NOT NULL THEN
'AND (rtrim(ltrim(replace(c.FirstName + '' '' + c.MiddleName + '' '' + c.LastName, '' '', '' ''))) like ''%'' + rtrim(ltrim(replace(@Actor,'' '','' ''))) + ''%'') ' ELSE '' END
+ CASE WHEN @DontReturnNotReadyMovies = 1 THEN
'AND ((ar.ID is not null) AND (ar.Ready = 1) AND (ar.CountryID = l.CountryID))' ELSE '' END
+ CASE WHEN @ProductionCountryID IS NOT NULL THEN
'AND (pca.ProductionCountryID = @ProductionCountryID)' ELSE '' END
+
'
select #temp.* ,ROW_NUMBER() over (order by ';
if @order = 'Title'
begin
set @SQLString = @SQLString + 'OriginalTitle';
end
else if @order = 'MediaID'
begin
set @SQLString = @SQLString + 'MediaID';
end
else
begin
set @SQLString = @SQLString + 'ID';
end
set @SQLString = @SQLString + ' ' + @ascending + '
) rn
into #numbered
from #temp
declare @count int;
select @count = MAX(#numbered.rn) from #numbered
while (@skip >= @count )
begin
set @skip = @skip - @take;
end
select ID, MediaID, EpisodNo, Dubbed, Format, OriginalTitle, @count TotalCount from #numbered
where rn between @skip and @skip + @take
drop table #temp
drop table #numbered';
execute sp_executesql @SQLString,@ParmDefinition, @MediaID, @Rfa, @LicenseWindow, @OwnerID, @LicenseType, @PriceGroupID, @Format, @GenreID,
@Title, @Actor, @ProductionCountryID, @DontReturnMoviesWithNoLicense,@DontReturnNotReadyMovies, @take, @skip, @now
end
该存储过程运行得非常好且速度很快（它的执行通常需要1-2秒）。
通话示例
DBCC FREEPROCCACHE
EXEC value = [dbo].[spGetMovieShortDataList]
@LicenseWindow =N'1',
@Rfa = N'NotReady',
@DontReturnMoviesWithNoLicense = False,
@DontReturnNotReadyMovies = True,
@take = 20,
@skip = 0,
@asc = False,
@order = N'ID'
基本上在执行存储过程期间执行了3个SQL查询，第一个Select Into查询花费了99％的时间。
该查询是
declare @now DateTime;
set @now = GetDate();
SELECT DISTINCT
m.ID, m.EpisodNo, m.MediaID, p.Dubbed, pf.Format, t.OriginalTitle
FROM Media m
INNER JOIN Asset a1 ON m.ID = a1.ID
INNER JOIN Asset a2 ON a1.ParentID = a2.ID
INNER JOIN Asset a3 ON a2.ParentID = a3.ID
INNER JOIN Title t ON t.ID = a3.ID
INNER JOIN Product p ON a2.ID = p.ID
LEFT JOIN AssetReady ar ON ar.AssetID = a1.ID
LEFT JOIN License l on l.ProductID = p.ID
LEFT JOIN ProductFormat pf on pf.ID = p.Format
WHERE
((l.TbaWindowStart is null OR l.TbaWindowStart = 0)
and ar.Rfa = 0 and ar.SkipRfa = 0)
And (l.WindowEnd < @now and l.TbaWindowEnd = 0 )
AND ((ar.ID is not null) AND (ar.Ready = 1) AND (ar.CountryID = l.CountryID))
在数据库上进行大量数据更新（很多表和行受更新影响，但是DB大小几乎不变，现在为752）之后，此存储过程变得非常缓慢。现在需要20到90秒。
如果我从存储过程中获取原始SQL查询-它会在1-2秒内执行。
我们尝试过：
- 使用参数创建存储过程
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON
-
用参数重新创建存储过程
with recompile -
清除产品缓存后执行存储过程
DBCC FREEPROCCACHE - 将where子句的一部分移到连接部分
- 重新索引表
- 使用类似以下的语句从查询中更新表的统计信息
UPDATE STATISTICS Media WITH FULLSCAN
但是，存储过程的执行仍然>> 30秒。
但是，如果我运行由SP生成的SQL查询-它执行不到2秒。
我已经比较了SP和原始SQL的执行计划-它们是完全不同的。在执行RAW SQL的过程中-优化器正在使用合并联接，但是在执行SP时-
优化器正在使用哈希匹配（内部联接），就像没有索引一样。
- RAW SQl的执行计划-快速
- SP的执行计划-慢
如果有人知道会是什么-请提供帮助。提前致谢！
问题答案：
尝试使用提示OPTIMIZE FOR UNKNOWN。如果可行，这可能比每次强制重新编译要好。问题在于，最有效的查询计划取决于所提供的日期参数的实际值。编译SP时，SQL
Server必须猜测将提供哪些实际值，并且在这里可能会做出错误的猜测。 OPTIMIZE FOR UNKNOWN就是为了解决这个确切的问题。
在查询末尾，添加
OPTION (OPTIMIZE FOR (@now UNKNOWN))
类似资料：
-
null 如果我从存储过程中获取原始SQL查询--它将在1-2秒内执行。 我们已经尝试： > 使用参数创建存储过程 null
-
问题内容： 我正在维护一个通过JDBC创建Oracle DB的应用程序。从今天开始，此查询： 由于某些oracle内部机制，开始变得非常缓慢，因为我的所有分支似乎都一样。 有人知道一个可能的原因以及如何面对吗？ 问候，努齐奥 问题答案： 数据字典或固定对象统计信息可能很旧，请尝试重新收集它们： 即使这样，也不一定能收集 所有 系统对象的统计信息。有些对象（例如）必须手动收集。尽管这是一个罕见的数据
-
已定义查询的Dao： 来自Hibernate调试日志的SQL： 当我在数据库上执行这个查询时，大约需要15ms，从代码上执行大约需要1.5秒。我在代码中注释掉了这一行，滞后消失了，所以问题肯定是这个jpql选择。 数据库连接配置： 更新1： debug.log：
-
问题内容： 我写了一个存储过程，昨天通常在不到一秒钟的时间内完成。今天，大约需要18秒。我昨天也遇到了这个问题，似乎可以通过删除并重新创建存储过程来解决。如今，这种技巧似乎没有奏效。:( 有趣的是，如果我复制存储过程的主体并将其作为直接查询执行，它将很快完成。它似乎是一个存储过程，正在减慢它的速度……！ 有谁知道可能是什么问题？我一直在寻找答案，但是通常他们建议通过Query Analyser运行
-
我正在对一个小表执行一个简单的查询 系统表只有三列(Id、Name、Progress)和1300行。 我获取数据的代码是： 这段代码在JTable中显示日期大约需要15秒，而如果在phpmyadmin中执行查询，则需要不到1秒。
-
不知道为什么，模拟器无论切换界面还是动画都变得很慢，但是输入，很正常，没有慢。 尝试-1 我重新启动Xcode和模拟器，但没有任何效果。