Viele Projekte arbeiten mit Datenbanken und da muss natürlich die Performance des Systems getestet werden. Meist werden kleinere Datenmengen durch manuelle Tests eingegeben, aber wie erhält man Massendaten?
Die übliche Antwort lautet natürlich „Kauf eines der Tools“ – und meist zu Recht. Diese Tools haben eine (oft) recht einfach bedienbare UI, sind flott und vor allem können sie semantisch sinnvolle Daten generieren.
Daten generieren mit SQL
Dennoch, mit ein wenig SQL kann man auch selbst derartige Daten generieren. Interessant wird es bei der Verknüpfung von mehreren Tabellen, was im folgenden Beispiel eine wichtige Rolle spielt.
Szenario: Unser Projekt verwaltet Firmen und deren Anlagen und für diese sollen Rechnungsdaten generiert werden. Für diese Daten gibt es im Projekt verschiedene Ansichten und Abfragen. Wir möchten einige Bereiche mit wenig Daten haben (um die Funktion des Systems zu testen), aber auch Massendaten.
Zuerst erzeugen wir die Firmen (hier nur drei Stück) mit einer einzigen Anweisung. Das ist der erste wichtige Punkt, um eine gute Performance bei der Generierung der Daten zu erreichen. Natürlich könnte man auch drei INSERT-Statements nacheinander schreiben oder mit PL/SQL einen Loop 1..3 verwenden. Ab einer gewissen Zahl von Daten, die man erzeugen möchte, wird das aber massiv langsamer werden.
Teilabfrage „firma_seq“: Erzeugt eine Sequenz von 1..3
Insert Statement: Die Firma soll einen aussagekräftigen Namen erhalten (hohe Nummer = viel Daten).
insert into firma (name)
select
'Firma '||firma_seq.firma_nummer as name
from (
select rownum as firma_nummer
from dual
connect by level<=3
) firma_seq
;
Als nächstes möchten wir die Anlagen zu den Firmen erzeugen. Nicht vergessen: Wir wollen einige Bereiche mit wenig, andere mit viel Daten. Daher sollen zur ersten Firma drei Anlagen zugeordnet werden, zur zweiten Firma sechs Anlagen usw.
Teilabfrage „firma“: Durch das Ranking erhält man eine laufende Nummer 1..N, damit kann man auch einfach die gewünschte Anzahl Anlagen je Firma festlegen. Diese Abfrage produziert je Firma genau eine Zeile.
Teilabfrage „anlage_seq“: Wir benötigen eine Sequenz, um durch den Join auf die „firma“ Teilabfrage je Firma und gewünschte Anzahl von Anlagen eine Zeile zu generieren. Dazu nehmen wir eine großzügig geschätzte Sequenz. Der Join wird durch die zuvor ermittelte Anzahl der Anlagen je Firma begrenzt.
Insert Statement: Der Anlagenname soll erkennen lassen, zu welcher Firma diese Anlage gehört.
insert into anlage (firma_id, name)
select
firma.firma_id,
'Anlage '||firma.firma_nummer||'.'||anlage_seq.anlage_nummer
from (
select
firma_id,
dense_rank() over (order by firma_id) as firma_nummer,
dense_rank() over (order by firma_id) * 3 as anzahl_anlagen
from firma
) firma
join (
select rownum as anlage_nummer from dual connect by level<=1000
) anlage_seq on (anlage_seq.anlage_nummer <= firma.anzahl_anlagen)
;
Zuletzt soll noch je Firma und Anlage eine Abrechnung pro Kalendermonat generiert werden. Hier soll es weniger um die Joins gehen, stattdessen sollen Daten im Vordergrund stehen.
Kernstück für zufällige Daten ist in Oracle das dbms_random Package. Es bietet mehrere Funktionen zur Erzeugung von Zahlen an, mit dem Standardbereich 0..1 oder mit selbst definierten Bereichen. Die Nachkommastellen der generierten Zahlen muss man allerdings selbst durch Rundung kontrollieren. Es steht ein Generator für Zeichen zur Verfügung, mit einigen Modi wie Groß, Klein, alphanumerisch, …
Rechnungsnummer: Drei Großbuchstaben, gefolgt von sechs Ziffern
Betrag: Ein zufälliger Wert im Bereich 100..900 Euro.
Zuschlag: Bei ca. 10 % der Rechnungen soll ein Zuschlag von 50 € erhoben werden, das wird durch den Vergleich einer Zufallszahl im Bereich 0..1 auf < 0.10 erreicht.
insert into abrechnung (firma_id,anlage_id,abrechnungsmonat,rechnungsnummer,betrag,zuschlag)
select
firma.firma_id,
anlage.anlage_id,
to_date('01.'||monat||'.2021', 'dd.mm.yyyy') as abrechnungsmonat,
dbms_random.string('U',3) || round(dbms_random.value(10000,99999), 0) as rechnungsnummer,
round(100 + dbms_random.value(100,900),2) as betrag,
case when dbms_random.value<0.10 then 50 else null end as zuschlag
from firma
join anlage
on anlage.firma_id = firma.firma_id
join (
select rownum as monat from dual connect by level<=12
) monat_seq on (1=1)
order by 1,2,3
;
Fazit
Einen Schönheitspreis gewinnen diese Daten nicht, dafür sollte man besser auf die erwähnten Produkte zurückgreifen. Aber in unserem Projekt hat dieser Ansatz geholfen, vom ersten Tag an Daten in ausreichender Qualität und vor allem Quantität bereitzustellen. Damit konnten wir von Anfang an auch die Performance des Systems sicherstellen. Zudem kann im Endergebnis das System mehr Daten verarbeiten sowie diese auch schneller verarbeiten als ähnliche Systeme beim Kunden.
Source Code
--------------------------------------------------------------------------------------------------------------
-- Datenmodell anlegen
--------------------------------------------------------------------------------------------------------------
drop table abrechnung;
drop table anlage;
drop table firma;
create table firma (
firma_id number(9) generated as identity,
name varchar2(100) not null,
constraint pk_firma primary key (firma_id)
);
create table anlage (
anlage_id number(9) generated as identity,
firma_id number(9) not null,
name varchar2(100) not null,
constraint pk_anlage primary key (anlage_id),
constraint fk_anlage_01 foreign key (firma_id) references firma (firma_id)
);
create table abrechnung (
abrechnung_id number(9) generated as identity,
firma_id number(9) not null,
anlage_id number(9) not null,
abrechnungsmonat date not null,
rechnungsnummer varchar(30) not null,
betrag number(18,2) not null,
zuschlag number(18,2),
constraint pk_abrechnung primary key (abrechnung_id),
constraint fk_abrechnung_01 foreign key (firma_id) references firma (firma_id),
constraint fk_abrechnung_02 foreign key (anlage_id) references anlage (anlage_id)
);
--------------------------------------------------------------------------------------------------------------
-- Daten generieren
--------------------------------------------------------------------------------------------------------------
-- Alle Daten löschen
truncate table abrechnung;
truncate table anlage;
truncate table firma;
whenever sqlerror exit rollback;
-- Firmen erzeugen (hier 3)
insert into firma (name)
select
'Firma '||firma_seq.firma_nummer as name
from (select rownum as firma_nummer from dual connect by level<=3) firma_seq
;
commit;
-- Anlagen je Firma einfügen. Die erste Firma erhält drei Anagen, die nächste sechs Anlagen, ...
insert into anlage (firma_id, name)
select
firma.firma_id,
'Anlage '||firma.firma_nummer||'.'||anlage_seq.anlage_nummer
from (
-- Je Firma deren laufende Nummer ermitteln und die gewünschte Anzahl von Anlagen
select
firma_id,
dense_rank() over (order by firma_id) as firma_nummer,
dense_rank() over (order by firma_id) * 3 as anzahl_anlagen
from firma
) firma
join (
-- Eine Sequenz von Anlagen, durch den Join die benötigten Zeilen je Firma mit N Anlagen zu erhalten
select rownum as anlage_nummer from dual connect by level<=1000
) anlage_seq on (anlage_seq.anlage_nummer <= firma.anzahl_anlagen)
-- order by 1,2
;
commit;
-- Je Firma und Anlage eine Abrechnung je Kalendermonat erzeugen.
-- Die Rechnungsnummer ist ein zufälliger String mit drei Bucstaben und sechs Ziffern.
-- Der Betrag ist ein zufälliger Wert im Bereich 100..900 Euro.
-- Der Zuschlag von 50 Euro wird bei ca. 10% der Rechnungen gesetzt.
insert into abrechnung (firma_id,anlage_id,abrechnungsmonat,rechnungsnummer,betrag,zuschlag)
select
firma.firma_id,
anlage.anlage_id,
to_date('01.'||monat||'.2021', 'dd.mm.yyyy') as abrechnungsmonat,
dbms_random.string('U',3) || round(dbms_random.value(10000,99999), 0) as rechnungsnummer,
round(dbms_random.value(100,1000),2) as betrag,
case when dbms_random.value<0.10 then 50 else null end as zuschlag
from firma
join anlage on anlage.firma_id = firma.firma_id
join (select rownum as monat from dual connect by level<=12) monat_seq on (1=1)
-- order by 1,2,3
;
commit;
Dieser Beitrag wurde verfasst von:
