SQL Server: stored procedure werden sehr langsam, raw SQL-Abfrage ist immer noch sehr schnell
Wir kämpfen mit einem seltsamen problem: eine gespeicherte Prozedur extrem langsam, wenn raw-SQL ausgeführt wird, ziemlich schnell.
Wir haben
- SQL Server 2008 R2 Express Edition SP1 10.50.2500.0 mit mehreren Datenbanken auf.
- einer Datenbank (es ist eine Größe von rund 747Mb)
- eine gespeicherte Prozedur, die dauert unterschiedlichen Parametern und wählt zwischen mehreren Tabellen aus der Datenbank.
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 funktioniert ziemlich gut und schnell (es ist die Ausführung dauerte in der Regel 1-2 Sekunden).
Beispiel nennen
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 während der Ausführung der gespeicherten Prozedur, die ausgeführt werden 3 SQL-Abfragen, die ersten Select Into
Abfrage nimmt 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, nach der massiven Daten-Updates auf der Datenbank (viele Tabellen und Zeilen wurden von dem update betroffen sind, jedoch DB-Größe war fast unverändert, jetzt ist es 752 ) werden arbeiten extrem langsam. Jetzt braucht es von 20 bis 90 Sekunden.
Wenn ich raw-SQL-Abfrage, die von der gespeicherten Prozedur - es wird ausgeführt, innerhalb von 1-2 Sekunden.
Wir haben versucht:
- die gespeicherte Prozedur wird erstellt, mit den Parametern
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON - erstellen Sie die gespeicherte Prozedur mit parameter
with recompile
- ausführen der gespeicherten Prozedur nach dem Spülen prod cache
DBCC FREEPROCCACHE
- bewegen, Teil der where-Klausel in die join-Teil
- Neuindizierung der Tabellen
- aktualisieren von Statistiken für die Tabellen aus der Abfrage, die mit Aussagen wie
UPDATE STATISTICS Media WITH FULLSCAN
Jedoch die Ausführung der gespeicherten Prozedur ist immer noch >> 30 Sekunden.
Aber wenn ich die SQL-Abfrage auszuführen, die erzeugt wird durch die SP - Ausführung für weniger als 2 Sekunden.
Habe ich im Vergleich Ausführungspläne für die SP und für die roh-SQL - Sie sind ganz anders. Während der Ausführung von ROH-SQL - optimizer ist mithilfe von Merge-Joins, aber wenn wir ausführen, SP - er verwendet Hash Match (Inner Join), gibt es noch keine Indizes.
Wenn jemand weiß was es sein könnte - bitte helfen. Vielen Dank im Voraus!
Nach der Lektüre Ihrer Frage, Titel und schaut in die Prozedur Definition größten vermuten ist
Parameter Sniffing
. Versuchen Sie es Ausführen Verfahren WITH RECOMPLE
option.Klingt wie parameter sniffing problem für mich, aber OP sagt, dass er das schon versucht 'WITH RECOMPILE'. Einfach nur neugierig, wenn Sie führen Sie Ihre raw-sql, machst du es mit dem date-parameter oder Wert ist das hardcoded?
Hi Jungs! Ich habe gerade überprüft die links - Sie arbeiten sind, zumindest für mich. Wenn Sie auf den link klicken, müssen Sie klicken auf einen anderen link. Aus der Seite: > herunterladen der Datei klicken Sie auf den folgenden link: > ExecutionPlan_RAW_SQL_FAST.sqlplan ja, wie ich schon sagte, ich habe versucht, speichern Sie die Prozedur with recompile - es hat nicht geholfen 🙁 Datum zugeordnet ist, während der Ausführung: set @now = GetDate();
InformationsquelleAutor Dmitry | 2014-06-03
Du musst angemeldet sein, um einen Kommentar abzugeben.
Versuchen Sie, mit dem Hinweis
OPTIMIZE FOR UNKNOWN
. Wenn es funktioniert, kann dies besser sein, als dazu zwingt, jedes mal neu kompilieren. Das problem ist, dass die meisten effizienten Abfrageplan wird, hängt vom tatsächlichen Wert des date-Parameter geliefert. Beim kompilieren der SP, sql-server, stellen Sie eine Vermutung auf, was die wirklichen Werte geliefert werden, und es ist wahrscheinlich, dass die falsche denke hier.OPTIMIZE FOR UNKNOWN
gedacht, für genau dieses problem.Ende Ihrer Abfrage hinzufügen
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx
OPTION (OPTIMIZE FOR (@now UNKNOWN, @LicenseWindow UNKNOWN))
direkt nach der ersten Abfrage in der SP und es sich ganz schnell.Ich habe jedoch eine Frage : haben Sie eine Idee, warum die SP war vor der Arbeit und jetzt muss ich zur Nutzung dieser Tip???
Es hängt von der zugrunde liegenden Daten in der Tabelle UND der Wert der date-parameter geliefert. Um es herauszufinden, müssen Sie analysieren die Art der Daten ändern. Es dreht sich alles um l.WindowEnd < @jetzt - bevor sich Ihre Daten geändert haben, der Standard-generierten Ausführungsplan wurde worden gut funktioniert, aber nach, es ist eindeutig nicht so gut.
Ich sehe, danke für die Klarstellung
InformationsquelleAutor Dan Ling
Da Sie über
sp_executesql
kompilieren Sie die Prozedur, oder löschen Sie die zwischengespeicherten plan für das Verfahren nicht tatsächlich helfen, den Abfrageplan für die Abfrage ausgeführt über sp_executesqlwird separat zwischengespeichert, um die gespeicherte Prozedur.
Müssen Sie entweder fügen Sie den Abfragehinweis
WITH (RECOMPILE)
auf dem sql ausgeführt wird, oder deaktivieren Sie den cache für diese spezifische sql vor der Ausführung:Dies ist natürlich irrelevant ist, wenn Sie ausgeführt
DBCC FREEPROCCACHE
Sie nicht bestanden hat, alle Parameter gelöscht und die ganzen cache.InformationsquelleAutor GarethD