Suchverfahren im dbms

[berndes00]

Moin Moin.

ich arbeite schon seit ein paar Jahren mit MSSQL Datenbank und hatte bisher keine Probleme.
Vor 2 Jahren habe ich ein paar Tabellen erstellt die von vielen Clients gefüllt werden, so ca. 15.000 Datensätze pro Tag. Am Anfang war es kein Problem, aber nun werden die Abfragen immer langsamer, da anscheinend das dbms eine lineare Suche durchführt. Leider muss nach Text-Spalten gefiltert werden.

Zu den Suchverfahren die intern im DBMS laufen, habe ich leider noch nicht wirklich was vernünftiges finden können. Bisher habe ich lineare Suche, binäre Suche, binärbäume, bei indezierte und nicht indezierten Daten gefunden. Aber was wird wann wo vom dbms eingesetzt? Kann man das beeinflussen? Bei einem indezierten aufsteigenden AutoWert wird es sicherlich die binäre Suche sein oder?

Ich würde da gerne mal durchblicken, nur leider finde ich nicht das richtig oder gebe nicht die richtigen Suchbegriffe ein. Habt ihr evtl. ein paar Webseiten, Bücher, Artikel oder sonst was die ihr empfehlen könnt?

Ich hoffe jemand einen ein Tipp für mich .

Hier hab ich mal noch ein konkretes Beispiel wo ich grade verzweifle:
ich habe 2 Tabellen A, und B. In A werden Belege gespeichert und in B die Belegpositionen. Nun gibt es für jeden Tag einen Beleg A von Typ L und von Typ S, und für jeden gibt es positionen. Dazu kommt, dass jeder Benutzer einen Beleg A von Typ L und S hat.

In A würde stehen:

BelegID     | User |Typ|   Datum    |
M8A-40910-L | M8A  | L | 02.01.2012 |
M8A-40910-S | M8A  | S | 02.01.2012 |
Z66-40910-L | Z66  | L | 02.01.2012 |
Z66-40910-S | Z66  | S | 02.01.2012 |

Ich hab als Schlüssel einen Text genommen, der aus dem User, dem Datum als Zahl und dem Typ besteht. In der Beleg-Tabelle hält es sich das noch in grenzen, aber dann bei den Positionen wirds leider richtig fies .

PosID        |BelegID     |Typ|PosNr|Datum     |weitereDaten
M8A-40910-L-1| M8A-40910-L| L |  1  |02.01.2012|...
M8A-40910-S-1| M8A-40910-S| S |  1  |02.01.2012|...
Z66-40910-L-1| Z66-40910-L| L |  1  |02.01.2012|...
Z66-40910-S-1| Z66-40910-S| S |  1  |02.01.2012|...

Jede Position wird durchnummeriert, darum wird im Schlüssel die BelegID nur um die Positionsnummer erweitert, so ist jeder DS eindeutig pro Tag.

Das ganz schlimme sind nund ie Abfragen auf die Tabellen.
In dem Programm, in dem die Tabellen genutzt werden wird ein Datumbereich vom User definiert, z. b. "von 02.01.2012 bis 31.12.2012".
Daraus hab ich bei den Belegen gemacht:

... WHERE Datum >= '02.01.2012' AND Datum <= '31.12.2012' ....

Mittlerweile ist das schon langsam. Nur das Programm geht mit den daraus geholten Daten her und filter die Positionen nach der BelegID:

... WHERE BelegID = 'M8A-40910-L' ...
-- nächster DS
... WHERE BelegID = 'M8A-40910-S' ...
-- nächster DS
... usw.

Das Programm liest die erste Zeile der Belege, dann Abfrage an die Positionen zu diesem Beleg, dann der nächste Beleg usw. Dabei wird wohl bei den Textfeldern eine lineare Suche angewendet, das wohl soviel bedeutet, dass alle Zeilen nach diesem Text durchsucht werden. Bei 100 Belegen würde der also 100mal die Positionen durchsuchen, das bei evtl. 200-250 zugreifenden Clients. Ergebnis: Ladezeit bis zu 30 Sekunden.

Ich suche nun nach etwas Hintergrundwissen für die internen Suchverfahren. Wie soll ich die Tabelle aufbauen, dass das Filtern schneller geht usw.
Momentan werden auch noch 10 Sekunden von der Hardware geschluckt, der Server ist relativ alt. Auf einem neuen Server hab ich das schon getestet und da dauert das suchen nur ca. 20 Sekunden.

Die Beleg-Tabelle habe ich schon angepasst, indem ich für das Datum die Zahl zusätzlich speichere und diese ist immer aufsteigend, so kann ich nach der Zahl suchen. Brachte im Test etwas, aber die Positionen sind die große Bremse .

Wäre super wenn ihr ein paar Tipps hättet

besten Dank!

 

[akretschmer]

Moin Moin.

ich arbeite schon seit ein paar Jahren mit MSSQL Datenbank und hatte bisher keine Probleme.
Vor 2 Jahren habe ich ein paar Tabellen erstellt die von vielen Clients gefüllt werden, so ca. 15.000 Datensätze pro Tag.

Vorweg: das ist peanuts.

Wäre super wenn ihr ein paar Tipps hättet

besten Dank!

Allgemein: auf Spalten, die in der WHERE-Condition stehen, ein Index.

Um zu sehen, wie eine DB die Suche durchführt, gibt es in vielen Systemen den EXPLAIN <Abfrage> - Befehl, der den Ausführungsplan anzeigt.

Du scheinst M$SQL zu nutzen: da kommt bestimmt bald der @ukulele vorbei ;-)

 

[ukulele]

Eigentlich habe ich dieser etwas knappen Antwort im Kern gar nicht mehr hinzuzufügen.

1.) Auf jedenfall einen Index der sowohl Datum_von als auch Datum_bis in der Reihenfolge beinhaltet. Eventuell auch noch als dritte Position die BelegID Spalte in den Index, da verstehe ich aber nicht ganz das vorgehen deines Clients.
2.) Natürlich einen Index auf die BelegID Spalte für sich genommen.

Ich verstehe nicht ganz was jetzt durchsucht wird, der Text der BelegID Spalte in der Belegtabelle oder Text in dein einzelnen Belegpositionen? Wenn letzteres könnte man sicher auch zu allen in der Suche enthaltenen Belegen die Belegpositionen in einer Abfrage holen.

In jedem Fall gilt auch für die Belegpositionen Tabelle sinnvolle Indexe zu setzen. Da wir hier aber durch die Belegtabelle die konkreten BelegIDs kennen wäre der Index BelegID, PosID wohl der richtige.

 

[berndes00]

Hi,
danke für eure Tipps.
Für dieses Beispiel wird mir das sicherlich helfen. Bisher habe ich aus unwissenheit immer nur ein Index gesetzt, den PK. Dieses Beispiel ist beim mir hochgekommen, da bei so wenigen Datensätzen die Ladezeit wirklich sehr lang ist. Und die BelegID in der Positionstabelle ist nur ein einfaches Textfeld, ich bin gespannt was das dingne als Index machen wird .

Gibt es da etwas allgemeines? Eine Spalte die ich durchsuchen möchte sollte Index sein? Kann ich irgendwie dem dbms mitteilen wie er suchen soll? Angenommen die Datumspalte als Zahl ist immer aufsteigend, da jeder neue Datensätze ein Tag weiter ist wird die Zahl immer größer. Jetzt würde sich doch bei der Spalte eine binäre Suche anbieten? Oder checkt das system das automatisch welche methode am schnellsten ist?

 

[akretschmer]

Gibt es da etwas allgemeines? Eine Spalte die ich durchsuchen möchte sollte Index sein? Kann ich irgendwie dem dbms mitteilen wie er suchen soll? Angenommen die Datumspalte als Zahl ist immer aufsteigend, da jeder neue Datensätze ein Tag weiter ist wird die Zahl immer größer. Jetzt würde sich doch bei der Spalte eine binäre Suche anbieten? Oder checkt das system das automatisch welche methode am schnellsten ist?

Wenn Du in der Spalte suchst, einen Index drauf hast und genug Zeilen in der Tabelle sind, wird ein Index-Scan sinnvoll sein und von der DB gemacht. Wenn jeder Tag nur einmal vorkommen kann und DARF, dann wird die DB einen UNIQUE INDEX erlauben und besser nutzen können. Wenn das streng sortiert ist, kann das auch die DB erkennen.

So, und nun nochmals: EXPLAIN nutzen. Zumindest in PG ist das sehr, sehr aussagekräftig. Für PG könnt ich hier noch ins Detail gehen, mit internen Statistiken und wie Pläne berechnet werden. Aber ich denke, das ist hier nicht nötig.

 

[berndes00]

auf jeden Fall werde ich EXPLAIN ausprobieren und mir ansehen was dabei rauskommt. Der Server ist noch ein MSSQL Server 2001, mal sehen was dabei rumkommt. Ich dachte es gäbe irgendwo ein bisschen Hintergrundwissen für das was die Datenbank so macht. Ich such weiter, wenn die Datenbank sowas automatisch erkennt und das optimale/schnellste Suchverfahren nimmt dann ist das schön, trotzdem möchte ich gerne wissen warum und wie das gemacht wird. Und evtl. kann ich das automatische erkennen beschleunigen.
Danke für die Hilfe

 

[akretschmer]

auf jeden Fall werde ich EXPLAIN ausprobieren und mir ansehen was dabei rauskommt. Der Server ist noch ein MSSQL Server 2001, mal sehen was dabei rumkommt. Ich dachte es gäbe irgendwo ein bisschen Hintergrundwissen für das was die Datenbank so macht. Ich such weiter, wenn die Datenbank sowas automatisch erkennt und das optimale/schnellste Suchverfahren nimmt dann ist das schön, trotzdem möchte ich gerne wissen warum und wie das gemacht wird. Und evtl. kann ich das automatische erkennen beschleunigen.
Danke für die Hilfe

Kein Problem, google mal nach "explaining explain postgresql". Das ist natürlich spezifisch für PostgreSQL. Sicher wirst Du für M$SQL aber ähnliche Dinge finden...

 

[ukulele]

Ob ein Index für das Durchsuchen einer Spalte wirklich sinnvoll ist weiß ich erlich gesagt nicht genau. Aber ich weiß das dein Select eine WHERE Bedingung hat / braucht und auf den Spalten bedarf es einen Index in einer sinnvollen Reihenfolge.