SQL Server 2008 R2 - Datenbankdesign (Indizierung)

Question

Hallo zusammen,

bevor ich meine Frage stelle, möchte ich euch vorerst ein paar Hintergrundinformationen und einen Überblick über die aktuelle Datenbankstruktur geben. Ich beschäftige mich derzeit mit einer Art "Data Warehouse" bei einer Bank und möchte hier die Performance des Datenzugriffs optimieren.

Istsituation:

Die Daten, Konto und Personendaten, werden dezeit per Ultimo (monatlich) ausgwertet und einen SQL Server 2008 abgelegt. Die Selektion wird dann vorwiegend über MS Access 2003 über ODBC-Verknüpfungen oder SQL-Pass-Throught ( SQLPT) Abfragen vorgenommen. Die Abfragen lassen sich dezeit in zwei Kategorien unterteilen:

1. Einzelwerte

Hier wollen einzele Kontoinformationen, Salden etc. auf Einzeldatensatzebene selektiert werden => keine Agregation. Das heist, das beispielsweise alle Girokonten für Folgeabfragen über eine SQLPT-Abfrage bereitgestellt werde. Anschliesend werden bassierend auf dieser Abfrage weitere Selektionen vorgenommen (Joins mit lokalen Daten, Agreggationen etc.).

2. Aggregierte Werte

Hier werden in einer SQLPT Abfrage bereits aggregierte Werte übertragen, welche soch also in überschaubarem Maß halten. Anschließend werden diese Daten ebenfalls in Excel oder Access weiterverarbeitet (ähnlich wie oben).

Datenmenge und Tabellenstruktur:

Tabelle Konten: Hier werden alle Konten pro Monat mit allgemeinen Informationen abgelegt. Der Primary Key ist dezeit eine Spalte (ID) des Dateityps Char mit ca. 20 Zeichen (Verkettete Kontonummer mit dem jewiligen Monat: 201108_Kontonummer). Pro Monat fallen hier ca. 500.000 Datensätze an. Aktuell sind ca. 4,5 Mio. Datensätze in der Tabelle enthalten.

Tabelle Detaildaten Girokonten: Diese Tabelle binhalten nur Informationen welche bei Girokonten relevant sind. Hier ist der gleiche Primary Key (ID) wie oben vorhanden. Pro Monat fallen hier ca. 150.000 Datensätze an. Aktuell sind ca. 1,5 Mio. Datensätze in der Tabelle enthalten.

Tabelle Personendaten: Diese Tabelle beinhaltet Informationen zum Kunden. Der Primary Key ist heir ebenfalls eins Spalte (ID_Pers) des Typs Char mit der ähnlichen Länge und Aufbau (Verkettete Kundennummer mit dem jeweiligen Monat: 201108_Kundennummer). Dieses Verknüpfungsfeld wird als Fremdschlüssel in der Tabelle Konten geführt. In der Tabelle Personendaten fallen die meisten Datensätze an. Pro Monat ca. 600.000, dezeit also fast 6. Mio.

Tabelle Berater/Mitarbeiter-Daten: Diese Tabelle speigelt die Organisationsstruktur des Unternehmens mit sämmtlichen Mitarbeiterdaten wieder. Der Primary Key besteht hier aus zwei Spalten, der OE (Organisationseinheit) und dem Monat (als Integer, 201108). Verknüpft zu einem Konto wird über die Tabelle Personendaten vorgenommen (Spalte OE und Spalte PM - jeweils in der Tabelle Personendaten Indizier als nicht eindeutiger, und nicht gruppierter Index).

Beispiel für eine Selektion bei der ich die Perfomance erhöhen möchte.

Select Konten.KontoNr, Konten.Saldo, DetaildatenGirokonten.Aktueller_Zinssatz, Personendaten.Betreuende_OE, Mitarbeiterdaten.Mitarbeitername (in wirklichkeit kommen noch ca. 20 Spalten aus den unterschiedlichen Tabellen hinzu).

From Konten KTO

Inner join DetaildatenGirokonten DetGiro on KTO.ID = DetGiro.ID

Inner join PersonenDaten PE ON KTO.ID_PERS = PE.ID_Pers

Inner Join Mitarbeiterdaten MA ON PE.OE = MA.OE AND PE.Monat = MA.Monat

Where Monat = 201108 (August 2011).

-------------------------------------------------------------------------------------------------------------------------------

Dieses Beispiel kann auf die meisten Auswertungen übertragen werden. Speziell diese Auswerzung benötigt auf dem SQL Server 2 Minuten, bis alle relevanten Datensätze angezeigt werden (Abfragezeit bleibt stehen, alle 150.000 Datensätze sind geladen).

Wird dieser SQL-Befehl nun in eine SQLPT Abfrage in Access übertragen und als Basis für weitere Abfragen verwendet, muss meines Wissens bei jedem Zugriff die Abfrage ausgeführt/neu berechnet werden. Selbst wenn ich diese Abfrage lediglich in einer Access-Abfrage über "Tabelle hinzufügen" einfügen möchte, wird eine erhebliche Wartezeit verursacht.

Ich habe hier versucht die derzeitige Situation so gut wie möglich zu beschreiben. Solltet Ihr nich Fragen haben, dürft Ihr diese natürlich gerne stellen.

Mich würden nun Tipps zur Optimierung der Joins, Indizierung oder generell zur Perfomancesteigerung brennend interessieren. Gerne dürfen auf Ideen zur Optimierung der Weiterverarbeitung in Access (ODBC, SQLPT) geäußert werden.

Vielen Dank für eure Unterstützung und Hilfe.

Freundliche Grüße Thommy1589

Answer 1

Hallo Thommy,

grundsätzlich ist der Bereich "Optimierung" aus Sicht des SQL-Servers sehr weitreichend. Ich würde mich an Deiner Stelle eventuell mal mit den folgenden Punkten beschäftigen

Datenbanken: http://msdn.microsoft.com/en-us/library/ms191149.aspx
Tempdb: http://msdn.microsoft.com/en-us/library/ms175527.aspx
Partitionierung: http://msdn.microsoft.com/en-us/library/ms178148.aspx
Indexierung: http://msdn.microsoft.com/en-us/library/ms190910.aspx
- Parallele Indexoptimierung http://msdn.microsoft.com/en-us/library/ms189329.aspx
- ...

Pauschale Antworten kann es IMHO nicht geben. Viele Entscheidungen müssen davon abhängig gemacht werden, welche Hardware Du einsetzt. Wie die Datenstrukturen aussehen, welche Indexes gesetzt und verwendet werden, wie sehen die Abfragepläne aus, ...

Wie Du siehst, gibt es eine ganze Menge Optionen, an denen man "schrauben" kann.

Mit "einem Satz" wäre das also nicht getan.

Ich arbeite an einem ähnlichen Projekt wie Du und erstelle gerade das Fachkonzept - allein für die Optimierungsmöglichkeiten haben wir fast zwei Wochen intensiv recherchiert und ausprobiert.

 

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 2

Dem wäre imho nur noch hinzuzufügen, das es für OLAP andere Ansätze gibt:

Fast Track Data Ware House geht im Besonderen auch auf die notwendig (Hardware-)Infrastruktur ein.

Und dann gibt es noch den Analysis Services:

SQL Server Analysis Services - Data Mining
SQL Server Analysis Services - Multidimensional Data

Im Besonderen bieten die Analysis Services noch PowerPivot und DAX für Excel.

Answer 3

Hallo Thommy,

beim Aufbau eines DWH gibt es einige Regel, eine rechte einfache dabei ist:
Immer den kleinst möglichen Datentypen verwenden, der zum Darstellen der aktuellen Daten ausreicht.

Es gibt die Beispieldatenbank "AdventureWorks DW" von CodePlex (http://msftdbprodsamples.codeplex.com/), die auch als Basis für den Olap Cube dient.
Wenn man da die Version für 2005 mit der für 2008/2008R2 vergleicht, gibt es hier einen nennenswerten Unterschied.
In 2008 werden Datumswerte als Int-Datentyp gespeichert (Format YYYYMMDD), in 2005 war es noch datetime; das spart 2 Bytes ein.
Das hört sich nicht nach viel hat, aber die Summe macht es. Es müssen je Datensatz 2 Bytes weniger gespeichert werden, das heisst auch 2B weniger schreiben und später lesen. Zudem ist es der PK und damit wird auch für den Index weniger Platz benötigt. Man braucht weniger Hauptspeicher und so kann mehr Daten in den Cache geladen werden.
Und so weiter ... die Summe macht es halt.

Und da fällt mir bei Dir gleich die ID für die Konten auf; ein CHAR(19) Wert.

In DE sind Kontennummern rein numerisch und maximal 10 Stellen lang. Dazu kommt dann noch der Datumswert mit 8 Stellen; sind 18 Stellen numerisch.
Das passt in den BIGINT Datentypen und der braucht nur 8 Byte; somit sparst Du 11 Byte ... die Summe macht es.

 

DECLARE @account varchar(10);
SET @account = '0123456789'

-- http://msdn.microsoft.com/de-de/library/ms187745.aspx
DECLARE @id bigint;        --= 8 Byte
SET @id = CONVERT(bigint, CONVERT(char(8), GETDATE(), 112))
          * 10000000000
          + CONVERT(bigint, @account);

Klaus Aschenbrenner hatte mal einen guten Vortrag zum Thema Performance Tuning gehalten, wo er auch auf das Thema wie Daten in den Pages gespeichert werden und welchen Einfluß die verwendeten Datentypen hierauf haben etc. Bisher habe ich die Slides noch nicht gefunden, falls ich drüber stolpere, liefere ich den Link nach.

Was Du auch noch als zukünftiges Thema im Auge behalten solltest, ist der Columnstore Index, der mit SQL Server Denali kommen wird. 

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Hallo zusammen, 

 

zuersteinmal, vielen Dank für die Antworten!

@Olaf: Vielen Dank für den direkten Optimierungsvorschlag! Daran habe ich als absoluter neuling beim Entwurf der DB nicht gedacht. Das ist das Problem bei "Lerning-by-Doing" :)

Noch eine Frage zum Primary Key: Macht es Sinn, den PK wirklich in einer Spalte zu speichern, oder reicht es aus, den PK aus zwei Spalten zusammenzusetzten? Wie sind hier die Performanceunterschiede? Ich würde mir hier denke ich nochmal einiges an Speicherbedarf sparen?

 

Vielen Dank für deine AW.

 

MFG Thommy

Answer 5

Hallo zusammen, 

 

zuersteinmal, vielen Dank für die Antworten!

@Olaf: Vielen Dank für den direkten Optimierungsvorschlag! Daran habe ich als absoluter neuling beim Entwurf der DB nicht gedacht. Das ist das Problem bei "Lerning-by-Doing" :)

Noch eine Frage zum Primary Key: Macht es Sinn, den PK wirklich in einer Spalte zu speichern, oder reicht es aus, den PK aus zwei Spalten zusammenzusetzten? Wie sind hier die Performanceunterschiede? Ich würde mir hier denke ich nochmal einiges an Speicherbedarf sparen?

 

Vielen Dank für deine AW.

 

MFG Thommy

Hallo Thommy,

ev. solltest Du dir generell noch mal ein paar Informationen zu Datenbankdesign und Relationalem Datenmodell antun. Grundsätzlich hat ein PK zwei Aufgaben innerhalb einer Relation:

- Eindeutigkeitsbeschreibung des Datensatzes
- Einschränkung (CONSTRAINT) für DRI (Referenzielle Integrität)

Wenn man das weiß, stellt sich die Frage nach 1, 2 oder ... Attributen, die als PK verwendet werden sollen, nicht mehr.

Nehmen wir z. B. ein klassisches Modell von Auftrag und bestellter Ware...

Die Eindeutigkeit eines Auftrages wird durch die Vergabe einer Auftragsnummer gewährleistet. Geht man nun aber einen Schritt zurück und folgt den Ausführungen von Olaf, sollte man sich überlegen, ob ein Primärschlüssel auf einer Auftragsnummer (i. d. R. immer verbunden mit Buchstabenfolgen), würde diese Entscheidung zwangsläufig dazu führen, daß der PK unnötig Speicher benötigt.

Wenn ich eine eindeutige Auftragsnummer benötige, kann ich das auch durch einen UNIQUE INDEX gewährleisten.

Primär geht es jedoch bei der Definition eines PK darum, die referenzielle Integrität zwischen dem Auftrag und der bestellten Ware zu gewährleisten. Von daher müßte dann ja der Primärschlüssel aus tbl_Auftrag auch in der Relation tbl_AuftragWare geführt werden. Wir haben also zwei Mal unnötig Datenspeicher verbraucht.

Stattdessen verwendet man lieber eine Surrogatschlüssel, der als PK verwendet wird.

Für das obige Konstrukt wäre es so, daß als PK ein Attribut mit dem Namen ID (Beispiel) und dem Datentype int verwendet wird. Diesen Schlüssel führe ich dann in der Relation tbl_AuftragWaren mit.

 

CREATE TABLE dbo.tbl_Auftrag
(
    Id    int    NOT NULL    PRIMARY KEY NONCLUSTERED,
    ...
)
GO

CREATE TABLE dbo.tbl_AuftragWare
(
    Auftrag_Id    int NOT NULL,
    Ware_Id       int NOT NULL,
    ....,

    CONSTRAINT pk_tbl_AUftragWare PRIMARY KEY
    (Auftrag_Id, Ware_Id),

    CONSTRAINT fk_tbl_AuftragWare FOREIGN KEY (Auftrag_Id)
    REFERENCES dbo.tbl_Auftrag (Id)
    ON DELETE CASCADE
)

 

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 6

Hallo Uwe,

weshalb wäre es sinnvoll, einen künstlichen Schlüssel zu generieren? Wenn ich den Monat mit der KontoNR verkette und als BigINT habe ich einen eindeutigen Schlüssel. Die Werte Konto-Nummer und Monat ändern sich auch nicht, daher muss ich mir über die Eindeutigkeit hier auch keine Gedanken machen, oder?

 

So wie ich deine AW nun verstanden habe, empfiehlts du mir, den PK als Attribut zu speichern?

 

MFG Thommy

Answer 7

Hallo Thommy,

ich habe wohl eher Deine Aufgabenstellung nicht richtig gelesen ;)

Entscheidend ist, daß der PK nicht "zu groß" (mehrere Attribute) wird.

Von daher ist die Entscheidung (Du hast ausgeführt, daß Kto + Monat immer eindeutig sind) für EIN Attribut IMHO die richtige Entscheidung!

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 8

Hallo Uwe,

ok, habe nun die Spalte ID neu generiert (Konto+Monat als BigInt). Wenn ich den Datenbankoptimierer starte, wird mir speziell für diese Abfrage vorgeschlagen, einen Index mit alle in der Abfrage enthaltenen Spalten zu erstellen. Mach sowas Sinn? Kostet schleißlich enorm viel Speicher!?!

MFG Thommy

Answer 9

Hallo Thommy,

das ist so nicht einfach zu bestimmen (Glaskugel).
Wenn die Abfrage schlecht programmiert ist, kann es schon sein, daß der SQL Server für die Optimierung andere Indexe vorschlägt.
Sehr häufig reicht es schon aus, die Abfrage selbst zu optimieren.

Stelle doch einfach mal Scripte der Struktur mit ein paar Spieldaten und DDL der Abfrage ein.
So wird das nur Glaskugellesen :D

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 10

Hallo Uwe,

könntest du deinen Wunsch etwas konkretisieren? Welche Scripte? Wie die vielleicht gemerkt hast...bin ich noch kein Profi ;-P

Danke.

 

MFG Thommy

 

Answer 11

Hallo Thommy,

das ist rech einfach. In der Abfrage verwendest Du ja ein paar Relationen. Rechtsklick auf die Relationen -> script in neues Fenster.
Dazu dann noch das gleiche mit den Indexes der einzelnen Relationen...

Anschließend noch perm DML ein paar Testdaten (=> INSERT INTO [DeineTabelle] (Feldliste) VALUES (Werte).

Müssen ja nicht viele Daten sein - einfach nur so viel, um sich von der Abfrage mal ein Bild zu machen.

Zu guter Letzt dann noch das gleiche von der Abfrage - fertig iss es ;)

Dann können wir das hier mal nachspielen und den Abfrageplan anschauen.
Nur so kann man konrekt mal sagen, was ev. optimiert werden kann.

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 12

Hallo,

ok..ich werde mich bemühen sowas zu rstellen. Wie sehe ich den die Relationen(Joins ???).

Noch was anderes, ich muss meine IDs nochmals neu generieren. Wie kann ich den zwei Zahlenwerte verketten?

Beispiel:

ID = Konto + Monat

ID = 0123456789 + 201101

Gewünschtes Ergebnis:

ID = 0123456789201101  Frage: Passt 9999999999201101 noch in Bigint oder was soll ich verwenden? Die Führenden Nullen sind Vorraussetzung.

Danke. Thommy

Answer 13

Hallo Uwe,

 

das mit dem zusammenführen der Kontonr und des Monats hab ich nun selbst hinbekommen. Gibts nen Trick, wie ich mir so ein Insert-Into Script generieren lassen kann?

Danke für deine Hilfe.

Thommy

Answer 14

Hallo Uwe,

 

das mit dem zusammenführen der Kontonr und des Monats hab ich nun selbst hinbekommen. Gibts nen Trick, wie ich mir so ein Insert-Into Script generieren lassen kann?

Danke für deine Hilfe.

Thommy

Hallo Thommy,

viele Wege führen nach Rom - am einfachsten wäre, die ganze DB zu scripten und dann die Elemente zu kopieren, die den "INSERT" beinhalten. Eigentlich wollen wir aber hier keine echten Daten sehen (brauchen wir ja nicht!). Vielmehr müssen ja nur ein paar Spieldaten vorhanden sein, um die Probleme eingrenzen zu können.

Oder aber Du stellst grundsätzlich erst mal nur Spieldaten rein und postest dann das Script!.

Ich mache es immer so, daß ich das SQL-Statement selbst scripte. Das sind dann bei einer Tabelle mit der folgenden Struktur wie folgt aus:

Id | Vorname | Nachname | Geburtsdatum

SELECT 'INSERT INTO dbo.Test (Id, Vorname, Nachname, Geburtsdatum) VALUES (' +
[Id] + ', ' + QUOTENAME([Vorname], '''') + ', ' + QUOTENAME([Nachname], '''') + ', ' + QUOTENAME(CONVERT(varchar(8), [Geburtsdatum], 112), '''') + ')'

Das Ergebnis kannst Du dann als INSERT posten.

 

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 15

ID = 0123456789201101  Frage: Passt 9999999999201101 noch in Bigint oder was soll ich verwenden? Die Führenden Nullen sind Vorraussetzung.

Danke. Thommy

Hallo Tommy,

bisher hatte ich gedacht, diese "kombinierte ID" hätte einen tieferen Sinn, aber das scheint ja doch nicht der Fall zu sein?

Wenn die führenden Null zwingend benötigt werden und diese aus der Länge der Kontennummern nicht rekonstruiert werden kann, ist ein numerischer Datentyp nicht geeigenet bzw. Du darfst dann hier nicht die KontenNr vorne ran stellen.

Das DWH soll doch fürs Reporting dienen und da stellen sich ja immer die Fragen, die das Reporting beantworten sollen, z.B. wie ist der Kontenumsatz im Jahr 2011 oder wie lautet der Saldo eines bestimmten Kontos; wie willst Du diese Fragen unter Verwendung
der zusammengesetzte ID beantwort? Natürlich wird das gehen, aber sehr inperformant.

Wenn Du hier zwei separate Felder für JahrMonat und Konto verwendest, geht es wesentlich besser.

Ein DWH Design sollte sich am Reporting Bedarf orientieren, sonst macht es keinen Sinn.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 16

Hallo zusammen,

@Olaf:

Die kombinierte ID hat bisher ausshließlich den Sinn, den Primärschlüssel zu bilden. Die Kontonummer und Monat (201108) werden in separaten Spalten gespeichert, um diese nicht ständig aus der ID lösen zu müsen.Die Trennung in Jahr und Monat macht meiner Meinung nach keinen Sinn. Ich habe ein Schlüsselverzeichnis angelegt, wo ich das ggf. separiere (siehe nachfolgendes Script svzPM)

 

@Uwe:

Jetzt habe ich endlich die gewünschten Scripte generiert. Ich habe der einfachheit halber auf einen kleinen Teil an Spalten reduziert, da ich die anderen auch nicht unbedingt alle preisgeben möchte. Des weiteren habe ich 5 Testdatensätze ergänzt. Ich hoffe du kommst damit klar!

 

Grundkonto:

CREATE TABLE [dbo].[tbl_Grundkonto_test](
	[ID] [bigint] NOT NULL,
	[ID_Pers] [bigint] NOT NULL,
	[KONTONR] [float] NOT NULL,
	[SALDO] [float] NOT NULL,
	[ABSCHL_OE] [char](10) NOT NULL,
	[PM] [INT] Null,
 CONSTRAINT [PK_GK_BigInt_test] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tbl_Grundkonto_test ([ID],[ID_Pers],[KONTONR],[SALDO],[ABSCHL_OE],PM)   VALUES (2011080000000217,2011081286437022,217,19044.79,'1410000',201108)
INSERT INTO tbl_Grundkonto_test ([ID],[ID_Pers],[KONTONR],[SALDO],[ABSCHL_OE],PM)   VALUES (2011080000000324,2011089300000403,324,700139.8,'1010000',201108)
INSERT INTO tbl_Grundkonto_test ([ID],[ID_Pers],[KONTONR],[SALDO],[ABSCHL_OE],PM)   VALUES (2011080000000431,2011089400000434,431,100.0,'8010000',201108)
INSERT INTO tbl_Grundkonto_test ([ID],[ID_Pers],[KONTONR],[SALDO],[ABSCHL_OE],PM)   VALUES (2011080000000548,2011081592661025,548,135421.8,'1010000',201108)
INSERT INTO tbl_Grundkonto_test ([ID],[ID_Pers],[KONTONR],[SALDO],[ABSCHL_OE],PM)   VALUES (2011080000000655,2011089600000486,655,518216.5,'6550000',201108)

Detaildaten:

CREATE TABLE [dbo].[tbl_Detail_Giro_Test](
	[ID] [bigint] NOT NULL,
	[ZINSSATZ_SOLL] [float] NOT NULL,
	[AKT_HABEN_ZISA] [float] NOT NULL,
	[MODELL] [varchar](50) NULL,
	[PM] [int] NOT NULL,
 CONSTRAINT [PK_tbl_Detail_Giro_Test] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [tbl_Detail_Giro_Test] ([ID],[ZINSSATZ_SOLL],[AKT_HABEN_ZISA],[MODELL],[PM])  VALUES (2011080000000217, 12.09, 0.25, '', 201108 )
INSERT INTO [tbl_Detail_Giro_Test] ([ID],[ZINSSATZ_SOLL],[AKT_HABEN_ZISA],[MODELL],[PM])  VALUES (2011080000000324, 2.5, 1.35, '', 201108 )
INSERT INTO [tbl_Detail_Giro_Test] ([ID],[ZINSSATZ_SOLL],[AKT_HABEN_ZISA],[MODELL],[PM])  VALUES (2011080000000431, 12.09, 0.25, 'Gebührenfrei', 201108 )
INSERT INTO [tbl_Detail_Giro_Test] ([ID],[ZINSSATZ_SOLL],[AKT_HABEN_ZISA],[MODELL],[PM])  VALUES (2011080000000548, 12.09, 0.75, 'Gebührenfrei', 201108 )
INSERT INTO [tbl_Detail_Giro_Test] ([ID],[ZINSSATZ_SOLL],[AKT_HABEN_ZISA],[MODELL],[PM])  VALUES (2011080000000655, 2.5, 0.25, '', 201108 )

Personendaten:

CREATE TABLE [dbo].[tbl_Person_test](
	[ID_Pers] [bigint] NOT NULL,
	[Personen_NR][BigInt] Not Null,
	[ANREDE] [varchar](50) NULL,
	[NAME] [varchar](50) NULL,
	[VORNAME] [varchar](50) NULL,
	[BETR_OE] [char](8) NOT NULL,
	[GESCHLECHT] [char](1) NOT NULL,
	[KUNDENTYP] [char](3) NULL,
	[POSTLEITZAHL] [varchar](50) NULL,
	[ORT] [varchar](50) NULL,
	[PM] [int] Null,
 CONSTRAINT [PK_PE_BigInt_test] PRIMARY KEY CLUSTERED 
(
	[ID_Pers] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tbl_Person_test (   [ID_Pers],   [Personen_NR],   [ANREDE],   [NAME],   [VORNAME],   [BETR_OE],   [GESCHLECHT],   [KUNDENTYP],   [POSTLEITZAHL],   [ORT], [PM])   VALUES (2011081286437022, 1286437022,'Frau', 'Nachname1', 'Vorname1', '0000000', 'W', '001', '11111', 'Teststadt1',201108)
INSERT INTO tbl_Person_test (   [ID_Pers],   [Personen_NR],   [ANREDE],   [NAME],   [VORNAME],   [BETR_OE],   [GESCHLECHT],   [KUNDENTYP],   [POSTLEITZAHL],   [ORT], [PM])   VALUES (2011089300000403, 9300000403,'Frau', 'Nachname2', 'Vorname2', '0220000', 'W', '001', '22222', 'Teststadt2' ,201108)
INSERT INTO tbl_Person_test (   [ID_Pers],   [Personen_NR],   [ANREDE],   [NAME],   [VORNAME],   [BETR_OE],   [GESCHLECHT],   [KUNDENTYP],   [POSTLEITZAHL],   [ORT], [PM])   VALUES (2011089400000434, 9400000434,'Frau', 'Nachname3', 'Vorname3', '0220000', 'W', '001', '33333', 'Teststadt3',201108)
INSERT INTO tbl_Person_test (   [ID_Pers],   [Personen_NR],   [ANREDE],   [NAME],   [VORNAME],   [BETR_OE],   [GESCHLECHT],   [KUNDENTYP],   [POSTLEITZAHL],   [ORT], [PM])   VALUES (2011081592661025, 1592661025,'Herrn', 'Nachname4', 'Vorname4', '9999999', 'M', '001', '44444', 'Teststadt4' ,201108)
INSERT INTO tbl_Person_test (   [ID_Pers],   [Personen_NR],   [ANREDE],   [NAME],   [VORNAME],   [BETR_OE],   [GESCHLECHT],   [KUNDENTYP],   [POSTLEITZAHL],   [ORT], [PM])   VALUES (2011089600000486, 9600000486,'Herrn', 'Nachname5', 'Vorname5', '9999999', 'M', '001', '55555', 'Teststadt5' ,201108)

Monat des Users & SVZ-Monate

CREATE TABLE [dbo].[systbl_aktPM_Users](
	[PM] [int] NOT NULL,
	[sysUser] [varchar](50) NOT NULL
) ON [PRIMARY]

Insert Into dbo.systbl_aktPM_Users (PM,sysUser) VALUES (201108,'s0134220')

GO

CREATE TABLE [dbo].[tblsvz_PM_Zeitbezug](
	[PM] [int] NOT NULL,
	[PM_Bezeichnung] [nvarchar](50) NOT NULL,
	[PM_Bezeichnung_Jahr] [nvarchar](50) NOT NULL,
	[Jahr] [int] NOT NULL,
	[PM_MM] [char](2) NOT NULL,
	[PM_Stichtag] [datetime] NOT NULL,
	[PM_Vor] [int] NOT NULL
) ON [PRIMARY]

Insert Into dbo.tblsvz_PM_Zeitbezug (PM,PM_Bezeichnung,PM_Bezeichnung_Jahr, Jahr, PM_MM,PM_Stichtag,PM_Vor) 
                             VALUES (201108,'August','August 2011',2011,'08','2011-08-31',201107)

Verwendeter SQL-Befehl:

SELECT GK.KONTONR
     , DetGiro.MODELL
     , GK.ABSCHL_OE
     , GK.SALDO
     , PE.BETR_OE AS BETREUER
     , PE.NAME + ', ' + PE.VORNAME AS Kundenname
     , svzPM.PM_Stichtag AS Stichtag
     , PE.GESCHLECHT
     , PE.KUNDENTYP
     , PE.POSTLEITZAHL
     , PE.ORT
     , DetGiro.ZINSSATZ_SOLL
     , DetGiro.AKT_HABEN_ZISA
FROM   dbo.tbl_Person_test AS PE 
INNER JOIN dbo.tbl_Grundkonto_test AS GK ON PE.ID_Pers = GK.ID_Pers 
INNER JOIN dbo.tbl_Detail_Giro_Test AS DetGiro ON GK.ID = DetGiro.ID 
INNER JOIN dbo.systbl_aktPM_Users AS sysPM ON GK.PM = sysPM.PM AND sysPM.sysUser = SYSTEM_USER 
INNER JOIN dbo.tblsvz_PM_Zeitbezug AS svzPM ON sysPM.PM = svzPM.PM

Danke im Vorfeld für deine kleine Analyse.

MFG Thommy

 

 

Answer 17

Hallo Uwe,

 

konntest du mittlerweile etwas herausfinden?

 

Vielen Dank für eine AW.

 

Thommy1589