SQL Server: gespeicherte Prozeduren werden sehr langsam, unformatierte SQL-Abfragen sind immer noch sehr schnell

Question

Jun 03, 2014, 03:15 PM

sql performance sql-server stored-procedures

SQL Server: gespeicherte Prozeduren werden sehr langsam, unformatierte SQL-Abfragen sind immer noch sehr schnell

Wir haben es mit einem merkwürdigen Problem zu tun: Eine gespeicherte Prozedur wird extrem langsam, wenn unformatiertes SQL ziemlich schnell ausgeführt wird.

Wir haben

SQL Server 2008 R2 Express Edition SP1 10.50.2500.0 mit mehreren Datenbanken.eine Datenbank (Größe ca. 747 MB)Eine gespeicherte Prozedur, die verschiedene Parameter verwendet und mehrere Tabellen aus der Datenbank auswählt.

Code:

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

Die gespeicherte Prozedur funktionierte ziemlich gut und schnell (die Ausführung dauerte normalerweise 1-2 Sekunden).

Beispiel für einen Anruf

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'

Grundsätzlich werden während der Ausführung der Stored Procedure die ersten 3 SQL-Abfragen ausgeführtSelect Into Abfrage dauert 99% der Zeit.

Diese Abfrage ist

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))

Diese gespeicherte Prozedur wurde nach einer umfassenden Datenaktualisierung in der Datenbank (viele Tabellen und Zeilen waren von der Aktualisierung betroffen, die DB-Größe blieb jedoch nahezu unverändert, jetzt beträgt sie 752) extrem langsam. Jetzt dauert es 20 bis 90 Sekunden.

Wenn ich eine rohe SQL-Abfrage von der gespeicherten Prozedur nehme, wird sie innerhalb von 1-2 Sekunden ausgeführt.

Wir haben versucht:

Die gespeicherte Prozedur wird mit Parametern erstellt

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON

Erstellen Sie die gespeicherte Prozedur mit Parameter neuwith recompile

Führen Sie die gespeicherte Prozedur aus, nachdem Sie den Prod-Cache geleert habenDBCC FREEPROCCACHEVerschieben Sie einen Teil der where-Klauseln in den join-TeilTabellen neu indizierenAktualisieren Sie die Statistiken für die Tabellen aus der Abfrage mit Anweisungen wieUPDATE STATISTICS Media WITH FULLSCAN

Die Ausführung der gespeicherten Prozedur dauert jedoch noch >> 30 Sekunden.

Wenn ich jedoch die vom SP generierte SQL-Abfrage ausführe, wird sie weniger als 2 Sekunden ausgeführt.

Ich habe Ausführungspläne für SP und für das unformatierte SQL verglichen - sie sind ziemlich unterschiedlich. Während der Ausführung von RAW SQL - der Optimierer verwendet Merge Joins, aber wenn wir SP ausführen - verwendet er Hash Match (Inner Join), als gäbe es keine Indizes.

Ausführungsplan für RAW SQL - Schnell Ausführungsplan für SP - Langsam

Wenn jemand weiß, woran es liegen könnte - bitte helfen Sie. Danke im Voraus!