Möglichkeiten zum schnellen Generieren von Testdaten für SQL Datenbank?

[Piktogramm]

Servus,

ich brauche größere Mengen Testdaten um das Verhalten eines Datenbanksystems (MS SQL) zu testen. Das Problem dabei ist jedoch, dass ich größere Mengen Testdaten brauche. So ab 60GB wären schön. Die Kür wäre, zwei getrennte Testdatensätze zu erstellen, bei denen die Kollisionshäufigkeit der Daten je Spalte einstellbar sind.

Das Problem dabei ist jedoch, dass die Mittel und Wege die ich ausprobiert habe allesamt langsam sind. Ich könnte den Spaß jetzt fröhlich weiter optimieren (und vor allem in C schreiben). Zeit ist jedoch ein knappes Gut vor Weihnachten und das Problem Testdatensätze zu generieren werde nicht nur ich haben. Also frage ich, ob jemand eine fixe Lösung kennt.

Für Testdatensätze kann ich zwar prinzipiell joinen bis der Festspeicher aus geht, jedoch bringen so zusammengejointe Tabellen sehr viele doppelte Werte mit, was für den testfall nicht taugt.

Meine Ansätze

#!/bin/bash

for i in {1..1000000}
do

#FLOAT

v=$[100 + (RANDOM % 100)]$[1000 + (RANDOM % 1000)]
v=$[RANDOM % 50000].${v:1:2}${v:4:3}
echo -n $v
echo -n ' ; '

#INT

echo -n $((RANDOM%2147483647+1))$((RANDOM%2147483647+1))$((RANDOM%2147483647+1))
echo -n ' ; '

#DATE

echo -n $((RANDOM%28+1)).$((RANDOM%11+1)).$((RANDOM%7000+1753))
echo -n ' ; '

#STRING

echo $(od -An -N16 -i /dev/urandom) | echo -n $(base64)
echo -e '\r\n'

done

Bash ist halt wirklich extrem lahm und der Int-Teil spuckt gerade nicht mehr als ein 16bit int aus (nicht schön, aber für den Testfall vernachlässigbar). Der Umweg über eine .csv ist egal, BULK INSERT frisst auch sehr große Dateien in kürzester Zeit.


Ms SQL

CREATE TABLE Datum (id INT NOT NULL IDENTITY(1,1), datum DATE, PRIMARY KEY (id));
CREATE TABLE Zeichenkette (id INT NOT NULL IDENTITY(1,1), zeichenkette VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE Ganzzahl (id INT NOT NULL IDENTITY(1,1), ganzzahl INT, PRIMARY KEY (id));
CREATE TABLE Gleitkomma (id INT NOT NULL IDENTITY(1,1), gleitkomma FLOAT, PRIMARY KEY (id));	

DECLARE @counter INT;
DECLARE @max INT;
SET @max = 500000;
SET @counter = 0;
DECLARE @FromDate DATE = '1900-01-01'
DECLARE @ToDate DATE = '3000-12-31'


WHILE @counter < @max
   BEGIN
		INSERT INTO Gleitkomma (gleitkomma)
			SELECT RAND()
		INSERT INTO Datum (datum)
			SELECT dateadd(day, 
               rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), 
               @FromDate)
		INSERT INTO Zeichenkette (zeichenkette)
			SELECT SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 9)
		INSERT INTO Ganzzahl (ganzzahl)
			SELECT CONVERT(INT, @max*100*RAND())
		SET @counter = @counter + 1
	END;
GO

Hier fressen die einzelnen INSERT Befehle die Leistung und auch ohne Speichermangel stürzt das Ganze bei einem Loop der größer ist als 500.000 ab -.-

Edit: Das script ist nicht ganz aktuell, hab meine Windowskiste nur nicht zur Hand. Auf den Trichter den Index über den Primärschlüssel fallen zu lassen bin ich auch schon gekommen. Der Einfluss ist bei Ms SQL jedoch sehr klein.


Edit2:
Die Schleifen laufen mit einer stark begrenzten Größe ab, ich weiß, dass mich ein einzelner Durchlauf nicht auf meine gewünschte Größe bringt.

 

[rg88]

du solltest autocommit ausschalten, dann laufen die inserts VIIIEEEELLLL schneller, am ende einen manueller commit() auf die Verbindung.
Ich würde das in Python umsetzen, dass ist sehr performant

 

[Piktogramm]

SET IMPLICIT_TRANSACTIONS ON

Der erste Testlauf ist vielversprechend, zumindest begrenzt die SSD jetzt nicht mehr.

Was Phyton angeht, da müsste ich mich von 0 einarbeiten. Also werd ich mal schauen ob es ohne Autocommit über die Nacht genügend Daten fabriziert.

 

[rg88]

Python ist halt plattformunabhängig, recht einfach und unglaublich performant und damit genau für solche datenintensive DB-Aufgaben perfekt.
Um etwas einlesen kommst du dabei natürlich nicht herum, das Know-How zahlt sich aber über kurz oder lang sehr aus.

Kleiner Hinweis vorweg: Python ist nicht nur Synatx gesteuert sondern es ist dabei auch gewaltig wichtig, die Einrückungen sauber zu machen, dadurch spart man sich aber wiederum Unmengen an Klammern, was den Code mMn optisch recht schlank hält.

 

[nittels]

Hallo,

Tip1: SET NOCOUNT ON (bringt 5-10% Performance)
Tip2: BEGIN TRAN / COMMIT TRAN
Tip3: DB vorher in den simple recovery schalten (und danach wieder in full wenn notwendig)

Hier der modifizierte Script:

SET NOCOUNT ON;
BEGIN TRAN -- one big transaction
DECLARE @counter INT;
DECLARE @max INT;
SET @max = 1000000;
SET @counter = 0;
DECLARE @FromDate DATE = '1900-01-01'
DECLARE @ToDate DATE = '3000-12-31'
 
WHILE @counter < @max
   BEGIN
   --BEGIN TRAN -- small transaction block
		INSERT INTO Gleitkomma (gleitkomma)
			SELECT RAND()
		INSERT INTO Datum (datum)
			SELECT dateadd(day, 
               rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), 
               @FromDate)
		INSERT INTO Zeichenkette (zeichenkette)
			SELECT SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 9)
		INSERT INTO Ganzzahl (ganzzahl)
			SELECT CONVERT(INT, @max*100*RAND())
		SET @counter = @counter + 1
    --COMMIT TRAN -- commit small transaction block
    END;
COMMIT TRAN -- commit big transaction
GO

Einziges Manko bei dieser Art ist, dass du zwar sehr schnell bist, aber auch recht intensiv Ressourcen benötigst (zwischendurch wird in die tempb ausgelagert, das Translog wird immer voller weil du alles in einer Transaction machst usw...). Das "kann" zu einem Problem werden wenn die Anzahl der Datensätze in die zig Millionen geht. Behelfen kannst du dir dann auf 3 Arten:

1) Du machst immer Batches (z.B. im Millionenbereich)
2) Du machst jede Table extra
3) Du machst nicht alles in einer Transaction, sondern nur immer einen while Block, deshalb habe ich die BEGIN TRAN / COMMIT TRAN ausdokumentiert im While Block drin gelassen, die anderen nimmste dafür raus. Damit bist du zwar langsamer, aber die .ldf Datei im Simple recovery kann sich immer wieder automatisch leeren und wächst nicht und die tempdb gibt auch Ruhe. Ist halt ein Kompromiss solltest du von den Ressourcen her (am ehesten Plattenplatz) auf deine Grenzen stoßen.

 

[Piktogramm]

nittels, danke!

Recovery ist auf simple (schon immer gewesen )

Autocount könnte wirklich ausgeschalten werden!

rg88s Tip habe ich bereits so umgesetzt, dass zwei verschachtelte Schleifen laufen. Einmal der innere Insertblock, der wird aller 100.000 Durchläufen verlassen und die äußere Schleife führt danach zum Commit, und startet den Insertblock erneut. Das ganze Läuft so schon deutlich schneller. Was als Problem bleibt ist, dass schlicht die SSD nach kurzer Zeit auf einstellige Schreibraten einbricht.

Naja ich lass das so den Tag über laufen und werde für die angedachten Testläufe den Arbeitsspeicher stark beschränken. Das ist zwar ein Stück weg vom Einsatzfall, aber der Teil ist nicht wichtig genug als das ich da noch mehr Zeit verblase.

DANKE nochmal an Alle!

 

[nittels]

Gut gelöst; 2 Schleifen ist das was ich gemeint habe mit "in Batches von x machen".

Nur der Ästhetik halber;

Bei "SET IMPLICIT_TRANSACTIONS ON" ist der Scope die Session. Bei "BEGIN TRAN/COMMIT TRAN" ist der Scope der Batch.

imho sauberer, leserlicher und weniger fehleranfällig wäre "BEGIN TRAN/COMMIT TRAN". Bei deiner Methode hast du das Risiko dass wenn deine Session vielleicht später noch was macht bzw. du sie später nochmal benutzt du entweder daran denken musst wieder ein COMMIT zu machen oder die implicit transactions auszuschalten. Mit meiner Methode hast du mehr Kontrolle über den Script ohne die Session selbst zu beeinflussen.