none
Warum läuft die Abfage mehrere Stunden ? RRS feed

  • Frage

  • Hallo zusammen,

    wir sind mitten in einer Migration von SQL2000 zu SQL2012.

    Unter SQL2000 hatten wir folgende Abfrage, die bei ca 5 Millionen Datensätzen nach 10 bis 20 Sekunden ein eindeutiges Ergebnis brachte:

    If (
    	SELECT  count([BSM-Nummer])  FROM [BSM WETOP].dbo.[Artikelstamm] WAS where [BSM-Nummer] not in (select [BSM-Nummer] from [BSM WETOP].dbo.[Artikelstamm Classes] where  (Class_Nr = 1008) ) 
    	) >0
    	begin 
    		Print 'Lala'
    	end

    Auf dem SQL2012 läuft diese Abfrage Stunden und bringt kein Ergebnis

    Wenn ich auf dem SQL2012 nur die innere Abfrage nehme, also das select Count([BSM-Nummer]) ... ist das Ergebnis nach 2 Sekunden da.

    Was muß ich wie anpassen, um in akzeptabler Zeitspanne ein verwertbares Ergebnis zu bekommen ?

    Vielen Dank für Eure Hilfe

    Peter Hundhausen

    • Typ geändert Ionut DumaModerator Dienstag, 4. Februar 2014 15:22 Keine Rueckmeldung des Fragenstellender
    • Typ geändert Ionut DumaModerator Mittwoch, 5. März 2014 09:42 Ruckmeldung des Fragenstellender
    Mittwoch, 1. Januar 2014 19:37

Antworten

Alle Antworten

  • Hallo Peter,

    schau Dir doch mal den Ausführungsplan der Abfrage an. Evtl. lässt sich da schon sehen, wo das Problem liegt.

    Evtl. fehlt noch ein Index.

    Wenn das nicht hilft, poste bitte mal die CREATE TABLE Statements der betroffenen Tabellen und einige Beispieldaten, mit denen man das SQL Statement ausführen lassen kann.

    ---

    Wie wird das gezeigte Statement (inkl. dem IF ...) eingesetzt? In deiner Funktion, einer SP, ...?


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community


    Mittwoch, 1. Januar 2014 20:07
    Moderator
  • Hallo Stefan,

    leider sagt mir der Ausführungsplan nicht allzuviel, irgendwann muß ich mir mal die Zeit nehmen, zu lernen, was der so alles aussagt.

    Das mit den Testdaten wird nicht viel bringen, weil es sich um Tabellen mit ca 5 Millionen Sätzen handelt. Mich irritiert halt nur, daß der veraltete SQL2000 diese Abfrage in 20 Sekunden schafft und der neue nicht.

    Ebenso für mich unverständlich, wenn ich das umgebende If-Statement weglasse, kann der SQL2012 das in 2 Sekunden

    Aber im Moment fehlt mir die Zeit, das hier weiter zu verfolgen, ich hatte gehofft, daß eine schnelle Antwort möglich ist. Im Gesamtprojekt der Migration muß ich bis Montag noch viel schaffen, da fehlt die Zeit, sich länger mit so einem Problem zu beschäftigen.

    Trotzdem Danke

    Freitag, 3. Januar 2014 12:33
  • Du solltest nach der Migration der Datenbank auf jeden Fall noch etwas Zeit für die Aktualisierung der Statistiken einplanen:

    exec sp_updatestats;
    dbcc updateusage ('MeineDatenbank');

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Freitag, 3. Januar 2014 13:19
  • Danke für den Tip, ist schon durchgelaufen
    Freitag, 3. Januar 2014 17:00
  • Hallo Peter,

    versuche mal, Deine Abrage von NOT IN nach NOT EXISTS umzuschreiben:

    SELECT  
    	COUNT([BSM-Nummer])  
    FROM [BSM WETOP].dbo.[Artikelstamm] t1
    WHERE [BSM-Nummer] NOT IN (
    			SELECT [BSM-Nummer] 
    			FROM [BSM WETOP].dbo.[Artikelstamm Classes] t2
    			WHERE  (Class_Nr = 1008) 
    			) 
    SELECT  
    	COUNT([BSM-Nummer])  
    FROM [BSM WETOP].dbo.[Artikelstamm] t1
    WHERE [BSM-Nummer] NOT EXISTS (
    			SELECT [BSM-Nummer] 
    			FROM [BSM WETOP].dbo.[Artikelstamm Classes] t2
    			WHERE t1.[BSM-Nummer] = t2.[BSM-Nummer]
    				AND (Class_Nr = 1008) 
    			) 

    Schönen Abend.
    Freitag, 3. Januar 2014 19:03
  • Wenn ich die Abfrage so "nackt" laufen lasse, wie du sie gepostet hast (und ich das Feld [BSM-Nummer] aus der where Bedingung herausnehme), ist die Abfrage nach 1 Sekunde fertig.

    Wenn ich aber die IF-Bedingung wieder drum herum setze, läuft die Abfrage sich wieder zu Tode

    Schade, aber trotzdem danke

    Freitag, 3. Januar 2014 19:56
  • Hallo Peter,

    dann fehlt auf dem Feld [BSM-Nummer] sehr wahrscheinlich ein Index.

    Schönen Abend.

    Freitag, 3. Januar 2014 20:02
  • Hallo Peter,

    ich habe mir mal die Arbeit gemacht, Dir die Unterschiede mit Beispieldaten aufzuzeigen:

    DECLARE @intCounter INT; DECLARE @intCounter2 INT; SET @intCounter = 1; SET @intCounter2 = 1; CREATE TABLE #artikelstamm ( [BSM-Nummer] INT, [Class_Nr] INT ); --CREATE INDEX artikel_BSM -- ON #artikelstamm ([BSM-Nummer]); --CREATE INDEX artikel_Class -- ON #artikelstamm ([Class_Nr]); WHILE @intCounter < 500000 BEGIN INSERT INTO #artikelstamm ([BSM-Nummer], [Class_nr]) VALUES(@intCounter, CASE WHEN SUBSTRING (CAST (@intCounter2 AS VARCHAR (MAX)),LEN (@intCounter2) , 1 ) = 8 THEN 1008 ELSE @intCounter2 END) SET @intCounter = @intCounter + 1 SET @intCounter2 = @intCounter2 + 1 END -- derzeit verwendet SET STATISTICS TIME ON; IF ( SELECT COUNT([BSM-Nummer]) FROM #artikelstamm t1 WHERE [BSM-Nummer] NOT IN ( SELECT [BSM-Nummer] FROM #artikelstamm t2 WHERE (Class_Nr = 1008) ) ) > 0 BEGIN Print 'Lala' END SET STATISTICS TIME OFF; -- Alternative1 SET STATISTICS TIME ON; IF ( SELECT COUNT([BSM-Nummer]) FROM #artikelstamm t1 WHERE NOT EXISTS ( SELECT [BSM-Nummer] FROM #artikelstamm t2 WHERE t1.[BSM-Nummer] = t2.[BSM-Nummer] AND (Class_Nr = 1008) ) ) > 0 BEGIN Print 'Lala' END SET STATISTICS TIME OFF; -- Alternative2 SET STATISTICS TIME ON; SELECT CASE WHEN ( SELECT COUNT([BSM-Nummer]) FROM #artikelstamm t1 WHERE NOT EXISTS ( SELECT [BSM-Nummer] FROM #artikelstamm t2 WHERE t1.[BSM-Nummer] = t2.[BSM-Nummer] AND (Class_Nr = 1008) ) ) > 0 THEN 'Lala' END SET STATISTICS TIME OFF; DROP TABLE #artikelstamm;

    Die Ausführungszeitzen siehst Du auf der Registerkarte Meldung am ganz Ende. Bei mir ohne Indexe mit 500.000 Datensätzen:

     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 13703 ms, verstrichene Zeit = 13741 ms.
    Lala
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 329 ms, verstrichene Zeit = 332 ms.
    Lala
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
    (1 Zeile(n) betroffen)
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 312 ms, verstrichene Zeit = 313 ms.


    Nun das ganze nun noch einmal mit Indexen (Auskommentierung in der Abfrage aufgehoben):

     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 437 ms, verstrichene Zeit = 435 ms.
    Lala
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 281 ms, verstrichene Zeit = 293 ms.
    Lala
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
    (1 Zeile(n) betroffen)
     SQL Server-Ausführungszeiten: 
    , CPU-Zeit = 296 ms, verstrichene Zeit = 297 ms.

    Du siehst:

    Deine Variante mit NOT IN ist ohne Indexe signifikant langsamer. Mein Vorschlag mit NOT EXISTS ist deutlich performanter. Indexe wirken sich (besonders bei Deiner Variante) erheblich aus. Die Effekte nehmen zu mit zunehmender Anzahl an Datensätzen.

    Ergebnisse hier unter MSSQL 2008R2.

    Schönen Tag.

    Samstag, 4. Januar 2014 09:33
  • Hallo P...

    leider sagt mir der Ausführungsplan nicht allzuviel, irgendwann muß ich mir mal die Zeit nehmen, zu lernen, was der so alles aussagt.

    Dann poste ihn doch bitte mal. Einfach - wie bereits erwähnt - den Ausführungsplan aktivieren und ausführen. Anschließend mal den Plan hier posten. Dann brauchen wie auch nicht in der Glaskugel lesen :)

    Das mit den Testdaten wird nicht viel bringen, weil es sich um Tabellen mit ca 5 Millionen Sätzen handelt. Mich irritiert halt nur, daß der veraltete SQL2000 diese Abfrage in 20 Sekunden schafft und der neue nicht.

    Das hängt unter anderem mit weiteren 12 Jahren Entwicklungszeit zusammen, die das Team von Microsoft (hier insbesondere das Team um den Query Optimizer) dazu verwendet haben, um Abfragen noch effizienter und schneller zu machen. Leider kann es dann aber vorkommen, dass - ursprünglich schnelle - Bestandsabfragen noch mal auf den Prüfstand müssen!

    Ebenso für mich unverständlich, wenn ich das umgebende If-Statement weglasse, kann der SQL2012 das in 2 Sekunden

    Das wundert mich auch. Von daher mal die Bitte, den Ausführungsplan zu posten! Eventuell kann man dann schon etwas Genaueres sagen. Zur Not reicht auch der "geschätzte" Ausführungsplan. Dazu einfach die Abfrage markieren und [STRG]+[L] drücken! Besser ist aber der richtige Plan, um zu sehen, wie "geschätzte" Werte von "tatsächlichen" Werten abweichen!

    Aber im Moment fehlt mir die Zeit, das hier weiter zu verfolgen, ich hatte gehofft, daß eine schnelle Antwort möglich ist.

    Hmm - sehe ich zwar anders; aber dann warten wir einfach mal ab, bis das Projekt migriert ist. Letztendlich werden sich die Anwender bei Euch melden und sich über die Latenzen beschweren.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Samstag, 4. Januar 2014 10:14
  • Wie wäre es mal mit folgender Variante?

    Declare @Anzahl integer;
    
    Select @Anzahl = count() ...
    
    If @Anzahl > 0 ...

     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Montag, 6. Januar 2014 08:55
  • Hallo Peter,

    Hat Dir die Antworten geholfen? Wenn Ja bitte markiere diese als Antwort.

    Danke und Gruss,

    Ionut


    Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-PrinzipEntwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

    Kann ich leider noch nicht beurteilen, da ich aktuell noch keine Gelegenheit hatte, mich mit den neuen Antworten zu beschäftigen.

    Ich werde alle Vorschläge prüfen und dann auch ein entsprechendes Feedback geben, aber wie ich schon geschrieben habe, mir fehlt aktuell die Zeit dazu

    Gruss

    Dienstag, 7. Januar 2014 14:23
  • **************************************************************************************************
    Dieser Thread wurde mangels weiterer Beteiligung des Fragestellenden ohne bestätigte Lösung abgeschlossen.
    Neue Rückfragen oder Ergänzungen zu diesem Thread bleiben weiterhin möglich.
    **************************************************************************************************

    Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-PrinzipEntwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

    Dienstag, 4. Februar 2014 15:22
    Moderator
  • Also, da bin ich wieder, ich habe nochmal ein bißchen herumprobiert.

    Der Ausführungsplan hat vorgeschlagen, daß ich Indizes ergänzen soll, das hab ich einfach mal gemacht.

    Damit ist die Abfragedauer runter auf ca 2 Min 27 Sek

    Wenn ich aber nur den Teil select Count .... ausführe, also ohne die umgebende IF-Bedingung, ist die Abfrage in 1 Sekunde abgefrühstückt.

    Wie kann ich denn den Ausführungspan hier posten ?

    Folgende Variante braucht erstaunlicherweise auch nur 1 Sekunde (mit den neuen Indizes)

    Declare @V int
    set @V = (SELECT  count([BSM-Nummer])  FROM [BSM WETOP].dbo.[Artikelstamm] WAS where [BSM-Nummer] not in (select [BSM-Nummer] from [BSM WETOP].dbo.[Artikelstamm Classes] where  (Class_Nr = 1008) ) )
    IF @V > 0
    	begin 
    		Print 'Lala'
    	end

    Wenn ich die neuen Indizes lösche, braucht diese Variante 2 Sekunden, was auch kein Problem darstellt

    Es bleibt mir zwar unverständlich, warum die ursprüngliche Variante sich tot gelaufen hat, aber mit der letzten Lösung kann ich für meinen Teil gut leben.

    An dieser Stelle mein Dank an alle, die mit Rat und Tat geholfen haben

    Peter

    Dienstag, 4. März 2014 19:32
  • HM, die Antwort von Christoph Mutmann kann ich nicht mehr als Lösung kennzeichnen ? !

    Vielleicht kann das ja ein Foren-Admin übernehmen, Danke !

    Dienstag, 4. März 2014 19:35
  • HM, die Antwort von Christoph Mutmann kann ich nicht mehr als Lösung kennzeichnen ? !

    Vielleicht kann das ja ein Foren-Admin übernehmen, Danke !

    meine Worte... :-)

    - das liegt an der wenig sinnvollen Umwandlung in Diskussion. Aber ich denke, das Thema wird jetzt auch von den Moderatoren verstanden. Vielen Dank für Deinen Hinweis daher.


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Mittwoch, 5. März 2014 09:06