Prozedur (SQL-Statement) optimieren

[fillibuster]

Hallo,

habe folgende Prozedur, welche nach dem hinzufügen des zweiten JOINS sehr langsam ist. Wo gibt es Optimierungsmöglichkeiten?:

Delphi-Quellcode:

procedure Tdata.getCatalog(var ioList : TDocxDocument; inList : TStringList; language : integer=1);
var
  i  : integer;
  tmp : TDocxline;
begin
  ioList.Clear;
  if workQuery.Active then WorkQuery.Active := false;
  workQuery.SQL.Clear;
  workQuery.SQL.Add('SELECT ass.level, ass.article_id, ass.additional_position, ass.mcount, ass.img, trans.name, art.number, art.type FROM ');
  workQuery.SQL.Add('assignments as ass JOIN articles as art ON  ass.article_id=art.id JOIN translations as trans ON trans.article_id=art.id WHERE');
  workQuery.SQL.Add('trans.language_id=:lng AND ass.id=:id');
  for i := 0 to inlist.Count - 1 do
  begin
    workQuery.ParamByName('id').AsInteger  := StrToInt(inList[i]);
    workQuery.ParamByName('lng').AsInteger := language;
    workQuery.Open;
    if not workQuery.IsEmpty then
    begin
      tmp.level          := workQuery.FieldByName('level').AsInteger;
      tmp.article_id     := workQuery.FieldByName('article_id').AsInteger;
      tmp.additional_position := workQuery.FieldByName('additional_position').AsInteger;
      tmp.mcount     := workQuery.FieldByName('mcount').AsInteger;
      tmp.img            := workQuery.FieldByName('img').AsString;
      tmp.article_name   := workQuery.FieldByName('name').AsString;
      tmp.article_number := workQuery.FieldByName('number').AsString;
      tmp.article_type   := workQuery.FieldByName('type').AsString;
      ioList.AddRange(tmp);
    end;
    workQuery.Close;
  end;
end;

Danke ...

[DeddyH]

Sind alle Schlüsselfelder indiziert?

[fillibuster]

@DeddyH
Du meinst, ob diese einen Index haben? Bisher nicht!

[Andreas Schilling]

In der where-Bedingung zuerst die Abfrage mit der kleinsten Ergebnismenge aus der Haupttabelle. Da in deinem Fall nur eine where-Bedingung auf die Haupttabelle geht, diese also zuerst abfragen. Dadurch werden nur noch Unterabfragen zu den resultierenden Datensätzen durchgeführt. Bei deiner Reihenfolge der where-Bedingung werden praktisch alle Datensätze der Haupttabelle angefasst um zu prüfen ob in Tabelle translations ein gesuchter Eintrag existiert.
workQuery.SQL.Add('where ass.id=:id and trans.language_id=:lng'); Die Felder ass.id, art.id und trans.article_id sollten zusätzlich noch indiziert sein.

[fillibuster]

Uuups, da wird die gnaze Sache direkt mal 80% schneller.
Danke!

[jobo]

80% sieht doch nach einem Fortschritt aus!
Ich wollte nur anmerken, dass MySQL sicher soetwas wie einen Explain Plan zu den Abfragen anbietet.

[p80286]

Zitat von Andreas Schilling:

In der where-Bedingung zuerst die Abfrage mit der kleinsten Ergebnismenge aus der Haupttabelle. Da in deinem Fall nur eine where-Bedingung auf die Haupttabelle geht, diese also zuerst abfragen. Dadurch werden nur noch Unterabfragen zu den resultierenden Datensätzen durchgeführt. Bei deiner Reihenfolge der where-Bedingung werden praktisch alle Datensätze der Haupttabelle angefasst um zu prüfen ob in Tabelle translations ein gesuchter Eintrag existiert.

Und ich hab mal gelernt, es istn egal in welcher Reihenfolge die "Where"-Abfragen erfolgen, da der Preprocessor das sowieso optimiert.

Nunja jedes DBMS ist anders.

Gruß
K-H

[jobo]

Zitat von p80286:

Zitat von Andreas Schilling:

In der where-Bedingung .. ein gesuchter Eintrag existiert.

Und ich hab mal gelernt ..
Nunja jedes DBMS ist anders.

Gruß
K-H

Deswegen habe ich ja Explain Plan empfohlen. Dabei dürften wenig Zweifel bleiben, wo es hakt.
Und man muss sich nicht soviel merken.

[sega84]

Hi, gebe allen anderen Recht... Habe allerdings noch ne Idee, wie du die verarbeitung besser machen könntest...

Delphi-Quellcode:

procedure Tdata.getCatalog(var ioList : TDocxDocument; inList : TStringList; language : integer=1);
var
  i  : integer;
  tmp : TDocxline;
  flevel, farticle_id, fadditional_position : TField;
begin
  ioList.Clear;
  if workQuery.Active then WorkQuery.Active := false;
  workQuery.SQL.Clear;
  workQuery.SQL.Add('SELECT ass.level, ass.article_id, ass.additional_position, ass.mcount, ass.img, trans.name, art.number, art.type FROM ');
  workQuery.SQL.Add('assignments as ass JOIN articles as art ON  ass.article_id=art.id JOIN translations as trans ON trans.article_id=art.id WHERE');
  workQuery.SQL.Add('trans.language_id=:lng AND ass.id=:id');
  for i := 0 to inlist.Count - 1 do
  begin
    workQuery.ParamByName('id').AsInteger  := StrToInt(inList[i]);
    workQuery.ParamByName('lng').AsInteger := language;
    workQuery.Open;
    flevel := workQuery.Fields[0];
    farticle_id := workQuery.Fields[1];
    fadditional_position := workQuery.Fields[2];
    //....
    if not workQuery.IsEmpty then
    begin
      tmp.level          := flevel.AsInteger;
      tmp.article_id     := farticle_id.AsInteger;
      tmp.additional_position := fadditional_position.AsInteger;
      //....
      ioList.AddRange(tmp);
    end;
    workQuery.Close;
  end;
end;

[fillibuster]

Hi,

danke für eure Tipps. Was ich mich noch Frage ist, ob man an dieser Stelle direkt lieber nen dynamisches INT-Array nehmen sollte:
    workQuery.ParamByName('id').AsInteger := StrToInt(inList[i]); damit dieses dauernde StrToInt wegfällt. Das wäre aber nen tieferer Eingriff

Viele Grüße ...