Effiziente SELECT Abfrage

[WorstNightmare]

Hallo,

ich stricke gerade mit Datenbanken rum, eher gesagt mit MySQL 5. Es handelt sich um die Datenbank eines Spieleservers und normalerweise greift ein Java-Programm auf diese zu, nun wollte ich allerdings auch mal versuchen, sie aus Delphi heraus zu verwalten.

Delphi-Quellcode:

  ZConnection1.Connected := True;

  ZQuery1.SQL.Text:='Select * from accounts;';
  ZQuery1.Open;
  while not ZQuery1.Eof do
  begin
    Showmessage(Zquery1.FieldByName('name').AsString);
    ZQuery1.Next;
  end;

Da die Datenbank frisch eingerichtet wurde, enthält sie nur den Account "admin". Dieser wird mir durch Showmessage auch angezeigt, allerdings möchte ich nun gerne machen, dass mir die üblichen Felder angezeigt werden (id, passwort etc.), aber eben nur von einem bestimmten Account. Ich könnte einfach alle Accounts durchgehen (falls mal irgendwann mehrere vorhanden sind), allerdings gibt es da sicherlich eine bessere Lösung, oder?

Ich hoffe ich bin im richtigen Forum, denn diese Frage hat eher mehr direkt mit MySQL zu tun als mit Delphi...

[FaTaLGuiLLoTiNe]

Vereinfachte Syntax des SQL-Befehls SELECT:

SQL-Code:

SELECT [Feldname1, Feldname2, ...] FROM [Tabelle] WHERE [Bedingungen]

In deinem Fall also etwa sowas:

SQL-Code:

SELECT (id, password, ...) FROM accounts WHERE name = 'admin'

Ich würde dir aber empfehlen, wenn du neu in der Arbeit mit Datenbanken bist, ein Tutorial oder sowas durchzuarbeiten. SQL ist eine eigene Sprache mit nicht geringem Funktionsumfang.

[edit=alzaimar] "code"- durch "SQL"-Tags ersetzt. Mfg, alzaimar[/edit]

[alzaimar]

Zitat:

Zitat von FaTaLGuiLLoTiNe

SQL-Code:

SELECT (id, password, ...) FROM accounts WHERE name = 'admin'

Ohne Klammern wär's besser.

[FaTaLGuiLLoTiNe]

Mpf, da hast du natürlich recht. Danke für die Berichtigung.

Und für die Zukunft merke ich mir auch, dass es hier Tags für SQL-Code gibt.

[WorstNightmare]

Ah danke, so geht es nun:

SQL-Code:

  SELECT id FROM accounts WHERE name = 'admin';

Ich hatte sowas ähnliches auch schon versucht, aber mit diesen anderen Hochstrichen (`), mit den "normalen" aus Delphi funktioniert es aber nun.

[DeddyH]

Zitat:

Zitat von alzaimar

Zitat:

Zitat von FaTaLGuiLLoTiNe

SQL-Code:

SELECT (id, password, ...) FROM accounts WHERE name = 'admin'

Ohne Klammern wär's besser.

Mit Parametern wär's noch besser :zwinker:

Delphi-Quellcode:

Query.SQL.Text := 'SELECT id, password FROM accounts WHERE name = :name';
Query.ParamByName('name').Value := 'admin';

[hitzi]

Zitat:

Zitat von DeddyH

Mit Parametern wär's noch besser :zwinker:

Ich nutze dazu immer die Format Funktion. Haben die Parameter einen Vorteil gegenüber Format?

[DeddyH]

Über das Format, Quoting etc. musst Du Dir bei Parametern keine Gedanken machen. Als Nebeneffekt schützen Sie außerdem vor SQL-Injection, wobei ich jetzt erst nachdenken müsste, ob diese bei Verwendung von Format() möglich wäre.

[Nersgatt]

Wenn Du z.B. in einer Schleife 10x die selbe Abfrage ausführen musst, nur mit unterschiedlichen Parametern, dann muss die DB das Statement nur 1x parsen und prüfen und dann beim Ausführen nur die Werte des parameters ändern. Kann u.U. einen erheblichen Geschwindigkeitsvorteil bringen.

[hitzi]

Danke für die Infos. Da werd ich in Zukunft mal auf die Parameter umsteigen :)

[Nersgatt]

Ein sehr sinnvolles Vorhaben.
Ich habe eine Anwendung übernommen, wo überhaupt nicht mit Parametern gearbeitet wurde. Insbesondere bei Schnittstellen, wo 1000x ein Insert ausgeführt wird, habe ich festgestellt, dass es nach umstellung auf Parameter um den Faktor 3 schneller lief. Da haben einige Leute erstaunt geschaut :mrgreen:
Und so stelle ich nun Stück für Stück den gesamten Code auf Parameter um.

[alzaimar]

Zitat:

Zitat von DeddyH

Mit Parametern wär's noch besser :zwinker:

Es ging um die SQL-Syntax. :cheer:

[mkinzler]

Zitat:

Zitat von alzaimar

Zitat:

Zitat von DeddyH

Mit Parametern wär's noch besser :zwinker:

Es ging um die SQL-Syntax. :cheer:

Und um Effektivität. :zwinker:

[pertzschc]

Zitat:

Zitat von Nersgatt

Insbesondere bei Schnittstellen, wo 1000x ein Insert ausgeführt wird, habe ich festgestellt, dass es nach umstellung auf Parameter um den Faktor 3 schneller lief.

Nun hätte ich die Frage, ob das auch bei Verwendung von ADO-Klasse mit MS SQL Server so ist, dass es mit Parametern schneller wird?

Danke,
Christoph

[mkinzler]

Ja, da bei prepared Statement zu einem nur die Parameter übertragen werden und zudem das Parsen, Planbilden usw. entfällt

[pertzschc]

Zitat:

Zitat von mkinzler

Ja, da bei prepared Statement zu einem nur die Parameter übertragen werden und zudem das Parsen, Planbilden usw. entfällt

Wie sollte man dann folgenden Code am besten umbauen (Beispiel eines Insert-Statement)? Bisher haben wir das SQL Statement komplett übergeben und ausserhalb dieser Funktion als String zusammengesetzt:

Delphi-Quellcode:

function TmyADOFunctions.ADOInsertIntoDB(a_SQLStatement: string): TMyADOResultStruct;
var
  myADOResultStruct           : TMyADOResultStruct;
  lCounter                    : integer;
begin
  assert(true = Prod_Conn_CorrectConnected);
  myADOResultStruct := TMyADOResultStruct.Create; // init

  try
    ADOCommand1.Connection.BeginTrans;
    ADOCommand1.CommandType := cmdText;
    ADOCommand1.CommandText := a_SQLStatement;
    ADOCommand1.Connection.Execute(a_SQLStatement, lCounter);
    myADOResultStruct.counter := lCounter;
    ADOCommand1.Connection.CommitTrans;
    myADOResultStruct.status := true;

  except
    // wenn Fehler abbrechen...
    on E: Exception do begin
      ADOCommand1.Connection.RollbackTrans;
      myADOResultStruct.status := false;
      myADOResultStruct.exception := E;
      myADOResultStruct.exceptionTxt := E.Message;
    end;
  end; // try-except
  Result := myADOResultStruct;
end;

Danke+Gruß,
Christoph

[anse]

Parameter zu verwenden bedeutet aber nicht sofort daß es zu einem prepared Statement wird - das muß die Datenbank-API erstmal unterstützen und dann heißt es doch eher Query.ExecPrepared oder nich?

[mkinzler]

Einmalig ( beim Ersten Insert oder bei Programmstart) Statement setzen und preparieren.
Beim Insert dann nur noch Parameter setzen.

Zitat:

Parameter zu verwenden bedeutet aber nicht sofort daß es zu einem prepared Statement wird - das muß die Datenbank-API erstmal unterstützen

Das habe ich mal bei MSSQL voausgesetzt

Zitat:

und dann heißt es doch eher Query.ExecPrepared oder nich?

Kommt auf DBMS/Komponenten an

[Frederika]

Zitat:

Zitat von mkinzler

Einmalig ( beim Ersten Insert oder bei Programmstart) Statement setzen und preparieren.
Beim Insert dann nur noch Parameter setzen.

Was für ein genialer Tipp. Da hätte man selbst drauf kommen sollen... Das hat bei ADO eben gerade super funktioniert. CommandText nur einmal mit INSERT-Statement belegen, in der Schleife nur noch Parameters ändern hat Faktor 2x gebracht. Dann noch Prepared := True nochmal Faktor 2x -- insgesamt etwa Faktor 3.8 reproduzierbar schneller als mit der langweiligen Nichtwisser-Version, die ich vorher hatte.

Danke!

[anse]

Einen Faktor 100 bekommst du wenn du die extended Syntax für INSERTs benutzt (die soweit ich weiß nur von MySQL unterstützt wird):

Code:

INSERT INTO `mytable` (`col1`, `col2`, `col3`)
  VALUES (1, 'bla', 'foo'),
    (2, 'bla', 'foo'),
    (3, 'bla', 'foo'),
    (4, 'bla', 'foo'),
    (5, 'bla', 'foo'),
    ...
    (1000, 'bla', 'foo')

Du bastelst also einen riesigen SQL String zusammen und fügst damit tonnenweise Zeilen mit einem einzigen Befehl in die Tabelle. Ist limitiert auf die max_allowed_packet Einstellung (per Default auf 1M).

[Frederika]

@anse: Tja, schade, hat mit ADO auf MS Access jedenfalls nicht geklappt. Merke ich mir aber, falls ich mal auf MySQL stoße.

[mkinzler]

Zitat:

Einen Faktor 100 bekommst du ...

Das erscheint mir etwas hochgegriffen :gruebel:

[anse]

Nö, eher noch zu tief. Es kommt natürlich auf die Anzahl der Zeilen an die man einfügen will - bei vielen "kurzen" Zeilen ist der Geschwindigkeitsvorteil durch Wegfall des Overheads enorm, dagegen bei längeren Zeilen weniger. Probier es aus :)

[mkinzler]

Intern wird das auch als prepared Staement abgehandelt. So groß dürfte der Overhead auch nicht sein.

[Bernhard Geyer]

Zitat:

Zitat von anse

Nö, eher noch zu tief. Es kommt natürlich auf die Anzahl der Zeilen an die man einfügen will - bei vielen "kurzen" Zeilen ist der Geschwindigkeitsvorteil durch Wegfall des Overheads enorm, dagegen bei längeren Zeilen weniger. Probier es aus :)

Faktor 100 kommst du auch nur hin wenn du eine schlechte Anbindung zur DB hast so das der Rount-Trip-Delays sehr ins gewicht fällt.

Am besten ist natürlich die Kombination aus beiden: Prepared Statements + Bulk-Insert :mrgreen:

[anse]

Zitat:

Zitat von Bernhard Geyer

Faktor 100 kommst du auch nur hin wenn du eine schlechte Anbindung zur DB hast so das der Rount-Trip-Delays sehr ins gewicht fällt.

Oder mit einer Anwendung die (wie es immer so ist) noch zusätzlichen Overhead pro Query dazubaut. Das ist z.B. in HeidiSQL so, wo jeder Query in ein Log-Memo geschrieben wird.

Naja, wir kommen glaube ich vom eigentlichen Thema ab :)