zu langsamer select! warum??

[kerim80]

Hallo,

ich habe eine Liste-Buchungen mit Filtern und sortieren der einzelnen Spalten(ASC und DESC).
So, ich verwende den folgenden Code aber dieser dauert schon 3 sek. bei 10 Datensätzen
und wenn ich das SQl-Statement in der IBConsole eingebe dauerts 0 sek und paar gequetschte:

SO SIEHT ES IN DER IBCONSOLE MIT 7 DATENSÄTZEN AUS:
Execution Time(hh:mm:ss.ssss) 00:00:00.0040
Prepare Time (hh:mm:ss.ssss) 00:00:00.0060
Plan PLAN SORT (JOIN (B NATURAL, A INDEX(ADRESSENINDEX1)))

UND DAS IST DER DELPHI-CODE

Delphi-Quellcode:

function TListeBuchungen.GetSql: string;
var i: integer;
    s, sFilter, sPattern: string;
begin
  query.sql.clear;
  query.sql.add('SELECT                                                                                              ');
  query.sql.add('B.BUCHUNGLFDNR,                                                                                     ');
  query.sql.add('B.LFDNR,                                                                                            ');
  query.sql.add('B.BUCHUNGNR,                                                                                        ');
  query.sql.add('B.TICKETNR,                                                                                         ');
  query.sql.add('B.ADRNR,                                                                                            ');
  query.sql.add('B.VERANSTALTER,                                                                                     ');
  query.sql.add('B.HINABFLUGDATUM,                                                                                   ');
  query.sql.add('B.HINABFLUGZEIT,                                                                                    ');
  query.sql.add('B.HINABFLUGORT,                                                                                     ');
  query.sql.add('B.HINANKUNFTORT,                                                                                    ');
  query.sql.add('B.HINFLUGNR,                                                                                        ');
  query.sql.add('                                                                                                    ');
  query.sql.add('B.RUECKABFLUGDATUM,                                                                                 ');
  query.sql.add('B.RUECKABFLUGZEIT,                                                                                  ');
  query.sql.add('B.RUECKABFLUGORT,                                                                                   ');
  query.sql.add('B.RUECKANKUNFTORT,                                                                                  ');
  query.sql.add('B.RUECKFLUGNR,                                                                                      ');
  query.sql.add('                                                                                                    ');
  query.sql.add('B.NETTOPREIS,                                                                                       ');
  query.sql.add('B.ANZAHLUNG,                                                                                        ');
  query.sql.add('B.TAX,                                                                                              ');
  query.sql.add('B.MWST,                                                                                             ');
  query.sql.add('B.RABATT,                                                                                           ');
  query.sql.add('B.GEWINNPROZENT,                                                                                    ');
  query.sql.add('A.NAME,                                                                                             ');
  query.sql.add('A.VORNAME                                                                                           ');
  query.sql.add('                                                                                                    ');
  query.sql.add('FROM BUCHUNGEN B, ADRESSEN A                                                                        ');
  query.sql.add('WHERE EXTRACT (YEAR FROM B.HINABFLUGDATUM) = ' +#39+ CBYear.Items.Strings[CBYear.itemindex] + #39    );
  query.sql.add('AND B.ADRNR = A.ADRNR                                                                               ');
  query.sql.add('%s                                                                                                  ');

  sFilter := AnsiUppercase(Trim(edit1.Text));
  s := '';
  if sFilter <> '' then begin
    sPattern := QuotedStr('%' + SFilter + '%');
    with query do
      for i := 0 to FieldCount - 1 do
        if Fields[i].DataType = ftString then begin
          if s <> '' then s := s + ' OR';
          s := s + Format(' UPPER(%s) LIKE %s', [LowerCase(Fields[i].FieldName), sPattern]);
        end;
    s := 'AND ( ' + s + ' )';
  end;

  Result := Format(query.SQL.Text, [s]) + ordersql;
end;

VIELEN DANK

[SvB]

Hallo, warum hast Du denn in den query.sql.add nach den Feldnamen so viele Leerzeichen drinne, bzw. zwischen manchen Feldern Leerzeilen. Vielleicht hat der SQL-Server damit ein Problem, bis er das richtig auf der Reihe hat. Du könntest auch mal am Anfang und am Ende der Funktion die Zeit messen und vergleichen, ob es schon an der Funktion liegt, die so lange braucht.

Grüße
Sven

[Phistev]

Und fasse mal den SQL-String zusammen, evtl. dauert das Zusammenfassen der Strings etwas länger.

[jensw_2000]

Die "klassische" JOIN-freie Verbindung zwischen 2 Tabelen ist häufig langsamer, weil diese Art der Abfrage intern erst einen CROSS JOIN macht, und die Rückgabemenge dann in der WHERE Clausel filtert.
Bei großen Tabellen kann das schon derb bremsen.

Des weiteren kannst du ja mal kontrollieren, ob du in beiden Tabellen einen Index auf ADRNR erstellt hast.
Das wäre auch noch eine Idee ...

Du machst das jetzt so ...

SQL-Code:

SELECT  A.Irgendwas
       ,B.Irgendwas

FROM BUCHUNGEN B, ADRESSEN A

WHERE EXTRACT (YEAR FROM B.HINABFLUGDATUM) = DeinDatum
      AND B.ADRNR = A.ADRNR

Das sollte bei langen Tabellen deutlich schneller sein

SQL-Code:

SELECT  A.Irgendwas
       ,B.Irgendwas

FROM BUCHUNGEN B
INNER JOIN ADRESSEN A
ON A.ADRNR = B.ADRNR

WHERE EXTRACT (YEAR FROM B.HINABFLUGDATUM) = DeinDatum

Ich hoffe es hilft und Firebird kann JOINS ...



[edit]

Grade erst gesehen ...

Ist die Abfrage eventuell auch nur langsam wenn du einen filter gesetzt hast ?

Die vielen % und LIKE's sind auch nicht grade schnell

[/edit]

[marabu]

Hallo Kerim,

wenn die Funktion GetSQL dein SQL-Statement zusammenbaut, dann solltest du nicht die einzelnen Teile der SQL-Eigenschaft der Query zuweisen. Die Zuweisung geschieht doch wahrscheinlich an einer ganz anderen Stelle:

Delphi-Quellcode:

procedure TListeBuchungen.CBYearChange();
var
  dt: TDateTime;
begin
  Query.SQL.Text := GetSQL;
  dt := Now;
  Query.Open;
  ShowMessage(TimeToStr(Now - dt));
  ...
end;

Die Funktion GetSQL sollte dann besser so aussehen:

Delphi-Quellcode:

function TListeBuchungen.GetSql: string;
const
  fmt = 'SELECT b.buchunglfdnr, b.lfdnr, b.buchungnr, b.ticketnr, '
      + 'b.adrnr, b.veranstalter, b.hinabflugdatum, b.hinabflugzeit, '
      + 'b.hinabflugort, b.hinankunftort, b.hinflugnr, '
      + 'b.rueckabflugdatum, b.rueckabflugzeit, b.rueckabflugort, '
      + 'b.rueckankunftort, b.rueckflugnr, b.nettopreis, b.anzahlung, '
      + 'b.tax, b.mwst, b.rabatt, b.gewinnprozent, a.name, a.vorname '
      + 'FROM buchungen b, adressen a '
      + 'WHERE EXTRACT(YEAR FROM b.hinabflugdatum) = %d '
      + 'AND b.adrnr = a.adrnr %s '
      + 'ORDER BY %s '
      ; 
var
  iJahr: integer;
  s, sFilter, sOrder, sPattern: string;
begin
  with CBYear do
    iJahr := StrToInt(Items[ItemIndex]);

  sFilter := AnsiUppercase(Trim(edit1.Text)); 
  s := ''; 
  if sFilter <> '' then begin 
    sPattern := QuotedStr('%' + SFilter + '%'); 
    with query do 
      for i := 0 to FieldCount - 1 do 
        if Fields[i].DataType = ftString then begin 
          if s <> '' then s := s + ' OR'; 
          s := s + Format(' UPPER(%s) LIKE %s', [LowerCase(Fields[i].FieldName), sPattern]); 
        end; 
    s := 'AND ( ' + s + ' )'; 
  end; 

  sOrder := 'b.hinabflugdatum'; // hier kommt bestimmt noch mehr Code hin...

  Result := Format(fmt, [iJahr, sWhere, sOrder]);
end;

Die Filterung in jedem Textfeld mit einem Like-Pattern, bei dem vorne und hinten trunkiert wird, kann bei größeren Tabellen irgendwann zum Laufzeitproblem werden. Eventuell wirst du die Suche etwas mehr einschränken müssen.

Der Vergleich der Ausführungszeiten von deinem Programm und IBConsole ist bestimmt nicht korrekt. Grundsätzlich erwarte ich ähnliche Zeiten in deinem Programm. Die kurze Ausführungszeit in IBConsole werte ich als Indiz dafür, dass FireBird mit der Erstellung eines optimierten Zugriffsplanes kein Problem hat.

Grüße vom marabu

[testoli]

Hallo,

meiner Ansicht nach führst Du einen FullTable scan aus, d.h. obwohl du nur einige Daten haben möchtest und wahrscheinlich auch die Spalten indexiert hast, werden alle Daten komplett durchsucht. Das führt bei großen Datenmengen zu langsamen Abfragen.

Versuche die Abfragen ohne *,%, etc. zu erstellen. Achte besonders auf Integer Spalten, wenn dort ein % benutzt wird, führt es zu obigem.

LG
Oliver

[marabu]

Allerdings wird seine Abfrage in Null-Komma-FastNix im SQL-Fenster von IBConsole ausgeführt...

marabu

[shmia]

Die Ursache für den langsamen SELECT kann auch die WHERE-Bedingung:
WHERE EXTRACT(YEAR FROM B.HINABFLUGDATUM) = .... sein.
Die Datenbank muss für die Tabelle Buchungen einen Full-Table-Scan ausführen.
(für jeden Datensatz EXTRACT ausführen)
Würde man das Feld HINABFLUGDATUM indizieren und die WHERE-Bedingung so schreiben:
WHERE B.HINABFLUGDATUM>=:datum1 AND B.HINABFLUGDATUM <:datum2 könnte IB vom Index profitieren und ausserdem wäre die Abfrage viel flexibler.
Man könnte so z.B. alle Daten vom März 2004 abrufen.

Ich würde die Behauptung "Abfrage in Delphi langsam, aber in IB Console schnell" nur dann trauen,
wenn am Ende von GetSQL steht:
Clipboard.AsText := Result; und dann wirklich in der IB-Console mit Paste & Ausführen gearbeitet wird.

[Jasocul]

Hoffen wir mal, dass nicht die BDE oder ODBC zwischen Delphi und der Datenbank hängt.

[kerim80]

Hallo zusammen,

ich danke euch allen für eure bemühungen mir zu helfen, es waren sehr gute anregungen dabei die mir weitergeholfen haben
und habe das problem gelöst

ich habe nicht viel geändert!
das einzige was ich gemacht habe ist das query.sql.add durch query.sql.text zu ersetzen. wie folgt
und es ist genauso schnell wie in der IBCoonsole.
Hätte nicht gedacht das es an dem query.sql.add liegen könnte:

Delphi-Quellcode:

  
function TListeBuchungen.GetSql: string;
var i: integer;
    s, sFilter, sPattern: string;
begin
  query.sql.clear;
  query.sql.text := 'SELECT                ' + #10+
                    'B.BUCHUNGLFDNR,       ' + #10+
                    'B.LFDNR,              ' + #10+
                    'B.BUCHUNGNR,          ' + #10+
                    'B.TICKETNR,           ' + #10+
                    'B.ADRNR,              ' + #10+
                    'B.VERANSTALTER,       ' + #10+
                    'B.HINABFLUGDATUM,     ' + #10+
                    'B.HINABFLUGZEIT,      ' + #10+
                    'B.HINABFLUGORT,       ' + #10+
                    'B.HINANKUNFTORT,      ' + #10+
                    'B.HINFLUGNR,          ' + #10+
                    '                      ' + #10+
                    'B.RUECKABFLUGDATUM,   ' + #10+
                    'B.RUECKABFLUGZEIT,    ' + #10+
                    'B.RUECKABFLUGORT,     ' + #10+
                    'B.RUECKANKUNFTORT,    ' + #10+
                    'B.RUECKFLUGNR,        ' + #10+
                    '                      ' + #10+
                    'B.NETTOPREIS,         ' + #10+
                    'B.ANZAHLUNG,          ' + #10+
                    'B.TAX,                ' + #10+
                    'B.MWST,               ' + #10+
                    'B.RABATT,             ' + #10+
                    'B.GEWINNPROZENT,      ' + #10+
                    'A.NAME,               ' + #10+
                    'A.VORNAME             ' + #10+
                    '                      ' + #10+
                    'FROM BUCHUNGEN B, ADRESSEN A                                                                                 ' +#10+
                    'WHERE EXTRACT (YEAR FROM B.HINABFLUGDATUM) = ' +#39+ CBYear.Items.Strings[CBYear.itemindex] + #39            + #10+
                    //'WHERE B.HINABFLUGDATUM >= ' +#39 + '01.01.2005' +#39 + ' AND B.HINABFLUGDATUM <= ' +#39 + '31.12.2005' +#39  + #10+
                    'AND B.ADRNR = A.ADRNR                                                                                        ' +#10+
                    '%s                                                                                                           ';

  sFilter := AnsiUppercase(Trim(edit1.Text));
  s := '';
  if sFilter <> '' then begin
    sPattern := QuotedStr('%' + SFilter + '%');
    with query do
      for i := 0 to FieldCount - 1 do
        if Fields[i].DataType = ftString then begin
          if s <> '' then s := s + ' OR';
          s := s + Format(' UPPER(%s) LIKE %s', [LowerCase(Fields[i].FieldName), sPattern]);
        end;
    s := 'AND ( ' + s + ' )';
  end;

  Result := Format(query.SQL.Text, [s]) + ordersql;
end;

Die Filterung in jedem Textfeld mit einem Like-Pattern, bei dem vorne und hinten trunkiert wird, kann bei größeren Tabellen irgendwann zum Laufzeitproblem werden. [marabu]

das stimmt, aber ich warte erstmal ab bis die tabelle ein paar 1000 datensätze hat dann werde ich das sql-statement ein bisschen verkürzen.


danke nochmals an alle.
bis demnächst

HOSCAKALIN