Ado Performance steigern

[EgonHugeist]

Hallo DP'ler,

das erste mal, daß ich eine Frage einstelle. Leider muß ich etwas ausholen:

Bin am basteln, wie ich unsere zwei teuren Brüder FireDac/UniDac toppen kann. Ich bin mit dem jetzigen Stand von Zeos-7.2 eigentlich ziemlich zufrieden. DevArt brauch ich nicht mehr nachzufragen, da as zu weit hinten liegt. FireDac ist jedoch in gewissen Bereichen noch interessant. Ich habe alle Treiber, sagen wir mal, nach Wunschliste, abgearbeitet. Zeos hat eigene Benchmark-Tests, um Performance-Vergleiche auszuführen. Der Sprung von <7.2 zu 7.2up ist gewaltig, je nach Anforderungen der Tests habe ich einen Performance-Zuwachs von Faktor 4 bis 20. Aber darum geht's jetzt nicht.

Am Ende steht da noch das Zeos-Protokol "ADO" im Raum.

Derzeit benutze ich am liebsten die Tests von http://synopse.info/fossil/wiki/Synopse+OpenSource, da diese externe Tests sind und nicht den Anschein erregen würden, sie wären in irgend einer Weise manipuliert.
Diese Test laufen ohne TDataSet oder der gleichen, direkter Zugriff auf's AdoRecordSet bzw. AdoCommand. Die Synopse Tests sind simpel und benchmarken 5000 Inserts und Lesen diese wieder. Wer's wissen will: sie basieren auf reiner UTF8-codierung, Ist aber hier im Kontext nicht notwendig, da ja nach nicht ADO-Treiber die Performance x10-20 dagegen steht.

Das brachte mich zum Grübeln. Entweder ist MSSQL einfach nur 'ne Schnecke oder de Hammer hängt ganz wo anders! Also begann ich im Zeos-Projekt zu suchen und zu basteln.. Ohne wirkliche Erfolge, wenn ADO zum Tragen kam.
Vor drei Wochen schenkte mir mein Vater mal wieder eines seiner alten Bücher: ADO-Programmierung von MS Press, Autor ist David Sceppa. Habe mir die 300 Zeiten mal schnell komplett gegönnt, um etwas zu lernen. Erfahren wollte ich freilich, wie man ADO "tunen" kann und was der beste Weg für BATCH Executions wäre:
ADO unterstützt das nur via AdoRecordSet -> implementiert -> hust, prust. Da kann ich's auch manuell Row By Row machen! -> Implementiert -> 20% schneller als übers AdoRecordSet.

Erstes Zahlen zum Vergleich & Merken von den Synopse-Test (inserts/Sekunde VOM LESEN WILL ICH WIRKLICH NOCH GAR NICHT REDEN!):

Zitat von Zeos-7.2 DBC ADO-direct:

{
"Engine": "ZEOS MSSQL ADO",
"CreateTableTime": "3.73s",
"NumberOfElements": 5000,
"InsertTime": "4.42s",
"InsertRate": 1129,
"InsertBatchTime": "975.32ms",
"InsertBatchRate": 5126,
"InsertTransactionTime": "3.59s",
"InsertTransactionRate": 1391,
"InsertBatchTransactionTime": "529.30ms",
"InsertBatchTransactionRate": 9446
}

Also das war ja so ernüchternt! Eigentlich habe ich mich regelrecht vera....t gefühlt. Das kann's ja wohl echt nicht sein. Bezahlst einen Batzen Kohle für MSSQL und dann das: MSSQL + MS ADO!!! Abgesehen vom Feature-Verlust im Vgl. zu FB.
Somit begann ich zu überlegen, wo denn dieser unglaubliche DROP her kommt:
Verschieden Versionen von SLQLNCLI .... nö keine merklichen Unterschiede.
Dann folgte nur die Logik. ADO ist nicht's weiter als ein End-User freundlicher Wrapper für OleDB. Alles in schöne Interfaces verpackt, Locale Typen kein Problem usw.. Was wäre, wenn ich via AdoCommand direkt mit OleDB kommunizieren könnte? Umgehe mal den ganzen OleVarinat Quark, und liefere, was der eigentlich Treiber will?
Erster versuch, klappt. Jedoch haben wir Tests, welche mich Zwangen alles nochmal umzuwerfen -> never discuss with the users!
Zweiter Versuch, ADO im Vordergrund, via QueryInterface das AdoCommand umgangen und nur noch mit ICommand/ICommandText/ICommandPrepare/IAccessor all meine Wünsche ausgeführt: ist 5-10%langsamer als VS1 mit OleDB. Resultate(FÜR JEDEN SELBER MESSBAR):

Zitat von Zeos-7.3 OleDB direct from and bypassed ADO:

{
"Engine": "ZEOS MSSQL ADO+OLEDB",
"CreateTableTime": "253.06ms",
"NumberOfElements": 5000,
"InsertTime": "880.99ms",
"InsertRate": 5675,
"InsertBatchTime": "619.58ms",
"InsertBatchRate": 8069,
"InsertTransactionTime": "455.54ms",
"InsertTransactionRate": 10975,
"InsertBatchTransactionTime": "150.37ms",
"InsertBatchTransactionRate": 36251,
}

Hossa! Da sieht doch die Welt gleich ganz anders aus! Immer noch nicht perfekt/vergleichbar mit anderen Providern, doch ein merklicher Zuwachs der Performace!
Wem die Test's nicht gefallen: Schaut mal im 7.3er SVN von Zeos, wie man das ADO gequarke umgeht, implementiert es & vegleicht selber.
ES rockt mit direkt OleDB und hinkt mit ADO.

Nun die eigentliche Frage, wenn mann bedenkt, daß Zeos nur über Defaults das Treibers zugreift, das Handbuch darauf vereist, daß ADO in JEDEM Falle die best mögliche Einstellungen ja nach Provider trifft...

Wer kennt sich mit ADO aus?
Was geht da noch? Übersehe ich Settings, die ich nicht kenne und ich im MS-Buch nicht zu finden vermag?
Wo und wie muß ich die Knöpfe drücken, um ein bischen zu Lächeln? Wäre über jeden "sinnvollen" Gedanken erfreut.

Im Buch beschrieben steht nich viel: "Über meine gesamte Zeit, habe ich noch nie veränderte Properties gesehen"... "mit ADO lassen sich High-Performance erstellen"....BlaBla Alles vom MS Autor.
Was kann man tun, um wenigstens im Ansatz aus 'nem "Veralteten, lahmenden Esel" einen "galloppierendes Pferd" zu machen? ICE erwartet ja keiner, wenn mann bedenkt, das ADO eigenen Speicher reserviert & OleDB auch noch dazu und DANN erst kommst du!

Michael

Ps. Direkt OleDB-Wrapper ist fast fertig für 7.3. Dennoch ist die Frage zwar nostalgisch aber ernst gemeint! Was geht denn da noch?
mein Connect String:

Zitat von ADO ConnectionString:

Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=zeoslib;Trusted_Connection=Yes;Data Source=EGONDEVLAPTOPW7\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EGONDEVLAPTOPW7;Use Encryption for Data=False;Tag with column collation when possible=False

[Sir Rufo]

Wäre es nicht ratsam erstmal die Grundperformance des MSSQL-Servers zu bestimmen? Wie schnell kann der denn wirklich 5000 Zeilen schreiben/lesen.

Wenn das feststeht, dann macht man den Test über die Zugriffskomponenten und sieht wieviel Prozent da verschluckt werden. Erst dann kann man auch eine echte Aussage machen. Bis jetzt ist es Kaffeesatz lesen

PS:

SQLEXPRESS - ist auch performancetechnisch nicht mit dem Server zu vergleichen! Lies dir mal die Beschränkungen dazu durch.

[EgonHugeist]

Danke für erste Anteilnahme .

Vielleicht habe ich zu weit ausgeholt? Ich wollte keine Performance von MSSQL o. andere Server an sich vergleichen oder in Frage stellen, sondern habe ADO(AdoCommand) mit seinen Parametern dem Prepare usw. mit einem direkten OleDB(noch nicht mal sqloledb, welches MSSQL-spezifisch wäre) Zugriff verglichen.

Somit spielt also was kann MSSQL oder was can die DevExpress-Version für mich keine Rolle.

Nochmals zum Verstädnis, für Ole, ich erschaffe ebenfalls ein AdoCommand, und weise die aktive Connection zu. Dann jedoch endet mein ADO-Code (nur für Updates derzeit) und ich leite sämtliche Ole-Interfaces, welch ich benötige via Supports() oder QueryInterface ab, um sicher zu stellen, daß der Ole-Provider auch kann, was ich machen möchte..

Um die updates machen zu können, ist wesentlich mehr Code notwendig, aber die voran genannten Zahlen können sprechen..

[Dejan Vu]

Hmm... Wenn es performancetechnisch eine Rolle spielt, wie schnell 5000 Inserts sind, dann stimmt was mit meinem Design nicht. Es ist eigentlich immer falsch, 5000 Inserts aus seiner Software heraus zu machen, wenn die Performance wichtig ist. Dann musst Du zu BULK INSERT greifen, bzw. (bei 5000 vermutlich einfacher und schneller) auf Dataset-Parameter (also eine @Tabelle). Aber weder das eine noch das Andere wird von ADO nicht unterstützt (ADO wird imho eh nicht weiterentwickelt).

BULK INSERT bekommst Du aber mit einem einfachen ADOCommand hin.

Zum Thema Schnecke: Beim Einfügen ist FB wirklich ziemlich schnell, aber dafür kann man beim Löschen von Datensätzen in den Urlaub fahren. FB eignet sich imho also nicht für eine DB, bei der sehr viele Daten geschrieben (das geht schnell) und auch wieder gelöscht werden (z.B. eine Messdatenbank, bei der z.B. Daten älter als 10 Tage gelöscht werden). Oder FB ist auch beim löschen sauschnell, aber ich bin zu blöd dafür.

Deine Messungen erinnern mich aber eher daran, herauszubekommen, ob man einen Ferrari oder einen Maserati schneller anschieben kann und wie viel Heu in die einzelnen Autos passt:

Wenn Du also Provider vergleichen willst, dann musst Du das schon entweder mit irgend einer Referenz-DB machen (also z.B. FB) oder Tests für viele RDBMS schreiben.

[Bernhard Geyer]

Zitat von Dejan Vu:

Hmm... Wenn es performancetechnisch eine Rolle spielt, wie schnell 5000 Inserts sind, dann stimmt was mit meinem Design nicht. Es ist eigentlich immer falsch, 5000 Inserts aus seiner Software heraus zu machen, wenn die Performance wichtig ist. Dann musst Du zu BULK INSERT greifen, bzw. (bei 5000 vermutlich einfacher und schneller) auf Dataset-Parameter (also eine @Tabelle).

Dann machen wir was falsch. Wir inserten Mio-Datensätze in Stunden. Den "Bulk Insert" kann man auch per "normalen" SQL- nachbilden.

Zitat von Dejan Vu:

Aber weder das eine noch das Andere wird von ADO nicht unterstützt (ADO wird imho eh nicht weiterentwickelt).

Betrifft AFAIK nur den MS OLE DB Provider for SQL Server, nicht den SQL Server Native Client.

Zitat von Dejan Vu:

BULK INSERT bekommst Du aber mit einem einfachen ADOCommand hin.

Eigentlich schon - oder jedenfalls fast. Du kannst problemlos mehrer INSERTS zu einem einzelnen "BULK" zusammenfassen.

Zitat von Dejan Vu:

Wenn Du also Provider vergleichen willst, dann musst Du das schon entweder mit irgend einer Referenz-DB machen (also z.B. FB) oder Tests für viele RDBMS schreiben.

Also wir haben das mal mit diversen DBs gemacht (MS SQL, MySQL, Oracle).
Die DBs nehmen sich nicht viel. Viel wichtiger ist der eigene Quellcode. Wenn man hier murks macht kann selbst der größer Cluster-Server nix mehr reisen wenn auf DB-Seite Grundvorrausetzungen wie genügend zugewiesener Speicher und (genügend) schnelle Festplatten dran hängen vorhanden sind. Als wir (vor Jahren) einen Prepared Statement-Cache eingebaut haben und dann noch auf selbstgeschriebenen "Bulk INSERT" umgestiegen sind hatten wir einen gewaltigen Performancegewinn - egal welche DB.
Bei MS SQL haben wir die ADOExpress/dbGo rausgeschmissen uns sind aufs ADO-Interface umgestiegen. Hat hier auch viel gebraucht.

[Dejan Vu]

Zitat von Bernhard Geyer:

Dann machen wir was falsch. Wir inserten Mio-Datensätze in Stunden.

Wenn die Performance reicht, nicht (steht doch auch da).

Zitat:

Zitat von Dejan Vu:

Aber weder das eine noch das Andere wird von ADO nicht unterstützt (ADO wird imho eh nicht weiterentwickelt).

Betrifft AFAIK nur den MS OLE DB Provider for SQL Server, nicht den SQL Server Native Client.

Das kann sein.

Zitat:

Zitat von Dejan Vu:

BULK INSERT bekommst Du aber mit einem einfachen ADOCommand hin.

Eigentlich schon - oder jedenfalls fast. Du kannst problemlos mehrer INSERTS zu einem einzelnen "BULK" zusammenfassen.

Nein. Äh. Ja, du kannst INSERT-Statements in einen String packen und den dann rüberschicken, das geht viel schneller (eine Transaktion vs. 1000).
Und nein: Du bekommst einen BULK INSERT nicht nur "fast", sondern vollständig hin.
Wenn Du Zugriff auf das Serververzeichnis hast, schreibst Du die Daten dort in eine Datei und führst ein BULK INSERT aus. Geht das so nicht, dann über BCP.EXE. Das liest die Daten lokal ein und bläßt sie übers Netz direkt in den Server.

Eventuell kommst Du über Delphi auch direkt an die SMO, aber da bin ich mir nicht sicher, weil das normalerweise für .NET ist. In den SMO ist ein SqlBulkCopy-Objekt.

BULK INSERT lohnt sich aber erst bei Batches > 5000 Records. Dafür dann aber mit 10k-100k Records pro Sekunde.

SELECT mit OPENROWSET ist auch noch eine Variante. Hab ich aber noch nicht probiert.

[Sir Rufo]

Wenn man die Performance der Zwischenschicht ermitteln will, dann teste ich doch nicht den BestCase, sondern den WorstCase. Allerdings ist es entscheidend, wie schnell wird dieser WorstCase ohne und mit Zwischenschicht verarbeitet. Aus dem Unterschied kann ich dann den Overhead der Zwischenschicht errechnen. Ist der Overhead unter einem Prozentsatz x, dann brauche ich mir über die Verbesserung der Zwischenschicht nicht mehr so viele Gedanken machen.

[Dejan Vu]

Nee, wir sind vom Thema abgekommen. Die Auslassungen über BULK INSERT haben nichts mit den Performancemessungen zu tun.

[EgonHugeist]

Bin von der Arbeit zurück..

Zitat von Dejan Vu:

Nee, wir sind vom Thema abgekommen. Die Auslassungen über BULK INSERT haben nichts mit den Performancemessungen zu tun.

Das ist vollkommen richtig. Auch MSSQL spielt hierbei keine Rolle. Ich könnte den Provider tauschen, auf Oracle, MSAccess, Jet oder oder oder. Auch die Anzahl der Records. Die Kluft bleibt spürbar die gleiche! Sie variiert nur ein wenig. Worst case wäre die Anzahl der Inserts zu erhöhen und dann noch LOB's hinzuzufügen, dann wird's wirklich richtig übel. 5000 sind eigentlich nichts, right?!

MSSQL oder mein Kommentar im Eingangs-Thread waren ja nur der sportliche Ansporn mal etwas tiefer zu graben, sich mit ADO, wie es funktioniert und worauf es letztentlich BASIERT mal auseinander zu setzten, einen Code aufzusetzten um mal zu schauen, was da sooo geht. Und es geht einiges.

Nochmal BULK, Provider, oder der gleichen sind hierbei außen vor die Zahlen sind im weitesten Sinne "Best-Case", wenn man so will, und für mich ein dringender Indikator für "Das kann so nicht stehen bleiben". Würde ich es so akzeptieren, hieße es den Kofferraum zu öffnen und zu schauen, wie viel Heu zu den anderen Türen reinschieben kann, ohne mir um gewisse veränderte oder veränderbare Umstände Gedanken zu machen.

Mir ist mir die Kluft einfach zu groß. In dem besagten Handbuch finde ich keine gelisteten Properties der Objekte, mehr noch der Autor verweist immer wieder darauf, das er in all seinen Jahren bei MS, nie geänderte Props. gesehen hat. Er rät sogar davon ab, diese zu verändern. Kein Kommentar weshalb.. Einfach in den Raum gestellt.

Was aber nicht heißen soll, das man es nicht macht

Darum wiederhole ich die Frage an den Fundus der DP:

Was kann man tun? Hat jemand Erfahrungen mit veränderten Einstellungen von ADO?
Kann man das Default Verhalten von ADO in irgend einer Weise *push*en?

Michael

P.s:
Noch schlimmer wird es erst beim lesen der Daten, wenn man sich http://msdn.microsoft.com/en-us/libr...=vs.85%29.aspx mal vollkommen durchliest, und sich den Weg der Daten zurück an den "consumer" via ADO und am Besten noch mit Performance Drop des TDataSet vorstellt.

Edit und alle sub-links wie: http://msdn.microsoft.com/en-us/libr...=vs.85%29.aspx

2. Edit:
Bernhard, das "clonen" des insert Stmts und die gleichzeitige Ausführung des mehrfach Inserts habe ich vorher auch schon getestet. Abgesehen davon, das es möglicher Weise nicht von allen Ole-Anbietern unterstützt wird, hat es mit ADO eigentlich keine spürbaren Erfolge gebracht. Den Versuch habe ich auch wieder bei Seite gelegt.

[EgonHugeist]

Gesundes Neues!

Ich wollte das neue Jahr mal nutzen und das Thema anschieben. *Push, Push* Falls irgend jemand 'ne Ahnung hat?!

Da mich das nicht in Ruhe gelassen hat, habe ich mal in ein paar freien Tagen ein neues Zeos-Protokol "OleDB" geschrieben. Nativer Zugriff via OleDB COM-Objects auf MSSQL(derzeit). Vollständig ohne ADO. Zeos-7.3 Siehe: Hier

Mal für euch zum Vergleich, was bei den Benchmark Tests (Beschreibung mit Resultaten(MSSQL fehlt da noch) Hier) heraus kam:

Code:

{
  "Engine": "ZEOS MSSQL ADO-OLEDB",
  "CreateTableTime": "279.16ms",
  "NumberOfElements": 5000,
  "InsertTime": "862.34ms",
  "InsertRate": 5798,
  "InsertBatchTime": "598.49ms",
  "InsertBatchRate": 8354,
  "InsertTransactionTime": "456.03ms",
  "InsertTransactionRate": 10964,
  "InsertBatchTransactionTime": "146.33ms",
  "InsertBatchTransactionRate": 34168,
  "ReadOneByOneTime": "97.41s",
  "ReadOneByOneRate": 51,
  "ReadAllVirtualTime": "146.62ms",
  "ReadAllVirtualRate": 34100,
  "ReadAllDirectTime": "120.59ms",
  "ReadAllDirectRate": 41462,
  "ClientCloseTime": "7.29ms"
}
{
  "Engine": "ZEOS MSSQL OLEDB(SQLOLEDB)",
  "CreateTableTime": "22.25ms",
  "NumberOfElements": 5000,
  "InsertTime": "520.75ms",
  "InsertRate": 9601,
  "InsertBatchTime": "97.36ms",
  "InsertBatchRate": 51354,
  "InsertTransactionTime": "517.12ms",
  "InsertTransactionRate": 9668,
  "InsertBatchTransactionTime": "98.50ms",
  "InsertBatchTransactionRate": 50757,
  "ReadOneByOneTime": "713.98ms",
  "ReadOneByOneRate": 7002,
  "ReadAllVirtualTime": "30.65ms",
  "ReadAllVirtualRate": 163084,
  "ReadAllDirectTime": "18.63ms",
  "ReadAllDirectRate": 268326,
  "ClientCloseTime": "6.29ms"
}
{
  "Engine": "ZEOS MSSQL OLEDB(SQLNCLI11)",
  "CreateTableTime": "117.20ms",
  "NumberOfElements": 5000,
  "InsertTime": "545.12ms",
  "InsertRate": 9172,
  "InsertBatchTime": "93.04ms",
  "InsertBatchRate": 53738,
  "InsertTransactionTime": "549.59ms",
  "InsertTransactionRate": 9097,
  "InsertBatchTransactionTime": "94.09ms",
  "InsertBatchTransactionRate": 53135,
  "ReadOneByOneTime": "822.99ms",
  "ReadOneByOneRate": 6075,
  "ReadAllVirtualTime": "30.96ms",
  "ReadAllVirtualRate": 161451,
  "ReadAllDirectTime": "19.35ms",
  "ReadAllDirectRate": 258384,
  "ClientCloseTime": "6.59ms"
}

@Dejan

wenn ich mir das mal so anschaue, liegt MSSQL gar nicht so weit von FB entfernt mit den Insert-Raten. Scheint mir eher ein ADO-Problem zu sein.. Oder ich stell mich dusselig an.. Ich habe die Anzahl der Records auch mal auf 100.000 erhöht, doch der rechnerische Durchschitt bleibt so ziehmlich der Gleiche.

@all
Also ich würde mich freuen, wenn ich ADO noch ein wenig anschubsen könnten. Das kann doch wirklich nicht alles sein

Grüße, Michael