get-the-solution


Datenbankoptimierung

By
on Regards: C#; DBMS;

Hier möchte ich kurz einige Begriffe und Möglichkeiten in den Raum werfen die bei Datenbankoptimierungen eine Rolle spiele können. Hier noch ein guter Link http://www.codeproject.com/Articles/35665/Top-steps-to-optimize-data-access-in-SQL-Serv
  • Query optimieren (z.B. mit Include Actual Execution Plan)
  • Tabelle partitionieren wenn es Sinn macht
  • Indexes anlegen
    • Geht auch bei Views
    • Sollten von Zeit zu Zeit aktualisiert werden ansonsten hat man gleich wieder Performance einbußen
      • z.B. mit einem User Agent Job
  • DB Schema anpassen
    • Schneeflockenschema
    • Computed Values durch statisch Werte ersetzen – > die Werte können dann von einem Agent Job aktualisiert werden
  • Kimball and Ross http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho
  • Hardware des Datenbank Server upgraden

Verwandte Themen: Datenbank erstellen mit dem ER-Diagramm und MySql


Email Subject mit UTF8 im Header

By
on Regards: C#; DBMS;

Emails sind laut Spezifikation in ASCII codiert weshalb man bei der Darstellung der Zeichen im Email sehr beschränkt ist.

Es gibt zwar die Möglichkeit im Header mit der Eigenschaft Content-Type (Content-Type: text/plain; charset=UTF-8) die Codierung mit anzugeben damit der Email Client weiß wie der Content Body codiert ist. Jedoch trifft das eben nicht auf den Header zu. Wie kann man dann also das Subject im Header mit UTF-8 codieren?

Um im Header die ASCII beschränkung zu umgehen gibt es die “MIME (Multipurpose Internet Mail Extensions) Part Three: Message Header Extensions for Non-ASCII Text” RFC Erweiterung. Der Emailclient muss diese RFC Erweiterung natürlich auch unterstützen. Diese RFC beschreibt zwei Möglichkeiten das Subject in einer anderen Codierung an zu geben.

  • Quoted Printable (subject starts with “=?utf-8?Q”)
  • Base64 (subject starts with “=?utf-8?B”)

Wir beschränken uns fürs erste auf die Base64 Variante da sie im Vergleich zur ersten weniger Zeichen benötigt.

Der Aufbau des Headers hat somit wie folgt aus zu sehen:

=?charset?B?text?=

Den Subject Text codieren wir in einen base64 String. Entweder ihr macht das per Code oder schnell online (https://www.base64encode.org/).

Anschließend muss man nur noch das Subject setzen. Kann z.B. so aussehen:

EXEC MSDB.dbo.sp_send_dbmail […] @subject = ‘=?UTF-8?B?base64text?=’

Links:


MySQL Cursor alternative – Dynamische SQL Cursor

By
on Regards: Archive; DBMS;

vor kurzem stand ich vor dem Problem, dass ich in einer MySQL Stored Procedure einen Cursor benötigt habe. Das Problem an der ganzen Sache war, dass für die Cursor Deklatraion einige Abfragen (u.a insert into, select usw.) vorausgesetzt wurden. Das Cursor-Statment wird quais während der Laufzeit “generiert”.

Das hat zur Folge, dass man einen Deklaration Block hat auf den eben die nötigen Abfragen zum Zusammenstellen des Cursor-Statments machen muss. Erst dann kann der Cursor mit DECLARE definiert werden. Das Problem wie folgt:

CREATE PROCEDURE `tsc_sp_add_page` (in idfolder int(11), in title varchar(65), in template varchar(100))
COMMENT 'creates a new page and adds all wildcards which will be shown infolder,InLanguage,InAll,InPage'
BEGIN
--id of the inserted page
DECLARE pageid INT(11);
...ein paar Deklaration
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
...dann die vorausgesetzten Abfragen für die Definition des Cursors
--get the language of the page
set pagelangid = (SELECT f.idlanguage as pagelangid FROM tsc_folder AS f WHERE f.id=idfolder LIMIT 0 , 1);
--first we create the page record
INSERT INTO `tsc_page` (`id` ,`idfolder` , `title` , `template`) VALUES (NULL , idfolder, title , template);
set pageid = LAST_INSERT_ID();
...cursor deklaration ist abhänig vom insert into statment
declare c_wildcardInAll cursor FOR select distinct w.id from tsc_wildcard AS w, tsc_page_have_wildcard AS wh, 
tsc_page AS p, tsc_folder AS f
WHERE wh.idpage = p.id
AND wh.idwildcard = w.id
AND p.idfolder = f.id
AND w.InLanguage =1
AND f.idlanguage =pagelangid;

Laut MySQL Syntax müssen aber alle Deklaration ganz zu Beginn der SP stehen. Nach der ersten Abfrage darf keine weitere Deklaration mehr folgen. Mit dem obigen Code-Ausschnitt erhält man dann die Meldung:

Error code 1064, SQL state 42000: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near
'declare c_wildcardInAll cursor FOR select distinct w.id from tsc_wildcard AS w, ' at line 29
Line 7, column 1

Jetzt wäre es toll, wenn es so etwas wie dynamische Cursor oder Parameter Cursor geben würde, was aber in MySQL 5.0.24 nicht der Fall war (Evtl wird ein solches Feature bei der nächsten Version nachgereicht?, Orcael kennt z.B. Cursor mit Parameter).

Um keine Cursor verwenden zu müssen, kann man eine temporäre Tabelle verwenden, die quasi als Cursor fungiert. Die temporäre Tabelle muss genau auf die Abfrage angepasst werden. Zusätzlich benötigt man eine weitere RowID Spalte um in der Schleife auf alle Datensätze zugreifen zu können, bzw. um iterieren zu können.

1.) Temporäre Tabelle erstellen

Da ich bei meiner Abfrage nur eine Spalte abfrage, sieht meine temporäre Tabelle recht simpel aus.

CREATE TEMPORARY TABLE twildcardInLang (
`RowID` int(11) NOT NULL auto_increment PRIMARY KEY,
`id` int NOT NULL
) ENGINE=MEMORY;

2.) Daten abfragen und in die Tabelle schreiben

Wichtig ist, dass die RowID die Eigenschaften auto_increment und PRIMARY KEY hat. Ansonsten kann man nicht durch die Datensätze iterieren. Danach fügen wir die Datensätze in die temporäre Tabelle ein.

INSERT INTO twildcardInLang (id) select distinct w.id from tsc_wildcard AS w, tsc_page_have_wildcard AS wh, tsc_page AS p, tsc_folder AS f
WHERE wh.idpage = p.id
AND wh.idwildcard = w.id
AND p.idfolder = f.id
AND w.InLanguage =1
AND f.idlanguage =pagelangid;

3.)Hilfsvariable für die Schleife setzen

Nun müssen wir wissen wie oft wir die Schleife durchlaufen wollen. Dies entspricht logischerweise der Anzahl der Datensätze in unserer temporären Tabelle. Als nächstes setzen wir die Zähl-Variable auf 1 um die Schleife durchlaufen- und die Daten der Tabelle abfragen zu können.

SET @NumberRecords = (select count(id) as id from twildcardInLang);
SET @RowCount = 1;

4.) Die Schleife erstellen

Der Code der Schleife sollte nun für sich sprechen.

WHILE @RowCount <= @NumberRecords DO
set @wildcardid = (SELECT id FROM twildcardInLang WHERE RowID = @RowCount);
...irgendwas mit @wildcardid machen
SET @RowCount := (@RowCount + 1);
END WHILE;

5.) Am Schluss entfernen wir die temporäre Tabelle mit DROP TABLE twildcardInLang;

Der komplette Code-Ausschnitt sieht dann in etwa so aus:

CREATE TEMPORARY TABLE twildcardInLang (
`RowID` int(11) NOT NULL auto_increment PRIMARY KEY,
`id` int NOT NULL
) ENGINE=MEMORY;

--get InLanguage=pagelangid wildcards with proper language wildcards and insert it into our temporary table (which we use as "virtual curosr")
INSERT INTO twildcardInLang (id) select distinct w.id from tsc_wildcard AS w, tsc_page_have_wildcard AS wh, tsc_page AS p, tsc_folder AS f
WHERE wh.idpage = p.id
AND wh.idwildcard = w.id
AND p.idfolder = f.id
AND w.InLanguage =1
AND f.idlanguage =pagelangid;
-- Get the number of records in the temporary table
SET @NumberRecords = (select count(id) as id from twildcardInLang);
SET @RowCount = 1;
WHILE @RowCount <= @NumberRecords DO
set @wildcardid = (SELECT id FROM twildcardInLang WHERE RowID = @RowCount);
-- link wildcard with page
INSERT INTO `tsc_page_have_wildcard` (`idpage`, `idwildcard`) VALUES (pageid, @wildcardid);
SET @RowCount := (@RowCount + 1);
END WHILE;
DROP TABLE twildcardInLang;
...

 

Links zum Thema:

http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

http://dev.mysql.com/doc/refman/5.0/en/while-statement.html


Datenbank erstellen mit dem ER-Diagramm und MySql

By
on Regards: DBMS;

Datenbanken erstellen / modellieren mit dem ER-Modell

Ein Modell ist ein Ausschnitt aus der Realität oder ein gedankliches Konzept. Das Entity-Relationship-Modell ist ein Modell das aus Gegenständen und Beziehungen besteht. Es wird häufig verwendet um eine Datebank zu designen.

Beim ERM-Model handelt es sich bei den Entities meist um einen physikalischen Gegenstand, oder um ein gedankliches Konzept. Die Beziehungen zwischen den Gegenständen werden abstrahiert.

Grundlagen

Beziehungstypen

Datenbank erstellen

Literatur

Grundlagen

Entity

Ein Gegenstand wird im Modell durch ein Rechteck abgebildet. Der Gegenstand kann Eigenschaften enthalten die durch Attribute dargestellt werden. Attribute die zu einem Gegenstand gehören werden durch Linien verbunden.

Schlüssel sind eine minimale Menge von Attributen, deren Wert eine Entity eindeutig innerhalb aller Entities eines Types identifizieren. Schlüssel werden durch unterstrichene Attributnamen dargestellt.

Generalisierung

Die Generalisierung soll für ein übersichtliches und strukturiertes Modell sorgen. Dies kann erzielt werden, in dem Entities mit den gleichen Attributen zu einem Basis Entity zusammengefasst werden. Die Unterentities „erben“ die Attribute des Basis Entitys. Das Erben der Unterenties kann symbolisch durch einen Pfeil dargestellt werden. Im unteren Beispiel erben Mitarbeiter und Kunde die Attribute von Person.

Relationship

Beziehungstypen werden durch Rauten dargestellt. Diese werden dazu passend beschriftet.

Beziehungstypen

Es gibt verschiedene Beziehungstypen.

1:1 Beziehung

Jedem Entity m aus Mann wird höchstens ein Entity f aus Frau zugeordnet. Umgekehrt verhält es sich genauso. Jedem Entity f aus Frau wird maximal ein Entity m aus Mann zugeordnet. Es kann auch vorkommen, dass Entities keinen Partner haben.

Die Relation kann wie folgt in Worten beschrieben werden.

Ein Mann ist mit einer Frau verheiratet. Eine Frau ist mit einem Mann verheiratet.

1:n Beziehung

Jedem  Entity h aus Haus wird kein oder beliebige viele Entities aus Bewohner zugeordnet. Jedes Entity b aus der Menge Bewohner steht maximal einem Entity aus Haus in einer Beziehung.

Die Relation kann wie folgt in Worten beschrieben werden.

Ein Haus hat mehrere Bewohner. Mehrere Bewohner können in einem Haus sein.

n:1

Verhält sich wie die 1:n Beziehung nur umgekehrt.

N:m

Wenn keine Einschränkungen und Begrenzungen gelten, also  jedes Entity aus E1 mit beliebig vielen Entities aus E2 in Beziehung stehen kann und umgekehrt jedes Entity aus E2 mit beliebig vielen Entities aus E1 assoziiert ist, handelt es sich um eine n:m Beziehung. (vgl. Alfons Kempler 2009)

 

Min / Max Notation

Mit der Min / Max Notation kann man die Beziehungstypen genauer spezifizieren. Wir können z.B. sagen, dass kein oder ein Haus von genau einer Person besessen wird. Eine Person kann aber 0 oder mehrere Häuser besitzen. Die Notation würde dann wie folgt aussehen.

Da wir beim Erstellen der Datenbank die Min / Max Notation außer Acht lassen, verweise ich deshalb für weitere Informationen auf den Wikipedia Artikel und auf das Kapitel 6.6 Die (min, max) – Notation von der Online-Kurs ‘Datenbanken und Datenmodellierung’ Print Version.

Datenbank erstellen

Nach dem also das Model nach den ER-Diagramm Richtlinien erstellt wurde, gilt es den Entwurf in eine Datenbank zu übertragen. Ich gehe ab hier davon aus, dass man das Grundlegende über Datenbanken weiß.

Fremdschlüssel dienen als Verweis zwischen zwei Relationen, d. h. er zeigt an, welche Tupel / Zeile der Relationen inhaltlich miteinander in Verbindung stehen.

Als erstes beginnt man damit, die Entities in Tabellen um zu wandeln. Als Tabellenamen werden vorzugsweise die Entitynamen verwendet. Alle Attribute werden in der Tabelle als Spalten dargestellt. Dabei muss man sich jetzt überlegen, welches Attribut welchen Datentyp verwenden soll.

Folgendes Modell kann man wie folgt in eine Tabelle übersetzen (für das Beispiel wurde eine MySQL DB verwendet):

 

CREATE TABLE `Autor` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 50 ) NULL
) ENGINE = InnoDB;

Wie man sehen kann wurde der Attributname „Identifikationsnr“ auf id verkürzt und als Primary Key gesetzt. Für den Name wurde der Datentyp varchar verwendet. So werden die Entities der Reihe nach in die Datenbank „übersetzt“.

Als nächstes muss man in der Datenbank die Voraussetzungen dafür schaffen, dass die Beziehungen abgebildet werden können. Am Einfachsten ist die n:m Beziehung ab zu bilden.

 

Aus der Min / Max Notation „(0,*)“  erkennen wir, dass es sich um eine „m:n“ Beziehung handelt.

N:M Beziehungen benötigen immer eine Hilfstabelle! In der Hilfstabelle müssen wir sogenannte Fremdschlüssel verwenden, um auf die jeweilige Tabelle zu verweisen. In die Hilfstabelle erstellen wir also zwei Spalten, die jeweils die Bezeichnung auf der zu verweisenden Schlüssel verwenden. Da die Beziehung „besuchen“ heißt, erstellen wir passenderweise eine Tabelle namens besuchen. Darin erstellen wir die zwei Spalten Schuelerid und Fachid. Das sieht dann so aus:

CREATE TABLE `Schueler` (
`Schuelderid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 100 ) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `Unterrichtsfach` (
`Fachid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 50 ) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `besuchen` (
  `Schuelderid` int(11) NOT NULL,
  `Fachid` int(11) NOT NULL
) ENGINE=InnoDB;

Anbei noch ein paar Testeinträge:

INSERT INTO `Schueler` (`Schuelderid` ,`Name`)
VALUES (NULL , 'Martin'), (NULL , 'Simon');

INSERT INTO `Unterrichtsfach` (`Fachid` ,`Name`)
VALUES (NULL , 'Betriebssysteme'), (NULL , 'Datenmodellierung');

Wenn Martin jetzt die die Unterrichtsfächer ‘Betriebssysteme’ und ‘Datenmodellierung’ besuchen soll, müssen wir die entsprechenden Einträge in die Tabelle ‘besuchen’ machen. Z.B. mit:

INSERT INTO `besuchen` (
`Schuelderid` ,
`Fachid`
)
VALUES ('1', '1'), ('1', '2');

INSERT INTO `besuchen` (
`Schuelderid` ,
`Fachid`
)
VALUES ('2', '2');

Nun fragen wir ab welche Fächer Martin besucht.

SELECTs.name,u.name
FROM`besuchen`ASb,UnterrichtsfachASu,SchuelerASs
WHEREb.fachid=u.fachid
ANDs.Schuelderid=b.Schuelderid
ANDs.name='Martin';

Wer will kann zur Hilfstabelle noch eine dritte Spalte id hinzufügen.

Aus der unteren Abbildung ist erkenntlich, dass ein Schüler 0 oder maximal einen Computer  verwenden kann. Ein Computer ist 0 oder mehreren Schüler zugeordnet. Es handelt sich also hier um eine 1:N Beziehung. Da wir im ersten Schritt alle Entities in der Datenbank implementiert haben, sieht unsere Computer Tabelle so aus:

CREATE TABLE `Computer` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 10 ) NOT NULL
) ENGINE = InnoDB;

Anders wie bei einer N:M Beziehung muss nicht eine Hilfstabelle erstellt werden. Der Fremdschlüssel der Computertabelle kann direkt in die Tabelle des Schülers hinzugefügt werden.

ALTER TABLE`Schueler`ADD`computerid`INTNULLCOMMENT' verweis auf tabelle computer'

In die Computertabelle dürfte man keine Spalte erstellen um den Fremdschlüssel vom Schüler zu speichern, weil laut ER-Diagramm ein Computer mehreren Schüler zugeordnet sein kann.

Bei dieser Abbildung wird davon ausgegangen, dass dem Schüler ein Computer zugeordnet sein kann, aber nicht muss. Deshalb sind in der Spalte computerid NULL Einträge erlaubt. Falls man davon ausgeht, dass ein Schüler 0 oder mehrere Computer verwendet, müsste man dies mit einer N:M Beziehung realisieren.

Theoretisch könnte man eine 1:N Beziehung auch mit einer Hilfstabelle realisieren, jedoch versucht man in der Praxis, überflüssige Tabellen zu vermeiden. Denn wenn man bei größeren ER-Diagrammen alle Beziehungen mit Tabellen übersetzt, kann die Datenbank schnell unübersichtlich werden.

 

Bei einer 1:1 Beziehung wird so vorgegangen wie im oberen 1:N Beispiel. Die Verwendung von Hilfstabellen ist nicht möglich. Um die Tabellen miteinander zu verknüpfen, wird in die jeweilige Tabelle für die man sich entscheidet, eine zusätzliche Spalte erstellt in die man den Fremdschlüssel speichert. Ein Beispiel dazu:

CREATE TABLE `Mann` (
`idmann` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = InnoDB;

CREATE TABLE `Frau` (
`idfrau` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 100 ) NOT NULL
) ENGINE = InnoDB;

Die Beziehung kann folgendermaßen realisiert werden:

ALTERTABLE`Mann`ADD`idfrau`INTNULLCOMMENT'verweis auf tabelle frau‘

Dabei spielt es keine Rolle welche Tabelle verwendet wird. Wir hätten genauso zur Tabelle Frau die Spalte mit dem Fremdschlüssel hinzufügen können.

Hat man alle Beziehungen in der Datenbank implementiert kann man als nächstes die Integritätsbedingung erstellen. Die Erstellung der Referenzielle Integritätsbedingung hier auch noch auszuführen sprengt allerdings den Rahmen. Ich verweise deshalb vorerst auf den Wikipedia Artikel “Referentielle Integrität“.

Literatur

Beziehungen, 09.02.2011, http://sql.idv.edu/thema/dbgrundlagen/beziehungen.htm#5

Referentielle Integrität, 09.02.2011, http://de.wikipedia.org/wiki/Referentielle_Integrit%C3%A4t#Verwendung_in_Datenbanksystemen

Integritätsbedingung, 09.02.2011, http://de.wikipedia.org/wiki/Integrit%C3%A4tsbedingung

A. Kemper A. Eickler Datenbanksysteme, 09.02.2011, http://www-db.in.tum.de/research/publications/books/DBMSeinf/

Alfons Kempler. (2009) Datenbanksysteme – Eine Einführung 7 Auflage, Oldenbourg Wissenscahftsverlag GmbH München