Hallo Leute,

ich wurde gefragt, ob es möglich ist eine Liste mit Messpunkten von einem Messgerät auf eine Datenbank zu übertragen und eine Sufu für die Messpunke zu programmieren. Klar geht das, nur ist es so, das pro Messung ca. 65000 Messpunkte (X,Y) anfallen. Im Laufe der Zeit könnten somit locker 650 Millionen XY Datensätze anfallen. Bei der Anzahl an Datensätzen fehlt mir einfach die Erfahrung wie es um die Performance bei einem SQL-Server bestellt ist.

Die Struktur der DB wäre recht einfach:

Tabelle mit Stammdaten:
ID <-Primärschlüssel
Name
Info
USW...


Tabelle mit Messpunkten:
ID (Int) <-Schlüssel aus Stammdaten
XValue (Float)
YValue (Float)


Kann ich das einfach so machen, oder würdet ihr da anders vorgehen?

Inwiefern ist die Performance von der Server Hardware abhängig? Aktuell steht ein MS-SQL-Server mit zwei Quad-Core Prozessoren und 16 GB Arbeitsspeicher zur Verfügung.

BTW: Mein Rechner kopiert gerade die ersten 1000 Dummys mit je 65000 Punkten in die DB, das dauert...... Vielleicht kann ich mir die Frage bald selbst beantworten. Trotzdem würde ich gerne eure Meinung/Erfahrungen dazu hören!

Zitat:

Kann ich das einfach so machen, oder würdet ihr da anders vorgehen?

Kommt auf die Fragestellung an die du an die Daten stellst. Ein Auswählen der Messreihe über die Stammdaten sollte fix sein (passende Indexierung vorausgesetzt), ein raussuchen bestimmter Messpunkte über x,y eher nicht. Fließkommazahlen sind aufgrund ihres unscharfen Charakters denkbar ungeeignet um darüber zu suchen. Ich habe keine Erfahrung in der Größenordnung aber ich kann mir nicht vorstellen das das in ansprechender Performance endet. Letztlich hängt hier die Performance nicht (besser kaum) am gewählten Datenbankmodell oder der gewählten Hardware sondern ausschließlich am gewählten relationalen Modell und das hängt natürlich explizit an der Fragestellung ab die du an die Daten stellen willst. Also überlege dir sehr genau welche Abfrageoperationen schnell sein soll und was demgegenüber vernachlässigbar ist. Dann kann mann sich eine passende relationale Struktur und Indexierung ausdenken.

Wenn x,y Koordinaten darstellen oder ähnlich in einem Zusammenhang stehen ließe sich vielleicht was mit dem geometry Datentyp und Spatial Indizes machen. Ich wär zumindest an einem Vergleich in deiner Größenordnung interessiert

Vielen Dank schon mal für deine Anregung!

Also, die Suche soll über die XY Koordinaten laufen und mir die IDs zurückgeben die das Suchkreterium erfüllen. Zudem müssen die Koordinaten zum zeichen aus der Tabelle geholt werden.

Es wird also nur drei Queries auf die Tabelle geben.

1.Insert, das ist nicht das Problem. Das Einfügen dauert ca. 15 Sekunden. Das sollte vertretbar sein und könne als Thread abgearbeitet werden, so das der User nicht darauf warten muss.

2.Suchen: SELECT ID FROM [Xplorer].[dbo].[XYValues] WHERE XValue > Min AND XValue < Max AND YValue < Amplitude

3.Laden: SELECT YValue , XValue FROM [Xplorer].[dbo].[XYValues] WHERE ID = IDValue


Ich lade gerade weiter Dummys in die DB, das dauert..... Aber ich denke das muss sein, damit ich mir ein tatsächliches Bild von der Sache machen kann.

Zitat:

2.Suchen: SELECT ID FROM [Xplorer].[dbo].[XYValues] WHERE XValue > Min AND XValue < Max AND YValue < Amplitude

Wenn du immer über einen Bereich von XValues suchst wäre ein Clustered Index auf XValues hilfreich. Der Index wäre aber nachträglich extrem schwer auf die Daten anwendbar. Ein Clustered Index ist die physische Sortierung der Tabelle was natürlich hier hilfreich ist wenn man immer sequentiell von der Platte lesen kann und nicht zu random gezwungen ist wie bei einem klassischen Index. Das kostet aber natürlich beim einfügen.

Ralf Jansen hat folgendes geschrieben :

Wenn du immer über einen Bereich von XValues suchst wäre ein Clustered Index auf XValues hilfreich. Der Index wäre aber nachträglich extrem schwer auf die Daten anwendbar. Ein Clustered Index ist die physische Sortierung der Tabelle was natürlich hier hilfreich ist wenn man immer sequentiell von der Platte lesen kann und nicht zu random gezwungen ist wie bei einem klassischen Index. Das kostet aber natürlich beim einfügen.

Das hört sich gut an! Ich werde versuchen mich in die Richtung schlau zu machen. Ist das "kompliziert" ?

BTW: Ich habe aktuelle 1100 Dummys in der Datenbank und ein SELECT DISTINCT ID FROM [Xplorer].[dbo].[XYValues] WHERE XValue > Min AND XValue < Max AND YValue > Amplitude dauert 7 Sekunden. Ich bin gespannt ob die benötigte Zeit für die Abfrage proportional mit der Anzahl der Datensätze steigt....

So, ich habe versucht ein Clustered Index auf der Tabelle XYValues festzulegen. Leider bekomme ich beim Insert mittels EntityModell eine Exception:

EntitySet 'ClusteredXYValues' kann nicht aktualisiert werden, denn es hat eine DefiningQuery, und im <ModificationFunctionMapping>-Element ist kein <InsertFunction>-Element zur Unterstützung des aktuellen Vorgangs vorhanden.


Ich beschreibe mal wie ich vorgegangen bin:

Die Tabelle Stammdaten steht in einer 1:n Beziehung mit der Tabelle XYValues.

Stammdaten
ID <- PK
Name
Usw..

XYValues
ID <- steht in Beziehung mit ID aus Stammdaten
XValues
YValues


Die Tabelle XYValues hat keinen PK. Wo sollte der auch drauf? Der macht doch keinen Sinn wenn XValues aufsteigend weggeschrieben wird, oder?

Im SQL Server Management Studio ->

Indize/Schlüssel -> Hinzufügen -> Namen vergeben......

Allgemein:
Ist eindeutig = nein
Spalten = XValues(ASC)
Typ = Index

Identität:
Name =Clustered_XValue
Als Clustered erstellen = Ja

Alle weiteren Felder kann ich nicht bearbeiten. (Außer die Felder Füllspezifikation/Statistiken neu berechnen.)


Ist das überhaupt richtig was ich da gemacht habe?

Zitat:

Die Tabelle XYValues hat keinen PK. Wo sollte der auch drauf? Der macht doch keinen Sinn wenn XValues aufsteigend weggeschrieben wird, oder?

Wenn du nicht gerade Storage Optimierung betreibst und ein paar Bytes sparen willst würde ich immer einen definieren. Für jeden Mist sei es simple sql joins, irgendwelche Frameworks die mit den Daten arbeiten wollen/sollen (wie z.B ein ORM wie EF) funktionieren nur richtig bei Tabellen die einen PK haben. Solltest du einen PK heute nicht brauchen dann vermutlich morgen, spätestens übermorgen.

Zitat:

Ist das überhaupt richtig was ich da gemacht habe?

Ich denke ja. Wobei du zuerst das Entity Framework verschwiegen hast. Und ich tatsächlich Zweifel habe das das das richtige ist. Die hast da scheinbar 2 isolierte Tabellen die hochoptimiert sein sollten damit das halbwegs schnell geht. Und bei EF geht es primär um convenience und dann irgendwann ..... ganz lange Pause .... dummeldiddeldummm ....... um Performance. Für die paar Operationen scheint mir EF völlig überdimensioniert und kontraproduktiv.

So jetzt läuft es. Ich denke da ist beim testen etwas durcheinander geraten. Der Schlüssel ließ sich nicht mehr löschen, da gab es ein Time out vom Server. Kurz die Datenbank gelöscht, Tabellen neu angelegt, mit PK Die Indizierung so festgelegt wie bereits beschrieben und nun funktioniert das Insert mit dem EF.

Das EF werde ich nicht benutzen, für die paar Queries kann ich auch mit SQL arbeiten. Ich benutze das nur zum testen, das ist halt so simpel damit auf die DB zu gehen. ( Ich glaube in ein paar Jahren kann keiner mehr SQL, so wie heute DOS )

Ein Insert dauert jetzt spürbar länger, das muss dann als Thread im Hintergrund passieren.

Ich werde in den nächsten Tagen ein paar 1000 Dummys in die DB schreiben und dann hier berichten wie lange eine Abfrage mit dem Clustered Index dauert.


Also, 1000 Dank für deine Unterstützung! Ich würde dir ja gerne ein Bier aus geben, aber das geht hier ja leider nicht

So geht virtuelles Bier

Danke für das Bier

So, ich habe aktuell > 167 Millionen Datensätze in der Datenbank (SQL-Server Express) , jetzt ist sie voll. Die Daten können auf alle Fälle akzeptabel abgefragt werden. Ein Abfrage dauert aktuell 23 Sekunden. In anbetracht der großen Datenmengen, sollte das Verhalten auf Akzeptanz stoßen, zumindest wenn den Usern klar ist, was da passiert.

Eine weitere Idee um die Performance für die Suche zu verbessern, wäre die Messpunktdicht zu verringern. Das würde aber bedeuten, das die Daten z.T. doppelt gehalten werden müssen. Ich würde eine Tabelle mit der vollen Messpunktdichte zum zeichnen anlegen und eine reduzierte zum Suchen. Das muss ich mal mit dem Leuten diskutieren ob und wie weit eine Reduktion der Daten möglich ist.

Wenn es beim Suchen nicht so sehr auf Präzision ankommt, könnte man sich mit einem Trick behelfen.

Wenn es z.B. genügt, das 3 Nachkomma zum Suchen genügen, könnte man die Meßwerte X und Y mit 1000 multiplizieren und als Integer mit in die Meßwert-Tabelle schreiben. Zum Suchen nimmst du dann die indizierten INT-Werte, das dürfte einige Größenordnungen schneller gehen, denke ich.

OlafSt hat folgendes geschrieben :

Wenn es beim Suchen nicht so sehr auf Präzision ankommt, könnte man sich mit einem Trick behelfen. Wenn es z.B. genügt, das 3 Nachkomma zum Suchen genügen, könnte man die Meßwerte X und Y mit 1000 multiplizieren und als Integer mit in die Meßwert-Tabelle schreiben. Zum Suchen nimmst du dann die indizierten INT-Werte, das dürfte einige Größenordnungen schneller gehen, denke ich.

Danke für den Hinweis! Das sollte sicher möglich sein, das werde ich mit in die Diskussion nehmen und auch gleich mal testen, damit ich den Unterschied vergeleichen kann

Hallo Leute,

ich habe die DB auf Int umgestellt und bin jetzt wieder bei 167 Millionen Datensätzen. Die benötigte Zeit für die Abfrage ist identisch (23"). Kann es sein, dass das Clustering so gut greift, das der Unterschied nicht spürbar ist? Allerdings habe ich das Gefühl, das die Größe der DB schmaler ausfällt, ganz sicher bin ich mir aber nicht. Ich muss mal schauen wie die Die Log-Datei aufbläht wenn ich alle Datensätze lösche. Gestern ist mir die Festplatte dabei vollgelaufen

Zum testen vielleicht das Recovery Model erstmal auf simple stellen Point-In-Time Recovery etc. wirst du in einer Testdatenbank ja wohl kaum brauchen.

Zitat:

Allerdings habe ich das Gefühl, das die Größe der DB schmaler ausfällt, ganz sicher bin ich mir aber nicht

Ein int hat 4 ein float 8 byte. Das sollte sich bemerkbar machen.

Ralf Jansen hat folgendes geschrieben :

Zum testen vielleicht das Recovery Model erstmal auf simple stellen Point-In-Time Recovery etc. wirst du in einer Testdatenbank ja wohl kaum brauchen. Zitat: Allerdings habe ich das Gefühl, das die Größe der DB schmaler ausfällt, ganz sicher bin ich mir aber nicht Ein int hat 4 ein float 8 byte. Das sollte sich bemerkbar machen.

Die DB steht auf simple. Ich denke die werde ich auch bei einem Produktiv-System so lassen, die Log-Datei war mehr als 4* so groß wie die DB-Datei.

Christoph1972 hat folgendes geschrieben :

So, ich habe versucht ein Clustered Index auf der Tabelle XYValues festzulegen. Leider bekomme ich beim Insert mittels EntityModell eine Exception: EntitySet 'ClusteredXYValues' kann nicht aktualisiert werden, denn es hat eine DefiningQuery, und im <ModificationFunctionMapping>-Element ist kein <InsertFunction>-Element zur Unterstützung des aktuellen Vorgangs vorhanden. Ich beschreibe mal wie ich vorgegangen bin: Die Tabelle Stammdaten steht in einer 1:n Beziehung mit der Tabelle XYValues. Stammdaten ID <- PK Name Usw.. XYValues ID <- steht in Beziehung mit ID aus Stammdaten XValues YValues Die Tabelle XYValues hat keinen PK. Wo sollte der auch drauf? Der macht doch keinen Sinn wenn XValues aufsteigend weggeschrieben wird, oder? Im SQL Server Management Studio -> Indize/Schlüssel -> Hinzufügen -> Namen vergeben...... Allgemein: Ist eindeutig = nein Spalten = XValues(ASC) Typ = Index Identität: Name =Clustered_XValue Als Clustered erstellen = Ja Alle weiteren Felder kann ich nicht bearbeiten. (Außer die Felder Füllspezifikation/Statistiken neu berechnen.) Ist das überhaupt richtig was ich da gemacht habe?

Eine kleine Ergänzung zum clustern. Bei der Spalte mit dem PK muss IsClustered = false und bei der zu sortierenden Spalte muss IsClustered = true gesetzt werden. Sonst werden die Werte nicht auf- oder absteigend weggeschrieben.

EDIT: Mist, eigentlich wollte ich nur den Beitrag editieren

Hallo Leute,

ich möchte das Thema noch mal aufgreifen. Und zwar habe ich für die Suche nun eine Tabelle mit stark reduzierten Messpunkten, es sind quasi nur noch die Maximum Punkte in der Tabelle. Damit konnte ich das Datenvolumen um ca. 75% reduzieren. Zum suchen reichen die Daten völlig aus. Nun zu meiner Frage: Eigentlich wollte ich zum zeichnen der Messpunkte eine Tabelle mit den vollständigen X,Y Daten anlegen. Wie bereits erwähnt, würden im laufe der Zeit, wirklich viele Daten in dieser Tabelle anfallen, bis zu 650000000 halt. Mir kam gerade die Idee, die Messpunktreihe zum zeichnen immer Binär weg zuspeichern. Dann hätte ich pro Messung in der Tabelle zum zeichnen nur einen Datensatz, statt 65000. Irgend wie finde ich die Idee gerade gar nicht schlecht, da ich doch immer die vollständige Messreihe abrufen würde, der Rest wird über die Reduzierte Tabelle abgedeckt. Was haltet ihr von der Idee? Die Tabelle bleibt so definitiv übersichtlicher. Somit sollte doch einen bessere Performance erzielt werden, oder?

Über eure Meinung würde ich wie immer sehr freuen!

Aus deiner Beschreibung würde ich in etwa diese Struktur ableiten:

Die Abfragen könnten so etwas schneller werden.
Der Speicherbedarf dürfte sich erst mal nur unwesentlich ändern, aber du könntest die Messwerte komprimieren, bevor diese im Blob-Feld gespeichert werden und damit viel Platz sparen.
Allerdings steigt der Aufwand beim Hinzufügen neuer Messwerte erheblich.

Vielen Dank für deine Rückmeldung!

Ja, ich habe es jetzt ähnlich gemacht. Das mit dem komprimieren könnte ich vielleicht noch dazu nehmen. Stellt sich nur die Frage, ob es notwendig ist? Eine Messreihe hat ca. 0.03 MB, ich glaube das kann ich vernachlässigen, oder? Mir ging es auch primär darum, einen möglichst schnellen Zugriff auf die Daten zu erzielen. Ich denke ganz schlecht stehe ich nicht da.

Ein Eintrag in der Tabelle T_MESSPUNKTE kostet vieleicht nicht mal 100Byte, die zugehörige Messreihe 30.000Byte.
Für 10.000 Messreihen sind das schon 300MB, da lohnt sich komprimieren.
Es geht ja nicht in erster Linie um den Platz auf der Festplatte, Datenbanken müssen auch regelmäßig gesichert werden.