当前位置: 首页 > 知识库问答 >
问题:

SQL Server:存储过程变得非常慢,原始SQL查询仍然非常快

史昀
2023-03-14
    null
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
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'
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)) 

如果我从存储过程中获取原始SQL查询--它将在1-2秒内执行。

我们已经尝试:

>

  • 使用参数创建存储过程

      null
  • 共有1个答案

    钱黎明
    2023-03-14

    尝试使用提示optimized FOR unknown。如果可以的话,这可能比每次强制重新编译要好。问题是,最有效的查询计划取决于所提供的日期参数的实际值。在编译SP时,sql server必须对将要提供的实际值进行猜测,而它在这里可能会做出错误的猜测。optimized FOR unknown就是为了解决这个问题。

    在查询结束时,添加

    OPTION (OPTIMIZE FOR (@now UNKNOWN))
    

    http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

     类似资料:
    • 问题内容: 我们正在为一个奇怪的问题而苦苦挣扎:当原始SQL执行得相当快时,存储过程将变得极其缓慢。 我们有 SQL Server 2008 R2 Express Edition SP1 10.50.2500.0,上面有多个数据库。 一个数据库(大小约为747Mb) 一个存储过程,该过程采用不同的参数,并且确实从数据库中的多个表中进行选择。 代码: 该存储过程运行得非常好且速度很快(它的执行通常需

    • 问题内容: 我正在维护一个通过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和模拟器,但没有任何效果。