Geschwindigkeitsproblem

[jbg]

Ich habe da ein kleines aber sehr nervtötendes Geschwindigkeitsproblem.

In einer Liste befinden sich Primarykeys, deren Datensätze bestimmten Kriterien entsprechen (Sowas wie [Tbl1.Feld > 10 und Tbl2.Feld < 10] oder Tbl3.Feld = 10)
Ein JOIN ist in dem Fall nicht möglich, da mehrere Kriterien kombiniert die Ergebnismenge ergeben, wobei diese sich teilweise ausschließen können: (((k1 AND k2) OR k3) AND NOT k4). Das ist alles schon durchgespielt und ich habe mich mit einem Gegenbeispiel überzeugen lassen, dass da kein JOIN möglich ist.
In diesem Primarykey-Ergebnismenge Schritt würde ein Auslesen der Datensätze zu einem rießigen Datenaufkommen führen (weil kein JOIN möglich). Wenn z.B. die das erste Kriterium lautete: Alle Kunden die Älter als 20 Jahre sind, kann das zu 10000 Datensätzen führen. Und da wäre mein momentaner Ansatz um einiges schneller.

Die Aufgabe ist nun die Datensätze für jeden Primarykey aus verschiedenen Tabellen zu hole, um daraus eine ClientDataSet Tabelle zu konstruieren. Mein langsamer Ansatz stellt nun an jede dieser verschienenen Tabellen pro Primarykey eine Prepared SELECT-Anfrage mit WHERE PrimKey='primkey'. Dabei kann eine oder mehrere Zeile herauskommen, die dann zu einer im CDS zusammengefasst werden (Kommaliste). Das ist aber trotz dem Prepared SELECT nicht schnell und kann bei vielen Primarykeys schon Richtung 1 Stunde gehen.

Eine Überlege von mir ist, mehrere Primkeys-Anfragen zu einer einzigen zusammenzufassen ala

Code:

SELECT field1, field2, ... from table1 WHERE PrimKey='primkey1' OR PrimKey='primkey2' OR ...

Nur gibt es da ein kleines Problem. Da macht Oracle nicht wirklich mit. Zum einen gerate ich an einen Bug von i9, bei dem die DB abfängt zu arbeiten und nicht mehr aufhört, obwohl das Ergebnis nur 2 Einträge liefern sollte. Und zum anderen kann ich das nicht für 10000 Primarykeys auf einmal machen. Wobei man das durch geschicktes Verteilen bereinigen kann (z.B. 10 Primkeys auf einmal). Aber der i9 Bug ist da das größte Problem. Leider kann ich den Bug im Moment nicht reproduzieren, weil sich die Tabellen mittlerweile geändert haben. Aber mit der Ungewissheit, dass er eintreten kann, kann man das fertige Produkt nicht auf den Kunden loslassen.

Irgendwelche Vorschäge, andere Ansätze?

[marabu]

Hallo Andreas,

die Geschichte mit dem JOIN kommt etwas verworren rüber. Natürlich geht ein JOIN - halt ein THETA JOIN und kein EQUI JOIN. Ganz klar, dass die Ergebnismengen deutlich größer werden, wenn du einen THETA Join verwendest (Theta wäre dabei ein Prädikat mit einem Vergleichsoperator, bei Gleichheit hast du einen EQUI JOIN).
Ein RDBMS wird immer die Ergebnismenge berechnen, bevor es dir einzelne Tupel zurück liefert - wenn Oracle das auch will, dann wirst du wenig dagegen tun können. Interbase berechnet zwar auch zuerst die Ergebnismenge, aber mit einer Stored Procedure bist du in der Lage die Ergebnismenge gestückelt (tupelweise) zurückzugeben. Dazu gibt es in Interbase SQL den Befehl SUSPEND. Durchforste mal dein Handbuch zum Thema Stored Procedure - vielleicht kann Oracle das ja auch?

Grüße vom marabu

[Robert_G]

Zitat von marabu:

Dazu gibt es in Interbase SQL den Befehl SUSPEND. Durchforste mal dein Handbuch zum Thema Stored Procedure - vielleicht kann Oracle das ja auch?

Es gibt keine Selectable Procedure in Oracle. (Und wird sie in den nächsten 20 Jahren wohl auch nicht geben )

@Andreas
Dumme Frage, aber hast du wirklich Indizes auf den Feldern?
Bei migrierten DBs von Ora<9 hat man das "Problem" dass Ora9 nicht mehr automatische Indizes auf Foreign Keys vergibt.
Außerdem kommt es nur noch bescheiden mit nicht/schlecht normalisierten Daten klar.
Die JOIN Syntax ist bei Ora übrigens nicht nötig, da der Optimizer genau erkennt wie du wo welche Tabellen verknüpft.
Bei 30 Tabellen in der From clause wird zwar der erste Lauf durch die Optimierung langsamer, aber effektiv macht es sich nicht wirklich bemerkbar.

Was du machen könntest, wäre diese Liste mit Werten als Array an ein Package zu übergeben. (wenn es immer eine Liste von x =1 or X = 2 or X = 3,... ist)
Dann nimmst du als rückgabewert wieder eine Liste von Werten. Auf die Art ersparst du dir sämtliche round trips da nur einmal Eine Liste hingeschickt und eine zurückgeschickt werden muss.

Wenn du DOA benutzt, kann du diese Oracle Objekte an deine Delphi Applikation binden. (siehe: TOracleObject, TOracleQuery.SetComplexVariable, TOracleQuery.GetComplexVariable)

ich habe mir eben zum Testen diese Beispieltabelle angelegt:

SQL-Code:

create table JbgTable
(
  ID        INTEGER not null,
  NAME      VARCHAR2(255),
  FIRSTNAME VARCHAR2(255)
);
alter table JBGTABLE
  add constraint JBGTABLE_PK primary key (ID)
  using index;

Jetzt braucht man einen Objekttypen, der einem Datensatz entspricht:

SQL-Code:

create or replace type TJbgItem as object
(
  Id        Integer,
  Name      VarChar(255),
  FirstName VarChar(255)
)

Die Liste, die man zurückbekommen will:
create or replace type TJbgItemList as table of TJbgItem; Die Liste, die man der Funktion übergibt:
create or replace type TIntegerList as table of Integer; Das Package[1]:

SQL-Code:

create or replace package JbgTest is

  function FetchValues(aIds in TIntegerList) return TJbgItemList;

end JbgTest;

SQL-Code:

create or replace package body JbgTest is
  cursor getRecords(iId in Integer) is
    SELECT TJbgItem(iId, Name, FirstName)
    FROM   JbgTable t
    WHERE  t.Id = iId;

  function FetchValues(aIds in TIntegerList) return TJbgItemList is
    result TJbgItemList := TJbgItemList();
  begin

    result.Extend(aIds.Count);

    for i in aIds.First .. aIds.Last loop
      open getRecords(aIds(i));

      FETCH getRecords
        INTO result(i);

      close getRecords;

    end loop;

    return result;
  end;

end JbgTest;

[1] eine einfache Funktion würde auch reichen, bei Packages wird aber wiederholter Zugriff auf einen darin liegenden Cursor etwas schneller.

Aus Mangel an DOA hier zu Hause habe ich es mit einem simplen PL/SQL Script getestet:

SQL-Code:

declare
  Ids    TIntegerList := TIntegerList();
  result TJbgItemList;
begin
  Ids.Extend(:Records);

  for i in Ids.First .. Ids.Last loop
    Ids(i) := dbms_Random.Value(:MinValue, :MaxValue);
  end loop;

  result := JbgTest.FetchValues(Ids);

  :Returned := result.Count;
end;

Die Tabelle habe ich mit 2*10^6 zufälligen VorName/NachName Kombis füllen lassen.
Wenn ich :Records 1000 übergebe (Wodurch 1000 zufallige IDs gesucht werden) braucht er keine 150 Millisekunden.
Und das obwohl mein kleiner "Server" nur ein Virtual Server auf einer kleineren Maschine ist!

Hihi, endlich mal wieder etwas mit Ora gespielt.

[jbg]

Danke (euch beiden). Das kann ich leider erst am Montag ausprobieren. Aber 150 Millisekunden klingt doch schon um einiges besser als meine 1 Sekunde für 10 Datensätze.

[Robert_G]

Oh Sh*t!
Ich sehe jetzt erst, dass du dbX nimmst.
Ich weiß nicht, ob du da Oracle Objekte verwenden kannst.

btw: bei den 14X ms war das Füllen mit Zufallswerten auch schon drin. (dbms_Random ist ziemlich gehackt und nicht sonderlich schnell )
Aber die Übertragung der Liste vom Server kostet auch wieder etwas Zeit...

[jbg]

Zitat von Robert_G:

Ich sehe jetzt erst, dass du dbX nimmst.
Ich weiß nicht, ob du da Oracle Objekte verwenden kannst.

Das Aufrufen ist sicherlich nicht das Problem (TSQLStoredProc). Wie aber die TJbgItemList zurückkommt, dass wäre schon interessant. Und vor allem, wie man die IntegerList übergibt.

Gut das ich normalerweise nicht soviel mit Datenbanken am Hut habe und wenn, dann nur einfache Dinge.

[Robert_G]

Zitat von jbg:

Das Aufrufen ist sicherlich nicht das Problem (TSQLStoredProc). Wie aber die TJbgItemList zurückkommt, dass wäre schon interessant. Und vor allem, wie man die IntegerList übergibt.

Da ich Ora & Delphi nur mit DOA kenne, habe ich mir von DOA mal einen wrapper für das Package anlegen lassen (schnell installiert...):

Delphi-Quellcode:

type
(*
package JbgTest is

  function FetchValues(aIds in TIntegerList) return TJbgItemList;

end JbgTest;
*)
  TJbgtest = class(TOracleCustomPackage)
  public
    function  Fetchvalues(Aids: TOracleObject): TOracleObject;
  published
    property Name;
    property Session;
    property Cursor;
  end;

var
  DefaultPLSQLTableSize: Integer = 100; // Default size of a PL/SQL Table

implementation

// JBGTEST.FETCHVALUES
function TJbgtest.Fetchvalues(Aids: TOracleObject): TOracleObject;
begin
  Result := TOracleObject.Create(Session, '"TJBGITEMLIST"', '');
  ThreadAcquire;
  try
    GetQuery;
    OCPQuery.DeclareVariable('function_result', otObject);
    OCPQuery.SetComplexVariable('function_result', Result);
    OCPQuery.DeclareVariable('AIDS', otObject);
    OCPQuery.SetComplexVariable('AIDS', Aids);
    OCPQuery.SQL.Add('begin');
    OCPQuery.SQL.Add('  :function_result := "JBGTEST"."FETCHVALUES"(AIDS => :AIDS);');
    OCPQuery.SQL.Add('end;');
    OCPQuery.Execute;
  except
    ThreadRelease;
    Result.Free;
    raise;
  end;
  ThreadRelease;
end;

Wenn du/deine Firma öfter mit Delphi + Ora zu tun habt solltet ihr euch DOA wirklich genauer ansehen.
Man kann damit Probleme lösen, die ohne Objektbindung nicht möglich bzw zu langsam wären.

Und weil ich gerade Lust hatte noch eine kleine Konsolen App (Aber für try, finall hatte ich keine Lust ):

Delphi-Quellcode:

var
   OracleSession        : TOracleSession;
   JbgItemList, IntegerList: TOracleObject;
   i, j                 : Integer;
begin
   OracleSession := TOracleSession.Create(nil);
        //hier wären connection strings...

   OracleSession.LogOn();

   IntegerList := TOracleObject.Create(OracleSession, 'TIntegerList', '');

   // 10´000 ZufallsIds
   FillWithRandoms(IntegerList, 10000);

   with TJbgTest.Create(nil) do
   try
      Session := OracleSession;
      JbgItemList := Fetchvalues(IntegerList);
   finally
      Free();
   end;

   for i := 0 to JbgItemList.ElementCount - 1 do
      with JbgItemList.ObjElements[i] do
      begin
         for j := 0 to AttrCount - 1 do
            Write(Attrs[j].Value, #9);
         WriteLn;
      end;

   ReadLn;
end.

Der springt auch bei 10000 so schnell über "JbgItemList := Fetchvalues(IntegerList);", dass man gar nicht mitkriegt was da passiert.

Zitat:

Gut das ich normalerweise nicht soviel mit Datenbanken am Hut habe und wenn, dann nur einfache Dinge.

Sehe ich mittlerweile auch nicht anders.

[jbg]

Zitat von Robert_G:

Wenn du/deine Firma öfter mit Delphi + Ora zu tun habt solltet ihr euch DOA wirklich genauer ansehen.

Gibt's das auch für Kylix? Wenn nein, dann ist das ein no-go.
Aber ich denke, dass das Programm sowieso nicht von dbExpress weggehen wird, weil da einfach schon zu viel programmiert wurde.

[Robert_G]

Hier gibt's die Demo: http://www.allroundautomations.com/downloads.html
Kylix steht da auch. Aber sicher, eine Zugriffsbibliothek für eine einzige Methode zu kaufen wäre unsinnig.