Laden...

Frage zu Datenbankdesign bei dymanischen Strukturen

Erstellt von telnet vor 10 Jahren Letzter Beitrag vor 10 Jahren 1.653 Views
T
telnet Themenstarter:in
327 Beiträge seit 2006
vor 10 Jahren
Frage zu Datenbankdesign bei dymanischen Strukturen

verwendetes Datenbanksystem: SQL Server 2008

Hallo,

ich habe grade folgendes Problem: Ich designe eine Datenbank bzw. eine Applikation zur Anzeige der Daten, wobei ich mit "flexiblen" Datenstrukturen zu kämpfen habe.

Ich habe immer einen Basisdatensatz, zu dem es dann mehrere Attribute gibt, wobei ein Attribut immer ein paar aus Name und Wert ist. Da es nicht zu jedem Basisdatensatz das gleiche Set an Attributen gibt habe ich jetzt folgende DB-Struktur:
Basistabelle 1:n Zuordnungstabelle
Zuordnungstabelle 1 : n Attributname
Zuordnungstabelle 1 : n Attributwert

Sowohl die Namen kommen mehrfach vor als auch die Werte (sind teilweise vordefiniert).

Jetzt will ich die Daten aber in meiner Anwendung anzeigen lassen, wobei die Attribute zum Basisdatensatz aber einfach als zusätzliche Spalten angezeigt werden sollen. Dazu habe ich eine Stored Procedure geschrieben, die die Attribute transponiert und an die Datensätze aus der Basistabelle dranhängt. Die kann ich dann in mein Programm reinladen und direkt an ein DataGrid binden.

Das Problem ist jetzt, dass der ganze Vorgang relativ lange dauert, da ich teilweise 100000 Datensätze auf einmal lade.. bei 20 Attributen inkl. Werten dauert die ganze Sache mit dem Transponieren dann schon richtig lange.

Gibt es für so eine Problemstellung evtl. einen anderen Ansatz? Mir würde nur einfallen, statt der o.g. Struktur eine Datenbanktabelle dynamisch aufzubauen, d.h. die Attribute in einer eigenen Tabelle speichern, zu der nach Bedarf dynamisch Spalten hinzugefügt oder entfernt werden, und die dann einfach mit einer Beziehung
Basistabelle 1 : n Attributtabelle in der DB liegt.

Über Anregungen, wie man so was am besten designen kann / sollte, wäre ich sehr dankbar!

742 Beiträge seit 2005
vor 10 Jahren

Ohne Details zu kennen, kann man glaube ich kaum eine perfekte Antwort geben, aber hier ein paar Ideen, vll. passt ja eine davon.

  1. Hinterfragen, warum du gleichzeitig 100.000 Einträge brauchst
  2. Die Transformation in der Anwendung machen.
  3. Alle dynamischen Attribute als serialisiertes XML, JSON, BSON, Binär etc. in eine Spalte kodieren und Transformation in der Anwendung durchführen.
  4. Eien Datenbank verwenden, die dynamischere Strutkuren zulässt, z.B. MongoDB
16.807 Beiträge seit 2008
vor 10 Jahren

Bist Du an MSSQL gebunden? Wenn nicht würde ich auf eine NoSQL Datenbank umsteigen, die eben kein festes Schema erfordert.
Auch in Sachen Performance sind das Welten zum (EF+)-MSSQL.

3.511 Beiträge seit 2005
vor 10 Jahren

Entweder eine NoSql (und so wie es klingt, passt das wie Faust aufs Auge), oder mit dem XML Spalten Typ im SQL Server arbeiten. Ist etwas gefummel, funktioniert aber ganz gut. Mit einem passenden XSD kann auch ein Index auf den Spalten abgelegt werden.

Die Transformation würde ich dann mit einer .NET CLR SP machen. Weil du hier wesentlich bessere Möglichkeiten mit dem Umgang von XML hast, und auch dynamische Tabellen als Ergenis lassen sich damit hervorgangend umsetzen.

"Jedes Ding hat drei Seiten, eine positive, eine negative und eine komische." (Karl Valentin)

T
telnet Themenstarter:in
327 Beiträge seit 2006
vor 10 Jahren

Die Option NoSql habe ich leider nicht - ich bin an einen SQL Server gebunden..
Die Sache mit dem XML-Spaltentypen habe ich mir angesehen und Testweise implementiert...

In den XML Spalten steht jetzt z.B. folgendes XML drin


<PROP NAME="ATTRIB1" VALUE="Wert1" />
<PROP NAME="ATTRIB2" VALUE="2653" />
<PROP NAME="ATTRIB3" VALUE="Test0815" />

D.h. pro Eigenschaft habe ich ein Element, das einen Namen und einen Wert hat.
Speichern ist der Daten ist relativ easy, beim rausziehen im richtigen Format hab ich noch etwas Probleme:

Ich will im Endeffekt eine Tabelle bekommen, die die Werte der Attribute NAME als Spaltennamen hat:

ATTRIB1 ATTRIB2 ATTRIB3
Wert1 2653 Test0815

Was ich jetzt gemacht habe ist erst mal alle Werte des Property NAME über alle Einträge rauszusuchen (DISTINCT)...
Mit diesen Spaltennamen habe ich mir dann dynamisch ein Query zusammengebaut, das mir dann die Daten in genau der richtigen Form ausgibt - allerdings denke ich nicht, dass das eine wirklich performante Lösung ist - es wird ja dann für jede Spalte ein XPath Query auf die Daten ausgeführt...

Habt ihr da vielleicht noch einen Tipp oder ein Stichwort für mich, wie ich es möglichst Performant schaffe, die Daten aus einer Struktur wie oben so auszugeben, dass ich ein Attribut für die Spaltennamen und ein anderes für die Werte verwende?

3.511 Beiträge seit 2005
vor 10 Jahren

Hallo,

du kannst auf einen Schlag ja mittels XPath alle Key/Value Paare auslesen. Das geht dann via


SELECT 
 C.value('(@NAME)[1]', 'nvarchar(100)') AS [Name],
 C.value('(@VALUE)[1]', 'nvarchar(100)') AS [Value]
FROM Table
CROSS APPLY XmlColumn.nodes('/PROPS/PROP') AS T(C)

Funktioniert, wenn dein XML wie folgt aussieht


<PROPS> 
  <PROP NAME="ATTRIB1" VALUE="Wert1" />
  <PROP NAME="ATTRIB2" VALUE="2653" />
  <PROP NAME="ATTRIB3" VALUE="Test0815" />
</PROPS>

Ansonsten musst du den XPath Ausdruck unter nodes('xxxx') anpassen.

Das Ergebnis kannst du dann in eine temp. Tabelle schmeißen und via dynamischen PIVOT kippen.

Das ist auf jeden Fall performanter als die "für jeden Wert ein Query" Lösung.

Die effizienteste Methode bei solchen Modellen ist allerdings eine eigene .NET CLR Stored Procedure. Denn hier kannst du direkt eine Tabelle ausgeben und mittels .NET Mittels die XML Manipulation vornehmen.

Gruß
Khalid

"Jedes Ding hat drei Seiten, eine positive, eine negative und eine komische." (Karl Valentin)

T
telnet Themenstarter:in
327 Beiträge seit 2006
vor 10 Jahren

Ok ich hatte gedacht vielleicht ist das direkte Auslesen schneller aber der direkte Verleich zeigt:

  • Methode 1: Dynamisches Query erstellen, bei dem jede Spalte durch einen XPath-Ausdruck definiert ist: 18s
  • Methode 2: Alle Key-Value Paare rausladen und dann mit einem dynamischen Query pivotieren: 4s

Das ganze bei 3200 Rows mit je 27 Spalten / Attributen im XML... ganz schöner Hammer der Unterschied...

Dann werd ich das mit der CLR SP nochmal probieren - da muss ich mich aber erst mal schlau machen - hab ich bisher noch nie gebraucht.

Vielen Dank an der Stelle schon mal für die wirklich hilfreichen Tipps!

T
telnet Themenstarter:in
327 Beiträge seit 2006
vor 10 Jahren

Also ich hab das mit der CLR Stored Procedure mal ausprobiert und zu meiner Überraschung war ich mit dieser Methode 3 langsamer als in der vorher erwähnten Methode 2. (43s vs. 33s bei ca. 25.000 Records mit 27 Spalten)

Jetzt habe ich dann noch eine Methode 4 realisiert:
Die Daten werden direkt als XML unverändert aus der DB in die Clientanwendung geladen (SqlReader) und die Daten per XDocument ausgelesen und in eine DataTable geschrieben, wobei die Attribute direkt als Spalten umgesetzt werden.
Damit bin ich jetzt bei ca. 2,6s für 25.000 Records a 27 Spalten.

Ich denke, dass die CLR SP wahrscheinlich schneller wäre, wenn der Code noch weiter optimiert würde (ist er nämlich im Test nicht gewesen).
Aber rein aufgrund des Geschwindigkeitsvergleichs und der Tatsache, dass ich keine zusätzliche DLL im SQL Server registrieren muss, werde ich schätzungsweise bei #4 bleiben und die Umsetzung der XML-Daten in das gewünschte Format im Client selbst realisieren. Was sich ganz gut trifft ist dabei, dass die Leute, die viele Daten laden, meist sehr leistungsfähige Rechner haben.

Danke auf jeden Fall für eure wertvollen Tipps!