MSSQL/ADO ... speed is everything !!!

[bernhard_LA]

ich experimentiere gerade an der Optimierung von SQL-Queries herum.
Die Aufgabenstellung ist wie folgt :


a) gegeben sind xx Millionen Records in einer MSSQL Datenbank , xx= 1 ... 100 ;
b) jeder einzelne Datensatz muss vom Server geholt werden und in einer Funktion PROC(aDataset) verarbeitet werden.
c) gemessen wird die Verarbeitungszeit aller Daten.


Ich habe eine Abhängigkeit der verarbeiteten Datensätze / Sekunde von der Größe des angeforderten Datenpaketgröße festgestellt.

Bei kleinen Paketen ist der Kommunikations-Overhead DB/Anwendung die Bremse, bei Großen Query-Ergebnissen geht der Zugriff auf einzelne Rekords nicht mehr linear in der Zeit.

Sind meine Beobachtungen und Erklärungen richtig?

[Dejan Vu]

Nein. Die Geschwindigkeit ist von vielen Faktoren abhängig, die im Provider und auch im Server eingestellt werden können.

In deinem Fall ist es aber eher so, das not Speed everything ist, but the Speicherverbrauch also into Consideration getaked werden muss. What brings it you when you theoretically very fast deine Daten fetchen kannst, aber die not in the Speicher ryne bekommst.

Die Lösung lautet: 'Overlapped': Wenn Du overlapped arbeitest, kannst Du sowohl Server als auch Client optimal ausnutzen. Overlapped bedeutet hier: Während ein Thread (oder mehrere) den nächsten Block von Daten liest, kann der andere (die anderen) Thread(s) die Daten bereits verarbeiten.

Deine Verarbeitungsgeschwindigkeit ist also nicht: Ladezeit + Verarbeitungszeit, sondern eher: Max(Ladezeit, Verarbeitungszeit)+Kleiner Overhead.

Und *dann* kannst Du an der Pagegröße herumdrehen, der Anzahl der Threads etc. Aber das Wichtigste: Dein Speicher wird nie überlaufen, deine Anwendung ist auch bei 100 Mrd Datensätzen stabil und schnell etc. Und Du kannst die Parameter so drehen, das der Server nicht übermäßig belastet wird etc.

Wenn dieses Tool jedoch öfter eingesetzt wird, würde ich mir überlegen, die Daten schon vorher zu verarbeiten/verdichten. Eventuell 1x täglich, alle Stunde, Minute, in Echtzeit etc.

[mkinzler]

Zudem wäre zu Überlegen, ob man PROC als SP oder UDF auf den Server verlagern kann.

[Sir Rufo]

Oder man benutzt Message Queuing und kann dann mit beliebig vielen Rechnern auf den Datensätzen rumknuspern ohne den Datenserver damit zu behelligen.

[Dejan Vu]

Zitat:

Zitat von mkinzler (Beitrag 1262670)

Zudem wäre zu Überlegen, ob man PROC als SP oder UDF auf den Server verlagern kann.

Oder als .NET-DLL, was der Performance zuträglich wäre. Allerdings ist das nur in Verbindung mit einem ETL-Prozess sinnvoll, d.h. eine kontinuierliche Verarbeitung.

Die Datenmenge ist zwar noch nicht sonderlich hoch, aber vermutlich wäre das auch etwas für BigData, zumindest, um sich damit mal zu beschäftigen.

[mkinzler]

Zitat:

Oder als .NET-DLL, was der Performance zuträglich wäre.

Was ja nur eine Implementationsform einer SP wäre

[Dejan Vu]

Zitat:

Zitat von mkinzler (Beitrag 1262674)

Zitat:

Oder als .NET-DLL, was der Performance zuträglich wäre.

Was ja nur eine Implementationsform einer SP wäre

Eine Stored Procedure ist in SQL geschrieben (und hat limitierte Möglichkeiten zur Auswertung). Eine .NET-DLL nicht. Also ist es nicht 'nur eine Implementationsform einer SP', sondern viel viel mehr.

[bernhard_LA]

Mein Pseudocode geht wie folgt :

Delphi-Quellcode:

    procedure complete_Process(...);     

           mytimer := 0;   ///  hier beginnt  die Zeitmessung

           someInit ();

           MakeSQLStr();

           query.sql.add();
           query.open;

           myTimer.result(1)    //  hier messe ich die Zeit bis zum laden der Daten in die Query

           query.first;
           for i := 0  to maxrecords  do
                begin   
                proc (query, ......);
                query.next;
                end;
           ....
            myTimer.result(2);    //  hier ist die Zeitmessung wie im Graphen ganz oben gezeigt

           finalize



          
           end;

die Zeit an Messpunkt1 ist gegenüber Messpunkt 2 zu vernachlässigen

[Sir Rufo]

@bernhard_LA

Und der Ansatz ist eben von der Performance her schlecht.

Code:

Daten lesen ---> Queue -+-> Verarbeitungs-Thread 1 -+-> Queue ---> Daten schreiben
                        +-> Verarbeitungs-Thread 2 -+
                        :                            :
                        +-> Verarbeitungs-Thread n -+

Bei der Implementierung ist natürlich darauf zu achten, dass man den Queues eine Maximal-Größe mitgibt, die nicht überschritten werden darf.

[Dejan Vu]

Hier ist der Aufwand (LadeZeit + VerarbeitungsZeit/AnzahlDerThreads). Überlappe LadeZeit und Verarbeitungszeit, dann bist Du bei der optimalen Lösung. Siehe auch Post #2 ( die jetzt nicht unbedingt optimal ist).

Ich würde hierfür einen Workerthreadpool einsetzen und die parallelen Ladejobs von der Serverkonfiguration (output-pipes) abhängig machen.


Jeder der L Lade-Thread lädt N Datensätze und überführt sie in Queue.
Jeder der V Verarbeitungs-Thread nimmt sich 1-M Datensätze und verarbeitet sie.

Der Ladethread wartet, wenn die Queue 'zu voll' ist, d.h. mehr als Q Einträge hat und fängt an, wenn die Queue weniger als P Einträge hat (P<Q).

Nun hat man 6 Stellschrauben (L,V, N,M,P und Q) mit denen sich trefflich optimieren lässt.
Vermutlich ist V die Anzahl der Cores und L die Anzahl der Outputpipes des SQL-Servers (4 waren das, glaube ich).

Je nachdem, wie der Server bzw. die Datenbank konfiguriert ist (Stichwort: Partioning) sind parallele Ladethreads auch überflüssig, weil der Server das eh parallelisiert. Bei 100 Mio DS kann man schon über Tablepartitioning nachdenken.

[arnof]

Mal ein Tipp:

nicht alle Datensätze laden!!!!!

Wenn Du was sehen willst, dann suche genau diese Datensätze raus -> WHERE ist hier eine tolle Sache, wenn ein Where da ist dann schränke die Sache mal mit TOP Anweisung ein.

Ich habe in den letzten Jahren die Erfahrung gemacht, das ich mit TOP 100 am besten leben kann, d.h. der Enduser sieht schon mal was und legt mir aber nicht den Server lahm. Wenn die User zahl steigt 10,50 > 100 legen solche SQL Anweisung den Server lahm ....

Die Zeiten von BDE sind halt vorbei!

[Jumpy]

Was wird denn inhaltlich in

proc (query, ......);

gemacht?

Vielleicht ist ja da schon Optimierungspotential, indem man das (teilweise) die Datenbank machen läßt?

[mkinzler]

Zitat:

Zitat von Jumpy (Beitrag 1262709)

Was wird denn inhaltlich in

proc (query, ......);

gemacht?

Vielleicht ist ja da schon Optimierungspotential, indem man das (teilweise) die Datenbank machen läßt?

Auf diese Idee ist noch niemand gekommen :wink:

[bernhard_LA]

Zitat:

Was wird denn inhaltlich in

proc (query, ......);

gemacht?

Vielleicht ist ja da schon Optimierungspotential, indem man das (teilweise) die Datenbank machen läßt?

über eine Ganze Anzahl von

Delphi-Quellcode:

mydataset.fieldbyname('...').as....

hole ich mit den Inhalt der einzelnen Spalten am aktiven Datensatz und erzeuge dann eine Klasse mit der geht's dann in die weitere Verarbeitung.

[mkinzler]

Warum nimmst du nicht ein fertiges ORM?

[Mikkey]

Zitat:

Zitat von bernhard_LA (Beitrag 1262717)

über eine Ganze Anzahl von

Delphi-Quellcode:

mydataset.fieldbyname('...').as....

Hier liegt allein schon ein großes Verbesserungspotential:

Wenn Du einmalig den Satz von TFields über fieldbyname('...') ermittelst und über diesen in jedem Record auf die Werte zugreifst, kannst Du das Tempo vervielfachen (ich habe bei so etwas schon das 5-fache Tempo erreicht).

[bernhard_LA]

@ Mikkey

Zitat:

Wenn Du einmalig den Satz von TFields über fieldbyname('...') ermittelst und über diesen in jedem Record auf die Werte zugreifst

Pro record machen wir den Zugriff auch nur 1 x über Fieldbyname ....

[mkinzler]

Zitat:

Zitat von bernhard_LA (Beitrag 1262726)

@ Mikkey

Zitat:

Wenn Du einmalig den Satz von TFields über fieldbyname('...') ermittelst und über diesen in jedem Record auf die Werte zugreifst

Pro record machen wir den Zugriff auch nur 1 x über Fieldbyname ....

Ja aber für jeden Datensatz.

[bernhard_LA]

OK, und wie geht es denn einfacher ? Kann ich Code-Beispiel sehen ?

[mkinzler]

Zitat:

Zitat von bernhard_LA (Beitrag 1262728)

OK, und wie geht es denn einfacher ? Kann ich Code-Beispiel sehen ?

Erzeuge persistente Felder (Doppelklick auf DataSet)

Die Frage nach dem ORM hast Du gesehen?

[DeddyH]

http://www.delphitools.info/2010/11/...s-your-friend/

[Mikkey]

Zitat:

Zitat von bernhard_LA (Beitrag 1262728)

OK, und wie geht es denn einfacher ? Kann ich Code-Beispiel sehen ?

Delphi-Quellcode:

var
  fld1, fld2: TField;
begin
  ...
  query.Open();
  fld1 := query.FieldByName('...');
  fld2 := query.FieldByName('...');
  while not query.EOF do begin
    ProcessRecord(fld1, fld2);
    query.Next();
  end;
end;

[bernhard_LA]

Zitat:

Die Frage nach dem ORM hast Du gesehen?

ja, wäre dies empfehlenswert : http://instantobjects.sourceforge.net/#documentation

[mkinzler]

Oder

http://www.tmssoftware.com/site/aurelius.asp
http://www.devart.com/entitydac/editions.html

[bernhard_LA]

die aktuelle Version vom code sieht wie folgt aus

Delphi-Quellcode:

   procedure complete_Process(...); 

            mytimer := 0; /// hier beginnt die Zeitmessung

            someInit ();

            MakeSQLStr();

            query.sql.add();
            query.open;

            myTimer.result(1) // hier messe ich die Zeit bis zum laden der Daten in die Query

            query.first;
            for i := 0 to maxrecords do
                 begin 
                 copy_data_to_generic_objectlist
                 query.next;
                 end;
             myTimer.result(2); // hier ist die Zeitmessung nach Ende alle DB arbeiten 


            for I := 0 to generic_objectlist do
                 begin
                  data :=generic_objectlist.items[i];
                  proc ( data, .....); 
                 end;
            myTimer.result(3); // hier ist die Zeitmessung wie im Graphen ganz oben gezeigt
            finalize



           
            end;

95 % der Zeit benötigt die Schleife proc(data ,,, ) , d.h. ich werde dem Ansatz von SIR RUFO folgen ....

[pertzschc]

Zitat:

Zitat von bernhard_LA (Beitrag 1262743)

die aktuelle Version vom code sieht wie folgt aus
95 % der Zeit benötigt die Schleife proc(data ,,, ) , d.h. ich werde dem Ansatz von SIR RUFO folgen ....

Sag mal, dass ist doch dasselbe Thema, welches Du bereits in http://www.delphipraxis.net/180711-m...ead-error.html zur Diskussion gegeben hattest.

Hier war die Zusammenfassung nach 7 Seiten Thread dann gefunden:

Zitat:

Wenn man wüßte was er wirklich treibt, könnte er auch ein paar konkrete Lösungsvorschläge bekommen.

Warum geht es also wieder ohne wirklichen Wissensdurst durch den TE weiter?
Grüße, Christoph

[bernhard_LA]

Das Thema von ersten Thread ist schon (teilweise) implementiert und funktioniert auch. Wir halten unser Query Ergebnismenge bei x records , x = 10 .... 10.000;

Aktuell ging es mir um die Diskussion des optimalen x für unsere Anwendung, bzw. unsere Performance-Messreihen vor der großen Codeänderung.

[Dejan Vu]

So, und überlappende Abarbeitung interessiert dich ebensowenig, wie parallele. *Kopfschüttel*

[Sir Rufo]

Zitat:

Zitat von bernhard_LA (Beitrag 1262717)

Zitat:

Was wird denn inhaltlich in

proc (query, ......);

gemacht?

Vielleicht ist ja da schon Optimierungspotential, indem man das (teilweise) die Datenbank machen läßt?

über eine Ganze Anzahl von

Delphi-Quellcode:

mydataset.fieldbyname('...').as....

hole ich mit den Inhalt der einzelnen Spalten am aktiven Datensatz und erzeuge dann eine Klasse mit der geht's dann in die weitere Verarbeitung.

Genau das Erzeugen von Daten-Objekten gehört noch in den Teil der die Daten ausliest (und natürlich ohne das ständige FieldByName)

[jfheins]

Zitat:

Zitat von bernhard_LA (Beitrag 1262750)

Aktuell ging es mir um die Diskussion des optimalen x für unsere Anwendung, bzw. unsere Performance-Messreihen vor der großen Codeänderung.

Eine Diskussion hilft da eigentlich wenig, sowas muss man probieren (manche sagen "messen"). Dazu kommt: Nur ihr habt die korrekte Hardware, nur ihr könnt das messen.

Läuft übrigens hier unter #7:

Zitat:

Mistake #7: Assuming that runtime characteristics in one environment tell you what behavior will be in a different environment.
Run your benchmarks in the actual environment that the code will be running in; use machines that have the same hardware and software that will typically be used by the customers who ultimately will run the code.

Aber das nur am Rande, ich glaube die wichtigen Punkte wurden schon erwähnt :mrgreen: