Laden...

GUID vs AutoIncrement

Erstellt von Xynratron vor 17 Jahren Letzter Beitrag vor 17 Jahren 4.697 Views
X
Xynratron Themenstarter:in
1.177 Beiträge seit 2006
vor 17 Jahren
GUID vs AutoIncrement

Hallo zusammen,

mir fällt immer wieder das Arugument auf, GUIDs als PK wären Performant schlechter als Autoincrement. Dem muss ich entgegenhalten, dass MS SQL-Server bereits in Version 7.0 zur Replikation GUIDs verwendete.

Um das mal zu testen, habe ich ein kleines Proggie geschrieben (Anhang) welches einfach Daten in eine DB schiesst (INSERT) und dass auf 4 Arten. Das schreiben passiert per Random, in ausreichender Anzahl, um die Daten möglichst Plausibel zu machen. Die Tabelle selbst besteht aus einem PK (UUID bzw. Int) und einem varchar(50)-Feld, welches immer voll befüllt wurde. Weitere Daten sollten nicht notwendig sein, da die Kosten des Einfügens nur von der benutzten Technik und dem verwendeten Treiber abhängen sollten.

Kurzerklärung:

Zu Anfang werden ein paar SQL-Statements gesetzt; das CMD-Object ist für alle Tests das gleiche.
Dann wird die Tabelle erstellt, und Daten eingefügt. Anschliessend die Tabelle gelöscht.
Es wird nur der Zeitraum vom ersten einfügen bis zum letzten einfügen betrachtet.
Das ganze wird mehrmals durchgeführt, um ein anständiges Mittel zu bekommen. Da 4 unterschiedliche Einfügemechanismen betrachtet werden, werden diese auch per Random aufgerufen.

Codeschnipsel:

            //snip
        //exemplarische Einfüge-Funktion - die 3 anderen sind nahezu identisch
        static int GUID_NewId(SqlCommand cmd)
        {
            string CreateTable = "CREATE TABLE tblTest (pk uniqueidentifier NOT NULL Primary Key, daten varchar(50))";
            string InsertData = "INSERT INTO tblTest (pk, daten) VALUES (NEWID(), @paramDaten)";
            string DropTable = "DROP TABLE tblTest";
            
            cmd.CommandText = CreateTable;
            cmd.ExecuteNonQuery();

            cmd.CommandText = InsertData;
            cmd.Prepare();
            cmd.Parameters.Add("paramDaten", System.Data.SqlDbType.VarChar);

            int time = Environment.TickCount;

            //hier gehts los....
            for (int i = 0; i < maxInserts; i++)
            {
                cmd.Parameters[0].Value = VarChar50();
                cmd.ExecuteNonQuery();
            }

            time = Environment.TickCount - time;
            
            cmd.Parameters.Clear();
            cmd.CommandText = DropTable;
            cmd.ExecuteNonQuery();
            Thread.Sleep(100);
            return time;
        }
//snap

//snip
            //schleife welche die 4 Funktionen aufruft
            int runtime_GUID_NewId = 0;
            [..]
            int runs_GUID_NewId = 0;
            [..]
            while (true)
            {
                int i = _random.Next(4);
                switch (i)
                {
                    case 0:
                        {
                            if (runs_GUID_NewId >= maxruns)
                                break;
                            runs_GUID_NewId++;
                            runtime_GUID_NewId += GUID_NewId(cmd);
                            break;
                        }
                    case 1:
//sappel

Testumgebung:

VS2005 + SQL2005; P4 3 GHz, 2G Ram, SATA-Stripe-Set 10k U/Min

Ergebnis:

es wurde jede Testroutine 500 mal aufgerufen und diese fügte jedesmal 5000 Datensätze in die Tabelle ein. Die 4 betrachteten Möglichkeiten waren:
*PK als GUID mit TSQL NewID() *PK als GUID mit .NET System.Guid.NewGuid() (vorbelegt) *PK als Int mit TSQL IDENTITY (1, 1) *PK als Int mit .NET int (vorbelegt)

Runtimes for Inserting Data
DataRows inserted 5000; for 500 times

GUID with NewId() : 1685
GUID as Param : 1707
Int with Identity : 1832
Int as Param : 1715

Fazit:

Beim einfügen über den Native-SQL-Treiber ist GUID beides mal schneller, ob per TSQL NewID() oder per .NET. Eindeutig fehlt noch die Überprüfung auf SELECT - ist aber schon in Planung - da die größe eines Indizes auf die Performance erheblichen Einfluss haben kann. Zu bemerken sein noch, dass eine UUID 128Bit hat, ein normaler INT nur 32Bit.

Vor- und Nachteile

Nachteile
*Gewöhnungsbedürftig *Beim Debuggen kann man sich eine UUID nicht merken (nen Int schon) *Mehr Speicherplatz (grösserer Indize -> less Performance) *Datenbanken werden undurchsichtliger

Vorteile
*keine Vorhersagbarkeit - vgl. T-Systems-Hack (2004?) - ZeigeVertrag.php?id=17 *Datenbanken können gemischt werden, da PK's, unwahrscheinlich gering, nicht doppelt sind *ohh, mehr fällt mir grad nicht ein^^

PS

Einen Test mit deutlich mehr INSERTS, sowie auch SELECT und DELETE steht noch aus. Daran werde ich mich machen, sobal ich wieder etwas Zeit habe, ich lass das aber nicht schleifen, da ich das wissen will^^
Vorurteile sind da, um sie zu wiederlegen, denk ich mir grad, hätte eigentlich ne Performacne um ca. 100-150% schlechter erwartet.

Wenn irgend jemand einen Ideologischen, Syntaktischen, Theoretischen, Praktischen, Dummen, Theologischen oder sonstwie gearteten Fehler entdeckt, bitte sagen, bevor ich noch mehr Zeit verplämper^^ (Achja, wie immer gelten Recht-scribt-feeler (die linken auf!) nicht...)

Erklärungen zur Verständlichkeit:

GUID= Global Unique Identifier = UUID (Universal Unique Identifier) = 128 Bit Key; ein Zufallswert (auf Netzwerkkarte, Datum/Uhrzeit und sonstigem) der theoretisch Weltweit (universal) eindeutig ist.

🙂

cu Xynratron

PS: war dem schon lange auf der Spur, hab aber erst durch dieses Board heute die Lust bekommen, mal selbst nachzuforschen, nicht immer dem "schlechte Performance" zu glauben. Wenn irgendwer einen Fehler oder ne Anregung hat, ich bitte darum, denn ich sterbe ungern dumm 🙂
PPS: Im Webbereich nutze ich nur noch UUID, eher haben immer alle andere Faktoren ein Performance-Problem^^

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

T
512 Beiträge seit 2006
vor 17 Jahren

Naja wir werden mit Sicherheit nicht auf GUIDs wechseln, auch wenn die 100 mal schneller wären. Mir graut es vor Anrufen wie: "Der Auftrag 00EF68B8-EE7D-4EA0-83C5-05604ECD75AB wurde nicht richtig zurückgemeldet" 😁

Außerdem traue ich dem Zufall nicht so recht. Eine Wahrscheinlichkeit von 1 / 2^128 einen doppelten Identifier zu erhalten bedeutet für mich nur, es ist möglich 😉
Die verrücktesten Dinge passieren und davon nicht wenige in der EDV. Da werden Datenbanken zur Unkentlichkeit verstümmelt und niemand hat was gemacht...

e.f.q.

Aus Falschem folgt Beliebiges

X
Xynratron Themenstarter:in
1.177 Beiträge seit 2006
vor 17 Jahren

najo, bedingt (imho^^) hast du ja recht,
wobei ein PK != Auftragsnummer (oder sonstwas sein sollte) und bei int32 die möglichkeit der Doppelung schon höher ist als bei UUID.

Aber: wäre mal interessant, was TSQL NewId() bei schon vorhandenem Key macht ... hmm die Datenbank sollte ein Problem darstellen, zu gross, mal probieren .. vermutlich muss man aber ein Try-Catch zu rate ziehen...

🙂

cu Xynratron

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

D
103 Beiträge seit 2006
vor 17 Jahren

Schon mal jemand versucht, zwei Tabellen die über einen hochzählenden Primärschlüssel verbunden sind, in ADO.net richtig anzusprechen? 🙄 Man fügt in der Haupttabelle einen Datensatz ein, bekommt vom DataSet einen Primärschlüsselwert, der überhaupt nichts mit dem Primärschlüsselwert zu tun hat, den dir die Datenbank beim Update geben wird und darüber verbindest du dann deine Untertabelle. Grausam. Ein GUID hingegen kann (glaube ich - da das Ändern der DB keine Option war, hab ichs nicht ausprobiert. 🙂 ) einfach so übernommen werden, weil im Gegensatz zu einer Hochzählzahl nicht die Gefahr besteht, dass er schon existiert.

"ADO.NET" von Ralph Westphal (Addison-Wesley) hat "mit automatischen Primärschlüsseln leben" ein ganzes Kapitel (von insges. vier Kapiteln) gewidmet.

Warum soll die Auftragsnummer kein Primärschlüssel sein? Sinnfreie "einfach so"-Primärschlüssel sind zum Kotzen...

3.511 Beiträge seit 2005
vor 17 Jahren

Also, wie arbeiten Teilweise mit GUIDs, teilweise mit Ints (Nicht fragen, ist so 🙂). Der Supportaufwand in unserer Firma ist gut um 30% gestiegen, seit wir uns mit den GUIDs rumschlagen. Denn ein Supportmitarbeiter kann mich mal eben anrufen und sagen: "Kunde hat angerufen und gesagt Datensatz 3242 in Tabelle beschädigt". Blöd nur wenn er Anruft und sagt: "Kunde hat angerufen und irgendwas von wilden Zeichen gefaselt.".
Ab und zu müssen wir uns Datenbanken von Kunden ziehen und diese zu checken. Als wir nur mit Ints gearbeitet haben, waren die so im Schnitt 300-400MB groß. Jetzt sind die 1-1,5GB groß!. Die zu ziehen dauert halt nicht mehr 1h, sondern auch mal 4h (gezippt).

Naja, es hat natürlich auch Vorteile das man GUIDs einsetzt. Meiner Meinung nach, sollte man diese nur Einsetzen, wenn man sich ganz sicher ist, das die Daten möglicherweise mit anderen Datenbanken migriert werden könnten. Falls es immer nur Daten sind, die nie migriert oder sonstwie behandelt werden, sollte man Auto-Incs verwenden.

Es ist z.B. sinnvoll Adressstämme mit GUIDs zu versehen, da diese eventuell mit anderen Datenbanken zusammengefügt werden könnten. Termindaten z.B. wüsste ich jetzt keinen sinnvollen Grund diese zu migrieren, also Auto-Ints.

Mein Fazit: GUIDs haben Vorteile, Integers haben Vorteile. Man muss halt abwiegen, an welchen Stellen man was verwendet. Wobei ich immer Fan von Integern bleiben werde, weil das den Entwicklungsaufwand doch erheblich reduziert gegenüber GUIDs 🙂

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

1.985 Beiträge seit 2004
vor 17 Jahren

Hallo dutop,

Original von dutop
Schon mal jemand versucht, zwei Tabellen die über einen hochzählenden Primärschlüssel verbunden sind, in ADO.net richtig anzusprechen? 🙄 Man fügt in der Haupttabelle einen Datensatz ein, bekommt vom DataSet einen Primärschlüsselwert, der überhaupt nichts mit dem Primärschlüsselwert zu tun hat, den dir die Datenbank beim Update geben wird und darüber verbindest du dann deine Untertabelle. Grausam.

dieses Argument habe ich jetzt schon öfters gehört und mir deshalb auch das, was Ralph Westphal dazu gesagt hat, durchgelesen. Wirklich nachvollziehen kann ich es nicht. Ich kann zwar die Probleme nachvollziehen, die dadurch entstehen können, aber selber auf das Problem gestoßen bin ich noch nicht.

Richtig ist, dass beim Anlegen eines Datensatzes die DataTables einen fiktiven AutoIncrement PK vergeben, um den Datensatz temporär in der DataTable anlegen zu können. Mit diesem fiktiven PK werden dann die Datensätze in den Child-Tables verknüpft. Ich habe allerdings kein Problem, diese Datensätze dann in der Datenbank zu persistieren.

Unter ADO.NET 2.0 ist mir das von Dir beschriebene Problem noch nicht aufgefallen. Hast Du evtl. weitere Informationen dazu, da ich 1. gerne dazulernen möchte und 2. gerne Probleme aus dem Weg gehen würde, die vielleicht im späteren Verlauf noch auftreten und mir bis jetzt nur noch nicht aufgefallen sind.

Gruß,
Fabian

"Eine wirklich gute Idee erkennt man daran, dass ihre Verwirklichung von vornherein ausgeschlossen erscheint." (Albert Einstein)

Gefangen im magischen Viereck zwischen studieren, schreiben, lehren und Ideen umsetzen…

Blog: www.fabiandeitelhoff.de

D
103 Beiträge seit 2006
vor 17 Jahren

Ist schon länger her, ich weiß nur, dass es ein Problem gab. Hab zwar die Notizen rausgekramt, aber die Zusammenhänge wieder zu finden, dürfte schwer werden.

Schreibe ich zuerst die Kindtabelle vom DataSet in die DB, beschwert sich das Dataset beim ersten Datensatz, dass der gerade angegebene Fremdschlüssel auf einen Primärschlüssel zeigt, dens in der Datenbank nicht gibt. Schreibe ich zuerst die Haupttabelle, ändert sich der Primärschlüsselwert und er findet dann bei der Kindtabelle wieder den Primärschlüssel nicht. Schreibe ich die Haupttabelle in die DB und will dann im DataSet die von der Datenbank erzeugten Primärschlüsselwerte übernehmen, kann es sein, dass es den Schlüsselwert schon im DataSet gibt. Schreibe ich den neuen Primärschlüsselwert erst mal in eine anderen Spalte verschiebe ich das Problem nur auf später - irgendwann muss der Schlüsselwert in den Schlüssel. Letztendlich musste ich die Verbindung zwischen den Tabellen komplett lösen, den Schlüsselwert der War-Mal-Haupttabelle aktualisieren, in der War-Mal-Kindttabelle in eine neue Spalte schreiben und später die Spaltenwerte in einem Zug auf die Spalte verschieben, die mal die Fremdschlüsselspalte war. (dieser Zwischenschritt ist nötig, damit ich nicht plötzlich Kindttabellendatensätze zusammenklebe (gleicher Primärschlüsselwert), die nicht zusammen gehören)

Das waren jetzt Lösungsansätze für ein Problem, das auftritt, aber von mir nicht beschrieben werden kann. 🙂 Ich hoffe, du kannst es selber rausfinden, sonst melde dich doch mal. Jedenfalls hasse ich seit diesem Tag... dieser Woche (wenn nicht mehr) selbsthochzählende Primärschlüssel. Ich hätte sie übrigens nicht gegen einen GUID-Primärschlüssel getauscht (ups... ich fliege gerade aus dem Thread-Thema 😉 ), sondern gegen einen eindeutigen, zusammengesetzten Primärschlüssel ersetzt.

347 Beiträge seit 2006
vor 17 Jahren

Öhm, deine Testtabellen sind nicht gleich definiert.
Wenn du einmal einen PK hast, ein anderes Mal nicht dürfte es nicht verwundern wenn die Tabele ohne PK schneller ist oder? 🙄
Prepare auf ein Command auszuführen, bei dem du die Parameter noch nicht deklariert hast ist auch sehr ... 🤔 kreativ. 🙄

Bei meinen Test ist "Int as Param" der schnellste. (War aber in einer VM auf einem anderen Rechner, das Biest kommt mir nicht auf meine Kiste 😉 )
Und es wundert mich auch nicht warum NewGuid schneller als ein AutoInc ist.
a) AutoInc war im SQL Server noch nie sonderlich flink
b) NewID muss nur einen Wert rausrotzen, AutoInc muss ihn nachschlagen

Was du in deinen Tests aber komplett unterschlagen hast, ist die Tatsache, dass Inserts so ziemlich das uninteressanteste sind wenn man Geschwindigkeiten messen will.
32 Bit zu vergleichen sollte (zumindest nach mienen naiven Vorstelungen) schneller passieren können als 128 Bit zu vergleichen. 32 Bits abzufragen sollte von File I/O und Netzwerk I/O ebenfalls schneller gehen als 128 Bit.
Verknüpfungen über GUIDs sollten also entweder ziemlich lahm sein, oder das DBMS ist zu blöd um richtig mit 32 Bit Ints zu arbeiten. Könnte natürlich auch eine politische Entscheidung sein, da Replikation GUIDs benötigt.

X
Xynratron Themenstarter:in
1.177 Beiträge seit 2006
vor 17 Jahren

Original von Robert G
Öhm, deine Testtabellen sind nicht gleich definiert.
Wenn du einmal einen PK hast, ein anderes Mal nicht dürfte es nicht verwundern wenn die Tabele ohne PK schneller ist oder? 🙄
Prepare auf ein Command auszuführen, bei dem du die Parameter noch nicht deklariert hast ist auch sehr ... 🤔 kreativ. 🙄

eieiei, ist wohl doch zu spät geworden gestern^^ Werde dies selbstverständlich nochmals korrigieren und mein OP verbessern... bin aber gerne 🤔 kreativ. 🙄 fg

*edit: hab mal schnell die pk dazugefügt, prepare weggenommen - kein Unterschied. Desweiteren hatte ja die Table mit GUID nen PK - die mit Int nicht. Insofern stimmt deine Aussage nicht

Was du in deinen Tests aber komplett unterschlagen hast, ist die Tatsache, dass Inserts so ziemlich das uninteressanteste sind wenn man Geschwindigkeiten messen will.
32 Bit zu vergleichen sollte (zumindest nach mienen naiven Vorstelungen) schneller passieren können als 128 Bit zu vergleichen.

kommt noch, kommt noch^^ hab ich ja geschrieben - ist hier erstmal als Anfang gedacht. Man kann ja auch nicht alles auf einmal machen. Genauso fehlt ja auch ein Test mit vielen Daten, so 10Mio Datensätze sollten es schon sein...

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

T
512 Beiträge seit 2006
vor 17 Jahren

Wenn in Datenbankdesign auch nur halb so viel Hirnschmalz gesteckt würde, wie in diese Diskussion, hätte ich nur halb so viel Arbeit.

Wir arbeiten viel mit bestehenden Datenbanken und das ist teilweise einfach nur grausam. Primärschlüssel? Nie gehört. Von normalisierten Tabellen mal ganz zu schweigen. Und dort haben dann auch Felder mit gleicher Bedeutung unterschiedliche Datentypen. NULL kennt man auch nicht, deswegen muss meist ein char Feld herhalten, wo dann "--" eingetragen wird.

e.f.q.

Aus Falschem folgt Beliebiges

89 Beiträge seit 2006
vor 17 Jahren

Wobei NULL auch vom Teufel stammt... Ich verlange korrekterweise eine weitere Tabelle1111!!!!einself

Q
992 Beiträge seit 2005
vor 17 Jahren

Original von purestrain
Wobei NULL auch vom Teufel stammt... Ich verlange korrekterweise eine weitere Tabelle1111!!!!einself

Hat uns unserer Berufsschullehrer letzte Woche auch erzählt. Aber man kann ihm da ganz gut mit "bewusster Denormalisierung" zwecks einfacher Statements und Performance kommen.

Ansonsten: Ich benutze bei jeder Tabelle einen numerischen Primärschlüssel der durch die Sequences in Oracle gefüllt wird. GUID nur bei Replikation.

925 Beiträge seit 2004
vor 17 Jahren

Original von Traumzauberbaum
Naja wir werden mit Sicherheit nicht auf GUIDs wechseln, auch wenn die 100 mal schneller wären. Mir graut es vor Anrufen wie: "Der Auftrag 00EF68B8-EE7D-4EA0-83C5-05604ECD75AB wurde nicht richtig zurückgemeldet" 😁

Außerdem traue ich dem Zufall nicht so recht. Eine Wahrscheinlichkeit von 1 / 2^128 einen doppelten Identifier zu erhalten bedeutet für mich nur, es ist möglich 😉
Die verrücktesten Dinge passieren und davon nicht wenige in der EDV. Da werden Datenbanken zur Unkentlichkeit verstümmelt und niemand hat was gemacht...

Bei einer Chance von 1 : 2128 ist die Wahrscheinlichkeit eines doppelten Eintrags aber wesentlich geringer, als bei einem UINT32, welcher nach 4.5Mrd Datensätzen definitiv und unweigerlich überläuft. Ein doppelter 128Bit GUID tritt allerspätestens nach 2 ^ 128 Datensätzen auf, was in etwa (18 * (1012)) * (18 * (10^12)) entspricht. Also 18 Billionen mal 18 Billionen. Kurz gesagt: sehr unwahrscheinlich gegenüber einem überlaufenden UINT32!

S
8.746 Beiträge seit 2005
vor 17 Jahren

Naja, Apfel und Birnen-Vergleich. Aber selbst wenn eine GUID doppelt auftauchen sollte, führt das nur zu einer Exception beim Einfügen. Und die kann man dann spielend dadurch behandeln, dass man den Aufruf einfach mit einer neuen GUID wiederholt.

Die Gründe, die gegen eine GUID sprechen sind ja bereits genannt worden. Performance ist bei Selects deutlich schlechter (wir reden hier von Größenordnungen (z.B. log n gegen), sofern ein Index zum Einsatz kommen kann) und die schlechte Lesbarkeit macht in der Entwicklungsphase keinen Spass.

Wenn die DB klein ist oder Performance keine Rolle spielt, dann ist GUID leichter, sonst sollte eine GUID höchstens zusätzlich eingesetzt werden.

Hier ein excellenter Artikel mit Untersuchungen und einem alternativen Algorithmus zur Erzeugung von GUIDS (sequential), der die Performance-Nachteile größtenteils wett macht.

http://www.informit.com/articles/article.asp?p=25862&seqNum=1&rl=1

http://www.sqlmag.com/articles/index.cfm?articleid=46821&

Der goldene Mittelweg besteht in der Verwendung der SQL Server 2005-Funktion Newsequentialid() (im Prinzip genau so eine sequential GUID). Leider macht das die Arbeit unter .NET nicht leichter (dann kann man auch gleich AutoIncrement nehmen).