SQL Server:存储过程变得很慢，原始 SQL 查询仍然很快 [英] SQL Server: stored procedure become very slow, raw SQL query is still very fast
问题描述
我们正在努力解决一个奇怪的问题:当原始 SQL 执行得相当快时，存储过程会变得非常慢.
We are struggling with a strange problem: a stored procedure become extremely slow when raw SQL is executed fairly fast.
我们有
- 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 秒).
The stored procedure was working pretty good and fast (it's execution usually took 1-2 seconds).
调用示例
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% 的时间.
Basically during execution of the stored procedure the executed 3 SQL queries, the first Select Into query takes 99% of time.
这个查询是
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))
这个存储过程，在对数据库进行大量数据更新后(很多表和行受到更新影响，但数据库大小几乎没有变化，现在是 752 )变得工作非常缓慢.现在需要 20 到 90 秒.
This stored procedure, after massive data update on the database (a lot tables and rows were affected by the update, however DB size was almost unchanged, now it is 752 ) become to work extremely slow. Now it takes from 20 to 90 seconds.
如果我从存储过程中获取原始 SQL 查询 - 它会在 1-2 秒内执行.
If I take raw SQL query from the stored procedure - it is executed within 1-2 seconds.
我们已经尝试过:
存储过程是用参数创建的
the stored procedure is created with parameters
设置 ANSI_NULLS ON将 QUOTED_IDENTIFIER 设为开启
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON
用参数with recompile
但是存储过程的执行仍然是>> 30 秒.
However the execution of the stored procedure is still >> 30 seconds.
但是如果我运行由 SP 生成的 SQL 查询 - 它的执行时间不到 2 秒.
But if I run the SQL query which is generated by the SP - it is executed for less than 2 seconds.
我比较了 SP 和原始 SQL 的执行计划 - 它们完全不同.在 RAW SQL 执行期间 - 优化器使用合并连接，但是当我们执行 SP - 它使用哈希匹配(内部连接)，就像没有索引一样.
I've compared execution plans for SP and for the raw SQL - they are quite different. During execution of RAW SQL - the optimizer is using Merge Joins, but when we execute SP - it uses Hash Match (Inner Join), like there are no indexes.
如果有人知道这可能是什么 - 请帮忙.提前致谢！
If someone knows what could it be - please help. Thanks in advance!
推荐答案
尝试使用提示OPTIMIZE FOR UNKNOWN.如果它有效，这可能比每次都强制重新编译要好.问题是，最有效的查询计划取决于提供的日期参数的实际值.在编译 SP 时，sql server 必须对将提供的实际值进行猜测，并且很可能在此处进行错误的猜测.OPTIMIZE FOR UNKNOWN 就是针对这个确切的问题.
Try using using the hint OPTIMIZE FOR UNKNOWN. If it works, this may be better than forcing a recompile every time. The problem is that, the most efficient query plan depends on the actual value of the date paramter being supplied. When compiling the SP, sql server has to make a guess on what actual values will be supplied, and it is likely making the wrong guess here. OPTIMIZE FOR UNKNOWN is meant for this exact problem.
在查询的末尾，添加
OPTION (OPTIMIZE FOR (@now UNKNOWN))
这篇关于SQL Server:存储过程变得很慢，原始 SQL 查询仍然很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
