DB Design für viele Variablen

[r4ffael]

Guten Tag,

folgende Ausgangssituation vorhanden:

Ich benutze MS SQL 2012 es geht mir hier aber um das generelle DB design!
Ich habe eine Menge von Objekten ( > 300.000 ) und wachsend. Dann hab ich eine Menge von ca 10.000 Variablen (Menge fest). Die Variablen sind im Bezug auf die Objekte Boolesche Werte, d.h. ein Object bestizt entweder die Variable (true) oder eben nicht. Im Schnitt sind pro Objekt ca 200 Variablen auf True, d.h. 9800 auf false.


Zur Zeit sieht meine Datenbank so aus:

Tabelle 1 (Objekte)
id int primary key, name string, string farbe, string länge
(Farbe könnte auch ein foreign key sein ich weiß)....

Beispiel:

Tabelle 1
1234, Kreis, Grün, 30 cm
1256, Viereck, schwarz, 5 cm

Tabelle 2 (Variablen)
id string, klartext string

Bsp:

Tabelle 2
AB1, skizze
C3F, rund
C3E, eckig

Tabelle 3 (Variablen pro Objekt)
idT1 int foreign key (auf Tab1 id) INDIZIERT , idT2 string foreign key (auf Tab2 id)

Bsp:
Tabelle 3
1234, AB1
1234, C3F
1256, AB1

Nun mein Problem:

Wenn ich Abfragen habe wie:

Select idT2 from Tabelle 3 where idT1 = 1234
und danach
Select idT2 from Tabelle 3 where idT1 = 1256

dauert dies ca 0,006 Sekunden / Abfrage. Ich muss aber in einem Schwung meist alle 300.000 Objekte Abfragen. Dies dauert dann also 30 Minuten.

Meine Idee war nun

Tabelle 3 löschen und in Tabelle 1 eine weiter Spalte

Tabelle 1 (neu)
id int primary key, name string, string farbe, string länge, string weitere Attribute

Beispiel:
1234, Kreis, Grün, 30 cm, {AB1;C3F;....}

Die Abfrage geht so weit auch sehr flott, Problem ist nun habe ich keine Normalenform mehr und wie löse ich:

Select id from Tabelle 1 where Attribut AB1 = true

??? Soll ich ein Mix aus beiden sachen machen? Dann speicher ich allerdings alles redundant ab. Kennt jemand ein best practice?

MfG

 

[akretschmer]

Nun mein Problem:

Wenn ich Abfragen habe wie:

Select idT2 from Tabelle 3 where idT1 = 1234
und danach
Select idT2 from Tabelle 3 where idT1 = 1256

dauert dies ca 0,006 Sekunden / Abfrage. Ich muss aber in einem Schwung meist alle 300.000 Objekte Abfragen. Dies dauert dann also 30 Minuten.

Moment, wenn Du eh ALLE abfragst, dann kannst auch gleich 'select idT2 from Tabelle3', also ohne WHERE, machen. Dann hast EINEN sequentiellen Lauf und nicht 300.000 einzelne (Index)-Läufe, die IN SUMME mehr brauchen als ein Seq-Scan.


Andreas

 

[r4ffael]

Ja das stimmt. Leider brauche ich nicht immer alle (vielleicht etwas falsch ausgedrückt... Mein Fehler. Also ich brauch oft nur 90 % oder auch mal nur 50.

 

[akretschmer]

Ja das stimmt. Leider brauche ich nicht immer alle (vielleicht etwas falsch ausgedrückt... Mein Fehler. Also ich brauch oft nur 90 % oder auch mal nur 50.

Okay, und wie lange dauert die Abfrage mit z.B. einerm WHERE ... NOT IN (...), oder aber generell alle abfragen und in der Applikation die nicht benötigten verwerfen? Kommt halt drauf an, was einfacher ist. Aber ein SeqScan über 300000 Rows, von denen ich dann 30000 wegwerfe ist immer noch sehr wahrscheinlich schneller als 270000 einzelne Index-Scans. Zumindest erwarte ich dies...

 

[r4ffael]

Naja was wirklich schnell geht, ist das oben beschriebene. Ich pack die ganzen Attribute in eine Zelle, direkt an das Objekt gekoppelt. Dann mach ich die 300000 scans und werf den rest weg. Das dauert keine 5 Minuten. Problem ist nur, wenn ich jetzt andersrum suchen will, also alle Objekte die das Attribut x haben, oder die Attribute x, y und z, dann komm ich mit der Variante nicht weiter.

 

[akretschmer]

Naja was wirklich schnell geht, ist das oben beschriebene. Ich pack die ganzen Attribute in eine Zelle, direkt an das Objekt gekoppelt. Dann mach ich die 300000 scans und werf den rest weg. Das dauert keine 5 Minuten. Problem ist nur, wenn ich jetzt andersrum suchen will, also alle Objekte die das Attribut x haben, oder die Attribute x, y und z, dann komm ich mit der Variante nicht weiter.

Hrm. Okay.

Das könne (unter PG) eine Anwendung für hstore sein: http://www.postgresql.org/docs/9.2/interactive/hstore.html :

This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined

Aber das wird es bei Dir wohl so nicht geben...

 

[ukulele]

Also erstmal fallen mir zwei Dinge ein:
a) Ich denke mal alle Tabellen haben einen sinnvollen Index.
b) So wie ich das verstehe gibt es zu jeder Kombination aus Tabelle 1 und 2 einen Eintrag in Tabelle 3, die entweder auf true oder false steht. Also ist Tabelle 3 die Abbildung der n:m Beziehung zwischen Tabelle 1 und 2 und jede erdenkliche Beziehung ist hier eingetragen?

 

[r4ffael]

zZ. hat nur die Tabelle 3 einen Index. Und in Tabelle 3 stehen auch nur die positiven (true) drin. Sie hat zZ also "nur" 300.000 * 200 Einträge statt 300.000 * 10.000

 

[ukulele]

Das sollte auch so sein denn die anderen haben ja effektiv keine Beziehung / Zuordnung.

Ich nehme an der Index auf Tabelle 3 geht auf den Fremdschlüssel idT1?

 

[r4ffael]

ja, genau das hab ich oben so beschrieben.

Tabelle 3 (Variablen pro Objekt)
idT1 int foreign key (auf Tab1 id) INDIZIERT , idT2 string foreign key (auf Tab2 id)

 

[Tommi]

Hallo zusammen,

ich denke, dass das DB-Design so absolut in Ordnung ist. Es ist variabel und kann viele erdenkliche Kombinationen abdecken.
In Tabelle1 und Tabelle2 sind Stammdaten enthalten, Tabelle3 ist somit eine reine Referenz-Tabelle, die Kombinationen aus Tabelle1 und Tabelle2 zusammenführt.
Das Problem ist hier nicht das DB-Design sondern vielmehr die Optimierung der Abfragen.

Zunächst sollten alle Tabellen einen Index haben (siehe Beitrag von ukulele)! Nichts bringt so viel Performance wie ein sinnvoller Index.
Für immer wiederkehrende Abfragen sollte man sich eine View anlegen. Eine View ist ja nichts anderes als eine gespeicherte SELECT-Abfrage.
Für diese erstellt der SQL-Server (der manchmal tatsächlich recht schlau ist) einen Ausführungsplan, der ebenfalls gespeichert wird.
Dieser Ausführungsplan berechnet über Statistiken die optimalen Abfrage-Reihenfolge, was die Performance um Einiges erhöht.

Nach dem angegebenen Beispiel ist eigentlich ja nur die Information von tatsächlichen Variablen-Zuweisungen interessant, die sich aus der Referenz-Tabelle ergeben.
Ich würde eine Gesamt-View erstellen, die alle Objekte und die diesen evtl. zugewiesenen Variablen wiedergibt.
Die Anweisung wäre dann wie folgt:

SELECT ...
FROM Tabelle1 Objekte
LEFT OUTER JOIN Tabelle3 Referenz
        INNER JOIN Tabelle2 Variablen
                ON Variablen.id = Referenz.idT2
        ON Tabelle1.id=Referenz.idT1

Diese View kann man dann mittels WHERE-Klausel nach Bedarf abfragen.
Eine WHERE-Klausel schränkt auch Ergebnisse innerhalb der View ein. D.h., dass eine Abfrage der View mit einer WHERE-Klausel, mit der die Anzahl der Ergebnis-Datensätze eingeschränkt wird, immer schneller ist als die Abfrage der gesamten View ohne WHERE-Klausel.


Viele Grüße,
Tommi

 

[akretschmer]

Zunächst sollten alle Tabellen einen Index haben (siehe Beitrag von ukulele)! Nichts bringt so viel Performance wie ein sinnvoller Index.
Für immer wiederkehrende Abfragen sollte man sich eine View anlegen.

Naja, bitte Indexe nicht mit der Gieskanne vergeben. Ein Index bringt Vorteile bei Abfragen, wenn er genutzt werden KANN. Das hängt aber u.a. von der Abfrage ab. Ein Index kann aber unterm Strich auch zur Bremse werden, wenn er a) bei Abfragen nutzlos ist und b) er durch viele Schreib-Operationen aktuell gehalten werden soll.

Ich kenne jetzt M$SQL nicht, bei PG gibt es Statistiken, wie oft user-defined Indexe genutzt wurden, mit einigen weiteren Details. In Kunden-DB's hab ich da schon durch ausmisten (also löschen nicht benötigter Indexe) einiges erreichen können.

Soweit ich das sah, hatte der Fragesteller für seine Abfragen durchaus passende Indexe. Aber vielleicht hab ich auch was übersehen.

Eine View ist ja nichts anderes als eine gespeicherte SELECT-Abfrage.
Für diese erstellt der SQL-Server (der manchmal tatsächlich recht schlau ist) einen Ausführungsplan, der ebenfalls gespeichert wird.
Dieser Ausführungsplan berechnet über Statistiken die optimalen Abfrage-Reihenfolge, was die Performance um Einiges erhöht.

Die Erstellung des Abfrageplanes ist i.d.R. der absolut geringste Teil der Laufzeit einer Abfrage. Ich weiß jetzt nicht, ob M$SQL *wirklich* den Plan speichert, aber da gibt es eine Stolperfalle: zur Planzeit kennt der Planner NICHT die wirklichen WHERE-Bedingungen. Das kann u.U. zu völlig kaputten Plänen führen. Manchmal hilft es daher auch, zu erzwingen, daß Pläne erst mit konkreten Parametern erstellt werden (z.B. in pl/pgsql-Funktionen via EXECUTE).

Andreas

 

[Tommi]

Hallo Andreas,

selbstverständlich gebe ich dir recht, Indizes sollte man nicht "verstreuen" wie es einem beliebt und bei einer Tabelle mit 10 Spalten und 5 verschiedenen Indizes sollte man dann auch mal über eine Optimierung nachdenken. Aber ich habe ja auch was von einem "sinnvollem Index" geschrieben.

Der SQL Server beschreibt den PKEY zwar auch als Index, der aber nicht unbedingt als Abfrageoptimierung angesehen werden kann/darf/sollte.
Dieser PKEY-Index wird als Clustered Index angelegt und kann keine Felder mitnehmen, was ja für eine Abfrageoptimierung oftmals sinnvoll ist.

Daher sollte jede Tabelle, insbesondere bei größeren Datenmengen, auch einen sinnvollen Index besitzen, der entsprechende Abfragen hierauf beschleunigt!
Im Beispiel des Thread-Erstellers haben Tabelle1 und Tabelle2 keinen Index (laut Angabe ist nur Tabelle3 indiziert).

Und somit komme ich mal wieder auf die Abfragepläne im SQL Server.
Die werden tatsächlich" gespeichert" (gecashed), und zwar für jedes Object, das einen solchen benötigt, sogar für ad-hoc-Afragen aus dem Management Studio.
Hierzu gibt es sogar Abfragemöglichkeiten (siehe hier: http://msdn.microsoft.com/de-de/library/ms187404.aspx)

Der SQL-Server ist tatsächlich nicht ganz so dumm wie man meinen mag und auch aller Vorbehalte einiger in Bezug auf MS-Technologien.
Der gecashte Ausführungsplan einer Abfrage gilt als Grundlage für die Abfrage-Ausführung. Hier wird auch festgehalten, welcher Trigger am sinnvollsten von der Abfrage genutzt wird.
Wenn es 3 ähnliche Trigger gibt, berechnet der SQL Server die günstigste und sinnvollste Nutzung (Statistik, Statistik, Statistik).

Dieses Verhalten der Ausführungsplan-Berechnung kann man im Management-Studio auch recht gut erkennen.
Bei einer komplexen Abfrage, die zum ersten Mal ausgeführt wird und für die noch kein ähnlicher Ausführungsplan exisitiert,
benötigt diese Ausführung um einiges länger als bei der nächsten Ausführung der Abfrage.
Im Management-Studio kann man sich für ad-hoc-Abfragen diesen Ausführungsplan auch gleich mit anzeigen lassen.
Hier werden sogar Indizes vorgeschlagen, wenn diese die vorliegende Abfrage beschleunigen würden und noch nicht existieren.

Die Statistiken des SQL Servers, die zur Berechnung herangezogen werden, sind Performance- und System-Statistiken, die nichts mit der Häufigkeit einer Nutzung zu tun haben.

Der Vorteil der gecashten Ausführungspläne liegt im Übrigen auch auf der Hand: beim Abfragen einer View mit einem WHERE-Filter wird der gecashte Ausführungsplan dieser View an der entsprechend sinnvollen Stelle um diesen Filter erweitert. Es wird zwar ein neuer Ausführungsplan für diese ad-hoc-Abfrage erstellt, für diesen ist jedoch der gecashte Ausführungsplan die Grundlage.
(kurzer Verweis auf den obne genannten "ahnlichen Ausführungsplan").
Daher wird eine Abfrage auf eine View mit einem WHERE-Filter auch schneller als die Abfrage aller Daten dieser View.


Viele Grüße,
Tommi

 

[Tommi]

Wir(ich) sind(bin) jetzt ganz schön vom Thema abgekommen.

Meine Empfehlung an den Thread-Erstelle ist weiterhin, das DB-Design nicht zu ändern sondern entsprechende Optimierungen zu implementieren, mit denen die gewünschten Anfroderungen (z.B. in Form von Abfragen) vernünftig umgesetzt werden können.


Viele Grüße,
Tommi