Ersatz für GUID als Primärschlüssel

[shmia]

In meiner Datenbank gibt es mehrere Tabellen, die eine GUID als Primärschlüssel verwenden.
Das Problem ist nun, dass die Anzahl der Datensätze in die Hunderttausende geht und eine Tabelle über 80 Felder hat.
Durch die Verwendung einer GUID als Primärschlüssel werden die Datensätze, die nacheinander eingebucht wurden
über die gesamte Tabelle verstreut.

Später werden dann die Verbuchungen eines Tages in einem Bericht aufbereitet.
Die Performance nimmt mit steigender Grösse der DB rapide ab, weil die Datensätze, die zusammen verarbeitet werden sollen,
nicht beieinander liegen.
Wenn ich das früher gewusst hätte, hätte ich nie GUIDs als PK verwendet. :(

Mein Plan ist nun, die GUIDs durch selbstgenerierte 128-Bits Schlüssel zu ersetzen.
Eine komplette Abkehr von diesen 128-Bit Schlüssel ist nicht möglich, da viele Datenbanken im Gigabyte-Grösse in freier Wildbahn existieren und eine Änderung des Schema zu viel Aufwand bedeutet.
(vielleicht mal in ferner Zukunft...)
Spezielle Funktionen des SQL Servers können nicht benützt werden, da auch andere Datenbanken unterstützt werden sollen.

Mein neuer 128-Bit Schlüssel soll so aussehen:

Code:

64 Bit - Systemzeit (UTC) über GetSystemTimeAsFileTime()
32 Bit - den rechten Teil aus den MAC der Netzwerkkarte
32 Bit - laufender Zähler

Damit soll folgendes erreicht werden:
Zeitlich nahe Verbuchungen sind auch in der Tabelle örtlich nah.
Kollisionen zwischen verschiedenen Rechnern werden durch den MAC-Anteil verhindert.
Der laufende Zähler verhindert Kollisionen zwischen sehr schnell aufeinander folgenden Inserts,
bei denen sich der Zeitanteil nicht ändert.

Jetzt habe ich noch ein Problem:
Auf einem Terminalserver haben alle Instanzen die gleich MAC-Adresse.

Irgendwelche Ideen oder Kommentare?

[Sherlock]

Keine Idee, nur ein Kommentar: Was sprach damals bei der Konzeption gegen eine Sequence? Selbstgenerierte Schlüssel führen (wie man hier eindrucksvoll sieht) doch immer zu Problemen.
Wir haben auch so einen mundgeklöppelten Schlüssel, den wir mitschleppen müssen (ist sogar der wichtigste in unserem System), der bringt nur Probleme.

Sherlock

[himitsu]

- User-Name/ID + Sitzungskennung ('nen Quersumme/Hash davon)
- Zeit
- Zähler (den könnte man doch bestimmt nur auf 16 Bit auslegen und hätte dann noch was für den Useranteil)

und jetzt kommt es noch darauf an, wie du zu Zusammengehörigkeit festlegst:
mehr nach Usern/Sitzungen > User-Sitzungskennung - Zeit+Zähler
mehr nach der Zeit > Zeit+Zähler - User-Sitzungskennung


Wenn die MAC nicht geht, dann mußt du halt etwas finden, welches sich unterscheidet :zwinker:

[Codewalker]

Warum müssen es denn überhaupt selbstgenerierte Schlüssel sein? An welchem Punkt reichen denn die Möglichkeiten, die das DBMS zur Verfügung stellt nicht mehr?

Edit: Sherlock war schneller :wink:

[shmia]

Zitat:

Zitat von Sherlock

Was sprach damals bei der Konzeption gegen eine Sequence? Selbstgenerierte Schlüssel führen (wie man hier eindrucksvoll sieht) doch immer zu Problemen.

Die Daten haben keinen natürlichen Schlüssel; also musste es ein künstlicher sein.
Die ursprüngliche Datenbank war MS Access.
Daher gab es keine Generatoren, Stored Procedures oder Ähnliches.
AutoInc-Felder konnten ebenfalls nicht benützt werden, weil der Primärschlüssel nach dem Einfügen in die Mastertabelle noch als Sekundärschlüssel in weiteren Detailtabellen benötigt wird.

Es blieben also nur drei Möglichkeiten:
1.) eine zentrale Instanz, die man nach einem neuen Schlüssel fragen kann (Ersatz für einen Generator)
2.) mehr oder weniger "zufällig" erzeugte Schlüssel, bei denen Kollisionen extrem unwahrscheinlich sind
und da waren wir bei GUIDs
3.) Eine Art Schlüsselreservierungssystem:
Das Programm schaut in eine bestimmte Tabelle und reserviert sich z.B. 256 aufeinanderfolgende Schlüssel
Nach dem diese Schlüssel verbraucht sind wird ein neuer Bereich reserviert.
Wird das Programm beendet gehen die unverbrauchten Schlüssel verloren.

Lösung 2 mit GUIDs war halt am Einfachsten zu implementieren...

[Bernhard Geyer]

Zitat:

Zitat von shmia

Durch die Verwendung einer GUID als Primärschlüssel werden die Datensätze, die nacheinander eingebucht wurden
über die gesamte Tabelle verstreut.

Definiere "verstreut". Ein relationales DBMS ist ein mengenorientiertes System bei dem du die "Verstreuung" nicht beeinflussen kannst. Aus ein Integer als Primärschlüssel kann eine verstreute Speicherung in der DB verursachen.

[shmia]

Zitat:

Zitat von Bernhard Geyer

Definiere "verstreut". Ein relationales DBMS ist ein mengenorientiertes System bei dem du die "Verstreuung" nicht beeinflussen kannst. Aus ein Integer als Primärschlüssel kann eine verstreute Speicherung in der DB verursachen.

MS SQL Server kennt einen speziellen Clustered Index (Gruppierter Index).
Pro Tabelle kann es nur einen clustered Index geben.
Der Primärschlüssel wird häufig als clustered Index gewählt; (das ist aber kein Muss).
Der clustered Index ist wertvoller als nonclustered Indizies, weil der Zugriff über den clustered Index schneller ist.

Die Datensätze in der Tabelle sind physikalisch in der Reihenfolge angeordnet in dem der Clustered Index sortiert ist.
Daher werden Datensätze mit einer GUID als Primärschlüssel nicht am Ende der Tabelle eingefügt, sondern der SQL-Server
muss ständig bestehende Seiten splitten (was natürlich auch die Performance bremst) und verstreut so hintereinander folgende Einfügungen über die gesamte Tabelle.

[generic]

Zitat:

gesamte Tabelle verstreut

Die Daten werden immer verstreut bzw. dort in die Tabelle geschrieben wo Platz ist.
Einige Datenbank liefern dir nur die Datenmenge immer nach dem Primär Schlüssel sortiert zurück.

Daher ist das eine Anforderung von dir, die Daten nach Datum sortiert zurück zu geben.
Das hat dann aber nichts mit der GUID zu tun, sondern vom dem Select und einen Datumsfeld nach welchen du sortierst.

[pertzschc]

Zitat:

Zitat von shmia

In meiner Datenbank gibt es mehrere Tabellen, die eine GUID als Primärschlüssel verwenden...
Später werden dann die Verbuchungen eines Tages in einem Bericht aufbereitet.
Die Performance nimmt mit steigender Grösse der DB rapide ab, weil die Datensätze, die zusammen verarbeitet werden sollen,
nicht beieinander liegen.

Ich habe schon einige Datenbanktabellen gesehen, die nach folgendem Muster aufgebaut waren:
Feld1 - Feld2 - Feld3 - Feld4 - Feld...
GUID Datum Zeit (Zeitstempel)

Dabei ist die GUID der PK und auf den Feldern 2&3 liegt ein weiterer Index, so das ein:

SQL-Code:

SELECT * FROM TABLE X
 WHERE DATE = '05.11.2009'
 ORDER BY DATE TIME.

ohne Probleme funktioniert. Wenn das irgendwann performancemäßig einbricht kann man Datenbanktechnisch sicher noch einiges tunen.

Was spricht in Deinem Fall dagegen?
Viele Grüße,
Christoph

[p80286]

Hallo shmia,

mal so ins unreine gedacht, was spricht dagegen die bisherigen "Doityourself"-Schlüssel gegen "richtige" Schlüssel zu tauschen? Beim erneuten Aufbau der DB arbeitest Du mit den neuen und alten Schlüsseln, etwa so:

SQL-Code:

update table1 set table2id (select table2.id where tabl2.altid=table1.altid)

Ich weiß jetzt allerdings nicht ob MS_SQL so etwas zulässt.

Und gibt es unter MS_SQL nicht auch die Möglichkeit mehrere Felder in einem Index zusammen zu fassen?

Hab mich allerdings schon einige Jahre nicht mehr mit MS_SQL befassen müssen, darum bin ich mir da nicht sicher.

Gruß
K-H

[shmia]

Zitat:

Zitat von generic

Die Daten werden immer verstreut bzw. dort in die Tabelle geschrieben wo Platz ist.
Einige Datenbank liefern dir nur die Datenmenge immer nach dem Primär Schlüssel sortiert zurück.

Ohne clustered Index schaut eine Datenbank zuerst, ob irgendwo "Löcher" (=gelöschte Datensätze) in der Tabelle sind
und versucht diese zu füllen.
Andernfalls wird am Ende der Tabelle (bzw. der Datenbank) physikalisch angefügt.
Das ist mit anderen Worten das was du geschrieben hast.

Mit clustered Index ist das Verhalten so:
Der clustered Index wird benützt, um die Einfügestelle zu finden.
Ist auf der Seite (8kb) noch Platz für den Datensatz, wird er dort reingeschrieben.
Andernfalls wird die Seite gesplittet.

Um mein Problem zu verstehen muss man verstehen, wie ein clustered Index funktioniert.
Hier wird erklärt, weshalb GUIDs als Primärschlüssel und clustered Index eine schlechte Performance haben:
http://www.insidesql.org/quick-tips/clustered-indexes

Man könnte nun den clustered Index auf ein anderes Feld legen, aber das geht aus folgenden Gründen nicht so einfach:
1.) es ist nicht vorhersehbar, wie lange das Umstellen des clustered Index dauert (kann viele Stunden brauchen)
Die Datenbank ist "mission critical" und wenn die DB auf unbestimmte Zeit down ist, dann gibt das Ärger
2.) viele Kunden setzen die kostenlose Express Edition *) ein. Die Grösse einer DB ist auf 4 GB limitiert.
Löschen und Neuanlegen des clustered Index braucht sehr viel Platz.
Sollte das 4GB Limit erreicht werden, dann ist die Datenbank "kaputt" und kann nur noch mit einem kostenpflichtigen SQL Server Standard Edition gerettet werden.
*) Sie sparen sich 2000-3000 Euro für die Std Edition; das ist Sparen am falschen Platz

[p80286]

Mein Beileid
"wir sparen, koste es was es wolle"

Gruß
K-H

[Blup]

Das Hauptproblem lässt sich auch nicht mit einem "Clustered Index" lösen.
Die 80 Felder sagen eindeutig schlechte Datenbankstruktur.
Wie viele Datensätze passen denn da noch in einen Cluster?

Prüft erst mal, welcher Index bei der Verarbeitung wirklich genutzt wird. Mit optimalem Index macht es normalerweise keinen wesentlichen Unterschied ob 10 oder 10millionen Datensätze in der Tabelle vorhanden sind. Müssen 10 Datensätze verarbeitet werden, sind das im schlimmsten Fall 10, im optimalen Fall 1 Cluster.

Wenn der Bericht jeweils nur die neuen Daten enthalten soll, könnte mit einer zusätzlichen Tabelle gearbeitet werden, die nur die ID der neuen Datensätze enthält und nach Erstellung des Berichts wieder gelöscht wird.

Führt das nicht zu Ziel, muss für diese Daten eine neue Struktur gefunden werden. In der Haupttabelle sollten neben der ID nur Felder bleiben, die für Referenzen auf andere Tabellen benötigt werden (KundeID oder ähnliches) und Felder die indiziert werden (z.B. Datum für Index KundeID + Datum). Alles andere kann z.B. in einer zugehörigen Wertetabelle über ID, Typ, Wert (PK über ID und Typ) abgelegt werden.

Muss die Datenbank fast durchgehend verfügbar sein, kann so eine Umstellung auch schrittweise im laufenden Betrieb erfolgen. Dazu müssen natürlich alle Zugriffe zuerst auf eine "Procedure" oder "View" umgestellt werden, die beide Datenstrukturen auswertet. Neue Daten werden in die neue Struktur geschrieben.
Ein Hintergrundprozess kann dann je nach Auslastung innerhalb einer "Transaction" einige Datensätze aus der alten Struktur in die neue überführen und aus der alten Tabelle entfernt. Cluster die von der alten Tabelle nicht mehr belegt sind, werden dabei automatisch für die neue Struktur verwendet. Die Größe der Datenbank würde sich dadurch nur unwesentlich ändern.