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

[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

[shmia]

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 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 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 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:
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