[Erledigt] [SQlite2] Sqlite_array_query lagt ...

[LastCoder]

Hallo,

habe folgendes Problem:

In meiner freien Aktenverwaltung "OpenLawyer's" (OpenLawyer's | Download OpenLawyer's software for free at SourceForge.net) gibt es eine Funktion, die Wiedervorlagen in der Zukunft anzeigen kann (es handelt sich dabei um Termine, wann etwas mit Akten noch getan werden muss).

Nun erfolgt folgende Abfrage über die Datenbank (SQLite2):

$aQuery=sqlite_array_query($hDatabase,"SELECT users.username, aktenzeichen.aznr, aktenzeichen.azjahr, wvtypen.typ, wvtypen.id, wiedervorlagen.zeitunddatum, wiedervorlagen.information, wiedervorlagen.nr FROM users, aktenzeichen, wvtypen, wiedervorlagen WHERE wiedervorlagen.status=0 AND wiedervorlagen.terminID=wvtypen.id AND wiedervorlagen.azID=aktenzeichen.id AND users.id=wiedervorlagen.bearbeiterID AND Wiedervorlagen.bearbeiterID='".$iUser."' AND wiedervorlagen.zeitunddatum<".$iTermin." AND wvtypen.id=".$iWvTypID." ORDER BY wiedervorlagen.zeitunddatum"); 

In der Tabelle wiedervorlagen gibt es ca. 5000 Einträge. Im Großen und Ganzen halte ich das nicht für besonders viel, wenn man bedenkt, was SQL Datenbanken und SQLite angeblich handeln soll.

Jedenfalls dauert die Abfrage inzwischen so lange, dass ein Server Timeout vom Browser angezeigt wird (bei Opera, bei Firefox kommt gar nichts) (Fehler kommt auf einem 1,7 GHz Single-Core, 1 GB RAM, unter Win wie Linux, mit LIGHTTPD und PHP5).

Gibt es irgendeine Möglichkeit, den Prozess zu beschleunigen, hat einer eine Idee, was ich vielleicht falsch gemacht habe ... ???

So long

LastCoder

[thomas_w]

Vermutlich sind auf den SQLite Tabellen keine oder falsche Indices angelegt. Mit Hilfe von Indices kann SQLite die Abfrage beschleunigen.

Vorschlag:

CREATE INDEX sx_wiedervorlagen_01 ON wiedervorlagen (zeitunddatum)

Diesen Index anlegen und dann den SQL nochmal probieren.

Ich hoffe SQLite kann dies verstehen, ich habe selber kein SQLite.

Grüße
Thomas

[ChrisB]

Gibt es irgendeine Möglichkeit, den Prozess zu beschleunigen,

Erste Frage in solch einem Fall immer: Hast du Indexe gesetzt?

hat einer eine Idee, was ich vielleicht falsch gemacht habe ... ???

Bist du mir jetzt böse, wenn ich sage, dich nicht mal selber ein Stück weit zu informieren vor dem Fragen wäre falsch gewesen ...?

Google "sqlite performance" -> SQLite Optimization FAQ

[LastCoder]

Hi,

Bist du mir jetzt böse, wenn ich sage, dich nicht mal selber ein Stück weit zu informieren vor dem Fragen wäre falsch gewesen ...?

Google "sqlite performance" -> SQLite Optimization FAQ

Oh mann, das ist eine trollartige Anmerkung .. ich habe das Teil 2005 als Hobby (und nicht etwa als Fulltime-Programmierer sondern neben meiner Anwaltstätigkeit) aus dem Boden gestampft (ausschließlich für mich und habs später für lau der Community überlassen..wieviele open-source anwaltliche Aktenverwaltungen kennst du denn ?) .. vielleicht gab es da die SQLITE Optimization FAQ noch nicht ? Und wer kommt schon auf den Gedanken, dass nach 5 Jahren Dauereinsatz (ohne jegliche Probleme von Anfang an) die zugrundeliegende Datenbank in die Knie geht ? Bei 20 Tabellen, wobei die größte lediglich 5000 Einträge hat ..

Die konstruktiven Anregungen prüfe ich gern ..

So long

LastCoder

[LastCoder]

Hi Thomas,

Vermutlich sind auf den SQLite Tabellen keine oder falsche Indices angelegt. Mit Hilfe von Indices kann SQLite die Abfrage beschleunigen.

Vorschlag:

CREATE INDEX sx_wiedervorlagen_01 ON wiedervorlagen (zeitunddatum)

Diesen Index anlegen und dann den SQL nochmal probieren.

Ich hoffe SQLite kann dies verstehen, ich habe selber kein SQLite.

Grüße
Thomas

Hatte ich bisher gar nicht auf dem Radar

Muss ich das einmalig erledigen und für jede Tabellen und jeden Tabelleneintrag, so dass man es bei der erstmaligen Initialisierung der Datenbank machen kann, oder muss das regelmäßig wiederholt werden ?

'Wenn ich das richtig verstehe mit den Indizes, ist es eine interne Angelegenheit für die Datenbank, so dass die Bezeichner wurscht sind ?

So long

LastCoder

[thomas_w]

Richtig. Sind die Indices mal angelegt, verwaltet die Datenbank diese selbst. D.h. einmal angelegt und gut. Was für einen Namen der Index bekommt ist im Prinzip egal, er muss aber eindeutig sein. Sinnvoll ist es daher, den Tabellennamen als Teil des Indexnamen zu verwenden.
Indices empfehlen sich auf alle Primay Keys (PK) und Foreign Keys (FK). In Deinem Beispiel z.B.

wiedervorlagen.terminID
wvtypen.id
...
etc.

Also zu allen Spalten, mit denen die Tabellen verbunden werden. MySQL und andere Datenbanken legen auf den PK oder FK automatisch einen Index an, ich weiß nicht, ob SQLite dies ebenso tut.

Mein Tuning-Tipp mit dem Index auf

CREATE INDEX sx_wiedervorlagen_01 ON wiedervorlagen (zeitunddatum);

beschleunigt in erste Linie das sortieren nach Datum und die Suche

..
wiedervorlagen.zeitunddatum<".$iTermin." 
..
ORDER BY wiedervorlagen.zeitunddatum

Grüße
Thomas

[LastCoder]

Danke !

Hi Thomas,

großartig ! Das hat das Problem vorerst gelöst ! Die Datenbank ist zwar um 100% angewachsen, dafür rennt sie wieder wie vorher .. meinem Kollegen fällt ein Stein vom Herzen !

Der Index für Wiedervorlagen->zeitunddatum allein hat nix gebracht, ging erstaunlicherweise dann gar nicht mehr . Habe dann für alle Tabellen die Primary Keys sowie die regelmäßig wieder vorkommenden Akten-Identifier Indizes angelegt und dann lief's ..

Falls wider Erwarten irgendjemand da draußen OpenLawyer's im Einsatz hat, stelle ich den "Speedup-Fix" mal hier ein:

<?PHP

$sDatabasepath='../db/';

$sDb='akten.opl';

$sDatabase=$sDatabasepath.$sDb;

function Error($Serrormsg)

{

    print("<html><head><script language='JavaScript'>function err() { alert('$Serrormsg'); }</script><body onload='err()'></body></html>");

}


echo "Update OpenLawyer's 0.99 [16.07.10 - Speedup database fix] <br/>";



$hDatabase=@sqlite_open($sDatabase,0777,$sError);

if ($hDatabase==FALSE) 

{

    Error("SQLite Error: ".$sError." ! Call Administrator !");

    die;

} 

sqlite_query($hDatabase,"CREATE INDEX IDX_aktenzeichen_01 ON aktenzeichen (id)");
sqlite_query($hDatabase,"CREATE INDEX IDX_akten_01 ON akten (azID)");

sqlite_query($hDatabase,"CREATE INDEX IDX_wiedervorlagen_01 ON wiedervorlagen (nr)");
sqlite_query($hDatabase,"CREATE INDEX IDX_wiedervorlagen_02 ON wiedervorlagen (status)");
sqlite_query($hDatabase,"CREATE INDEX IDX_wiedervorlagen_03 ON wiedervorlagen (azID)");
sqlite_query($hDatabase,"CREATE INDEX IDX_wiedervorlagen_04 ON wiedervorlagen (zeitunddatum)");
sqlite_query($hDatabase,"CREATE INDEX IDX_wiedervorlagen_05 ON wiedervorlagen (terminID)");
sqlite_query($hDatabase,"CREATE INDEX IDX_wiedervorlagen_06 ON wiedervorlagen (bearbeiterID)");

sqlite_query($hDatabase,"CREATE INDEX IDX_users_01 ON users (id)");

sqlite_query($hDatabase,"CREATE INDEX IDX_rechnungsnummer_01 ON rechnungsnummer (id)");
sqlite_query($hDatabase,"CREATE INDEX IDX_rechnungsnummer_02 ON rechnungsnummer (azID)");


sqlite_query($hDatabase,"CREATE INDEX IDX_aktenvita_01 ON aktenvita (nr)");
sqlite_query($hDatabase,"CREATE INDEX IDX_aktenvita_02 ON aktenvita (azID)");

sqlite_query($hDatabase,"CREATE INDEX IDX_kosten_01 ON kosten (nr)");
sqlite_query($hDatabase,"CREATE INDEX IDX_kosten_02 ON kosten (azID)");

sqlite_query($hDatabase,"CREATE INDEX IDX_posteingang_01 ON posteingang (nr)");
sqlite_query($hDatabase,"CREATE INDEX IDX_posteingang_02 ON posteingang (azID)");

sqlite_query($hDatabase,"CREATE INDEX IDX_postausgang_01 ON postausgang (nr)");
sqlite_query($hDatabase,"CREATE INDEX IDX_postausgang_02 ON postausgang (azID)");


sqlite_query($hDatabase,"CREATE INDEX IDX_adressen_01 ON adressen (id)");

sqlite_query($hDatabase,"CREATE INDEX IDX_beteiligte_01 ON beteiligte (id)");
sqlite_query($hDatabase,"CREATE INDEX IDX_beteiligte_02 ON beteiligte (azID)");
sqlite_query($hDatabase,"CREATE INDEX IDX_beteiligte_03 ON beteiligte (adressenID)");

sqlite_query($hDatabase,"CREATE INDEX IDX_wvtypen_01 ON wvtypen (id)");

sqlite_close($hDatabase);

echo "Done.<br/>";

?>

So long

Lastcoder