Hallo Chris,
hierbei kann man von zwei Seiten an die Aufgabe herangehen.
Auf Seiten der Speicherung gelten die gleichen Bedingungen
wie man sie auch bei uniqueidentifier (Guid) vorfindet.
Wozu man im Web reichlich Diskussionsbeiträge findet, stellvertretend einer davon:
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx
Dein Schlüssel ist mit 8 Bytes zwar nur halb so lang, verteilt sich aber ebenso
auf die gesamte Tabelle und die Defragmentierung wird dadurch extrem hoch.
Je nachdem wofür der Coupon-Code verwendet wird, könnte man hier ggf.
dadurch Entlastung schaffen, dass man periodisch einen Vorrat von Codes erzeugt
und diese in einer separaten (relativ kleinen) Tabelle hält, und alle genutzten
in einer großen (partitionierten) Archivtabelle.
Denn der von Dir gesehene Einbruch findet zum Teil deswegen statt, wenn sich die
Tabelle nicht mehr im Speicher halten läßt (und später will man kaum den
Speicherbedarf an einem Code ausrichten).
Die zweite Seite wäre das Erzeugen der Codes als solches. Ich bin nun kein
Mathematiker/Statistiker (und habe auch keinen als Nachbarn ;-), so dass ich
keine passende Formel aus dem Hut zaubern kann. Bei Google findet man zwar
viel Zeugs (aber auch viel unausgegorenes ;-) Ein Hinweis, der zu lesen war,
bezieht sich auf das http://en.wikipedia.org/wiki/Birthday_problem
Mit der jetztigen Formel erzeugst Du einen 8 Byte (64 Bit) großen Code,
von dem effektiv (32er @base) nur 5 Byte (40 Bit) genutzt werden.
BTW: Du mußt bei Modulo einen draufzählen, damit Du korrekte Werte erhälst,
denn SUBSTRING ist in SQL 1-basiert.
Anstatt nur viele Guid-Checksummen zu verwürfeln, könnte man eine
zeitliche Komponente einfügen. Wodurch man eine teilweise Sortierung erhält.
Die kann man wiederum dazu nutzen, die Fragmentierung kleiner zu halten
oder auch für Partitionierung-Funktionen nutzen. Zum anderen kann u. U.
die zeitliche Komponente genutzt werden, um Codes als obsolet zu erklären
(je nachdem wie sie verwendet werden).
Im unten stehenden Beispiel hatte ich (gestern abend) eine Variante für die ersten Stellen probiert.
Dabei kommt es zu mehr Kollisionen, die allerdings, soweit ich in der Zeit testen konnte,
weniger exponentiell ansteigen.
Zudem hatte ich dort mit einem Integer als Primärschlüssel experimentiert, um die
Fragmentierung kleiner zu halten - wodurch der Speicherbedarf (zwei Schlüssel) mitwächst.
SET NOCOUNT ON;
GO
DROP TABLE dbo.COUPONCODE, dbo.COUPONLOG;
GO
CREATE TABLE dbo.COUPONCODE
(
COUPONID bigint IDENTITY(1, 1) NOT NULL,
COUPONCODE char(8) NOT NULL,
-- Andere Sortierung (mag anders bunter aussehen ;-)
-- besser als Teil einer Sicht/Abfrage
CUPONCODE_SCRAMBLE AS (
SUBSTRING(COUPONCODE, 8, 1) + SUBSTRING(COUPONCODE, 1, 1)
+ SUBSTRING(COUPONCODE, 6, 1) + SUBSTRING(COUPONCODE, 3, 1)
+ SUBSTRING(COUPONCODE, 4, 1) + SUBSTRING(COUPONCODE, 5, 1)
+ SUBSTRING(COUPONCODE, 2, 1) + SUBSTRING(COUPONCODE, 7, 1)),
CONSTRAINT PK_COUPONCODE PRIMARY KEY /* NONCLUSTERED */ (COUPONID),
CONSTRAINT UK_COUPONCODE UNIQUE (COUPONCODE) -- WITH (FILLFACTOR = 75)
);
GO
CREATE TABLE dbo.COUPONLOG
(
id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Zeit datetime2 NOT NULL,
ZeitMs int NOT NULL,
Gesamt bigint NOT NULL,
Anzahl int NOT NULL,
Fehler int NOT NULL);
GO
SET NOCOUNT ON;
DECLARE @base char(32) = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
DECLARE @baseTime datetime2 = CAST('20100101' AS datetime2);
DECLARE @baseValue int;
DECLARE @baseId int;
DECLARE @i int = 0;
DECLARE @inserts int = 0, @failures int = 0;
DECLARE @starttime datetime2 = SYSDATETIME();
BEGIN TRAN
WHILE @i < 10000000
BEGIN
BEGIN TRY
-- Für 10 Minuten ab 1.1.2010 reicht es etwa 10 Jahre
-- SET @baseValue = DATEDIFF(mi, @baseTime, SYSDATETIME()) / 6;
-- Im Test je 10 Sekunden da kurzer Zeitraum - nicht direkt vergleichbar!
SET @baseValue = DATEDIFF(ss, @baseTime, SYSUTCDATETIME()) / 6;
SET @baseId = ABS(CHECKSUM(NEWID()));
INSERT INTO dbo.COUPONCODE WITH (TABLOCKX, HOLDLOCK) (COUPONCODE)
VALUES ( -- alternativer Divisor möglich (je nach Zeitraum)
+ SUBSTRING(@base, ((@baseValue / 32 / 32 / 32) % 32) + 1, 1)
+ SUBSTRING(@base, ((@baseValue / 32 / 32) % 32) + 1, 1)
+ SUBSTRING(@base, ((@baseValue / 32) % 32) + 1, 1)
+ SUBSTRING(@base, ((@baseValue) % 32) + 1, 1)
+ SUBSTRING(@base, ((@baseId / 32 / 32 / 32) % 32) + 1, 1)
+ SUBSTRING(@base, ((@baseId / 32 / 32) % 32) + 1, 1)
+ SUBSTRING(@base, ((@baseId / 32) % 32) + 1, 1)
+ SUBSTRING(@base, ((@baseId) % 32) + 1, 1));
SET @i += 1;
SET @inserts += 1;
IF (@i % 50000) = 0
BEGIN
-- PRINT CAST(@i AS nvarchar(10)) + N' Inserts, ' + CAST(@failures AS nvarchar(10)) + N' Failures';
INSERT INTO dbo.COUPONLOG(Zeit, ZeitMs, Gesamt, Anzahl, Fehler)
VALUES(
SYSDATETIME(),
DATEDIFF(ms, @starttime, SYSDATETIME()),
(SELECT COUNT_BIG(*) FROM dbo.COUPONCODE),
@inserts, @failures);
SET @inserts = 0;
SET @failures = 0;
COMMIT;
BEGIN TRAN;
END;
END TRY
BEGIN CATCH
SET @failures += 1;
END CATCH;
END;
COMMIT;
GO
SELECT *
, CAST(ZeitMs AS float) / CAST(NULLIF(Gesamt,0) AS float) AS GesamtMs
, CAST(ZeitMs AS float) / CAST(NULLIF(Anzahl,0) AS float) AS AnzahlMs
, CAST(Fehler * 100.0 AS float) / CAST(NULLIF(Anzahl,0) AS float) AS FehlerPrz
FROM COUPONLOG ORDER BY id desc;
GO
DBCC SHOWCONTIG ('dbo.COUPONCODE') WITH TABLERESULTS, ALL_INDEXES;
GO
--ALTER INDEX ALL ON dbo.COUPONCODE REBUILD; --WITH (FILLFACTOR=100);
GO
--SELECT TOP(10000) * FROM dbo.COUPONCODE WITH (NOLOCK) ORDER BY COUPONCODE DESC;
GO
Im Nachhinein betrachtet (nicht mehr probiert), wäre eine Alternative, einen Big Integer
oder zwei Integer zu nutzen, wo man die Stellen zur Laufzeit - wie beim COUPONCODE_SCRAMBLE
in einen Code umwandelt. Mit Bit-Schuppserei sollte das über einen Identitätswert +
Zeit-Komponente in einen (pseudozufälligen) Code zu transponieren sein.
Wie man sieht habe ich auch mit dem FillFactor rumgespielt. Nur auf einer sehr großen Tabelle
ist der eher kontraproduktiv, so dass eine Teilung in einen aktiven und Archivteil dabei ratsamer ist.
Gruß Elmar
