Autor |
Beitrag |
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: So 16.11.14 23:08
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!
_________________ Gruß
Christoph
|
|
Ralf Jansen
Beiträge: 4700
Erhaltene Danke: 991
VS2010 Pro, VS2012 Pro, VS2013 Pro, VS2015 Pro, Delphi 7 Pro
|
Verfasst: Mo 17.11.14 00:00
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
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Mo 17.11.14 18:25
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.
_________________ Gruß
Christoph
|
|
Ralf Jansen
Beiträge: 4700
Erhaltene Danke: 991
VS2010 Pro, VS2012 Pro, VS2013 Pro, VS2015 Pro, Delphi 7 Pro
|
Verfasst: Mo 17.11.14 18:49
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.
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Mo 17.11.14 23:24
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....
_________________ Gruß
Christoph
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Do 20.11.14 22:21
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?
_________________ Gruß
Christoph
|
|
Ralf Jansen
Beiträge: 4700
Erhaltene Danke: 991
VS2010 Pro, VS2012 Pro, VS2013 Pro, VS2015 Pro, Delphi 7 Pro
|
Verfasst: Do 20.11.14 22:47
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.
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Do 20.11.14 23:23
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
_________________ Gruß
Christoph
|
|
icho2099
Beiträge: 101
Erhaltene Danke: 12
WIN XP, WIN 7, WIN 10
Delphi 6 Prof, Delphi 2005, FPC
|
Verfasst: Do 20.11.14 23:43
So geht virtuelles Bier
Für diesen Beitrag haben gedankt: Ralf Jansen
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Sa 22.11.14 11:41
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.
_________________ Gruß
Christoph
|
|
OlafSt
Beiträge: 486
Erhaltene Danke: 99
Win7, Win81, Win10
Tokyo, VS2017
|
Verfasst: Sa 22.11.14 21:06
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.
_________________ Lies, was da steht. Denk dann drüber nach. Dann erst fragen.
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Sa 22.11.14 21:30
_________________ Gruß
Christoph
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: So 23.11.14 21:50
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
_________________ Gruß
Christoph
|
|
Ralf Jansen
Beiträge: 4700
Erhaltene Danke: 991
VS2010 Pro, VS2012 Pro, VS2013 Pro, VS2015 Pro, Delphi 7 Pro
|
Verfasst: So 23.11.14 22:16
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.
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: So 23.11.14 23:30
_________________ Gruß
Christoph
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Do 07.05.15 20:34
_________________ Gruß
Christoph
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Fr 15.05.15 08:05
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!
_________________ Gruß
Christoph
|
|
Blup
Beiträge: 173
Erhaltene Danke: 43
|
Verfasst: Mo 18.05.15 11:14
Aus deiner Beschreibung würde ich in etwa diese Struktur ableiten:
Quelltext 1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16:
| CREATE TABLE T_MESSPUNKTE ( ID INTEGER NOT NULL, BESCHREIBUNG VARCHAR(40), XMIN NUMERIC(9,3), XMAX NUMERIC(9,3), YMIN NUMERIC(9,3), YMAX NUMERIC(9,3), DATA BLOB SUB_TYPE 0 SEGMENT SIZE 80 );
ALTER TABLE T_MESSPUNKTE ADD CONSTRAINT PK_MESSPUNKTE PRIMARY KEY (ID);
CREATE INDEX IDX_MESSPUNKTE_XMAX ON T_MESSPUNKTE (XMAX); CREATE INDEX IDX_MESSPUNKTE_XMIN ON T_MESSPUNKTE (XMIN); CREATE INDEX IDX_MESSPUNKTE_YMAX ON T_MESSPUNKTE (YMAX); CREATE INDEX IDX_MESSPUNKTE_YMIN ON T_MESSPUNKTE (YMIN); |
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.
|
|
Christoph1972
Beiträge: 690
Erhaltene Danke: 16
VS2015 Pro / C# & VB.Net
|
Verfasst: Mo 18.05.15 17:09
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.
_________________ Gruß
Christoph
|
|
Blup
Beiträge: 173
Erhaltene Danke: 43
|
Verfasst: Mo 18.05.15 18:00
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.
|
|