SQL-Server Performance

Question

Ich habe 4 Tabelle in meiner SQL-Server 2005 Datenbank, die ueber FKeys miteinander verbunden sind (Beziehungen 1:100). Die erste Tabelle hat im worst case 10000 Datensaetze. Die zweite hat dementsprechend 10000*100=10^6, die dritte 10^8 und die letzte 10^10 Datensaetze. Ich greife von meiner Applikation (vb.net) aus ueber TableAdapter (ado.net) auf die Datenbank zu und muss oftens JOIN-Anfragen (ueber alle vier Tabellen) stellen. Werde ich Probleme mit dem Datennbank-Performance haben? Soll ich Join-Anfragen bei dieser Ordnung meiden?

Danke und Gruesse    

Answer 1

Hi,

Ich habe 4 Tabelle in meiner SQL-Server 2005 Datenbank, die ueber FKeys miteinander verbunden sind (Beziehungen 1:100). Die erste Tabelle hat im worst case 10000 Datensaetze. Die zweite hat dementsprechend 10000*100=10^6, die dritte 10^8 und die letzte 10^10 Datensaetze.

Geht doch. Ist nicht wenig aber auch nicht sonderlich viel. Wichtiger wäre die Struktur selbst. Ist sie optimiert bzw. normalisiert, braucht es diese Anzahl an Detaildatensätzen oder lässt sich das über eine Anpassung der Struktur verbessern, ...? Wie viele Spalten welcher Art hältst du vor? ...?

Ich greife von meiner Applikation (vb.net) aus ueber TableAdapter (ado.net) auf die Datenbank zu und muss oftens JOIN-Anfragen (ueber alle vier Tabellen) stellen.

Warum? Nenne mal konkrete Beispiele, bei denen Du zwingend alle 4 Tabellen in einer einzelnen Abfrage miteinander verbinden musst.

Werde ich Probleme mit dem Datennbank-Performance haben?

Nein. Oder auch Ja. Das kann man so nicht beantworten. Eine optimierte DB und eine optimierte Anwendung kann man sicherlich auf einem "kleinen" System beherbergen. Wenn es dumm läuft, kannst du damit auch einen halben Cluster niedermähen :)

Soll ich Join-Anfragen bei dieser Ordnung meiden?

Nicht zwingend. Es kommt, wie schon gesagt, auf die genaue Ausgestaltung an. Vermeide alles, was nicht zwingend notwendig ist, es sei denn, es kostet noch mehr Resourcen und/oder Performance, das nicht zu tun.

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

ich hab' eine beliebige 10-stellige Zahl, die in vier Stufen unterteilt ist(ab-cd-ef-ghij). Jede Stufe enthaelt den value (zB die 4. Stufe den Value ghij, die 3. den ef) sowie andere Infos und kann nur existieren, wenn die vorige schon existiert (von links nach rechts gelesen). Daher die Realisierung in 4-Tabellen ueber FK-Verbindungen. In der ersten Tabelle steckt eine customerID als FKey auf Customer-Table, die bei der Anfrage vorkommt (->daher die Join-Bildung). Eine Such-Anfrage zB der 4. Tabelle laueft ueber eine Join-Bildung (ueber die FKeys) aller Tabellen mit Such-Kriteria (customerID=@id,ab=@ab,cd=@cd,ef=@ef,ghij=@ghij) und soll wenige Spalten anfoerdern. Und das soll sehr schnell gehen.

 (Eine andere Alternative waere die CustomerID auch in alle anderen Tabellen mitzuspeichern, sowie die values so zu speichern, so dass man Join-Anfragen umgehen kann. Also z.B. wenn ich die CustomerID in der vierten Tabelle mitspeichere (so wie auch den gasammten Value ab-cd-ef-ghij anstatt nur ghij), waere eine 'lokale' Anfrage bei der 4. Tabelle moeglich (ohne Join-Bildung)

Bei einer Join Bildung (Beziehungen 1:100,  worst case 10.000 Master Datensaetze der 1. Tabelle ) resultieren 10^4*10^6*10^8*10^11=10^29 Datensaetzen. Anders bei der 2. Alterrnative ohne Join-Bildung "nur" 10^11.    

 
danke und viele Gruess

manolis

Answer 3

Hallo Manolis,

Du solltest erst einmal normalisieren ...
So ein sprechender Schlüssel ist zwar nett für Menschen, aber eine Spaßbremse,
was das relationale Modell angeht:

Ich wage zu bezweifeln, dass Du den theoretischen Wertebereich ausschöpfen wirst.
Bei solchen Konstruktren bekommt man der Erfahrung nach früher das Problem,
dass der Schlüsselbereich für eine der Stufen vorzeitig erschöpft ist, wohingegen
in einem anderen reichlich Luft ist. (Und so etwas umzubauen, macht wirklich keinen Spaß).

Und aus dem Grunde solllte man solche Schlüssel vermeiden.

 

Gruß Elmar

Answer 4

Hi Elmar,

das Beispiel mit dem worst case ist einbisschen uebertrieben. Also die 10-stellige Zahlen sind einfach Kostenstelle eines Betriebes im Betrachtungsjahr aus dem Finanzbuchhaltungsbereich und koennen niemals den Schluesselbereich ausschoepfen. (Obwohl die Applikation viele Betriebe gleichzeitig mit im Betracht einzieht)

Ich denke die Datenbank ist normalisiert, ich habe keine Datenredudanz, also jeder Value  existiert nur einmal (bei einer Stufe und CustomerID=ID vom Betrieb im Betrachtungsjahr)  

 

Gruesse

Manolis

Answer 5

Hallo,

Was Kostenstellen angeht:
Idealerweise sind dort zwar die Nummerschemata vorgegeben.
Aber durch Änderungen ist die durch den Schlüssel implizierte Hierarchie
längst nicht immer von Dauer. Und man verläßt sich besser nicht darauf.
Was durch Auftrennen Nummernbereiche in separate Spalten geschehen sollte.

(Eine Erfahrung, die ich bei meinem allerersten Programm gemacht habe -
einer Kostenrechnung wenn auch schon lange her ;-):

Da Du Kunden erwähnst:
Die wechseln Geschäftsfelder, werden aufgekauft, fusioniert...
und schwupps wechseln sie die Kostenstelle.

Gruß Elmar

Answer 6

Hallo,

Es gibt bei meinem Fall keine Aenderungen solcher Art . Eigentlich ist keine klare Kostenrechnung, sondern finanzbuchhalterische Betrachtung der jaehrlichen Bilanzkonten eines Geschaeftes mit einer bestimmten Rechtsordnung (im griechischen Raum)

Danke

Manolis  

Answer 7

Hallo Manolis,

und wer hat Dir das gesagt: Ein griechischer Buchhalter, der Chef?
(Und nein: Ich spiele damit nicht auf die griechischen Finanzverhältnisse an ;-)

Mir wurde ähnliches und wird auch heute immer mal wieder versichert,
dass das in Stein gemeisselt, Bronze gegossen wäre usw...
Am Ende kommt es doch schneller wieder zu solchen Änderungenals man denkt.

Entwerfe die Datenbank am Anfang so, dass sie selbst solche Änderungen verkraften kann.
Da solche Datenbanken die typischen Vertreter für Analysis Services und Co. sind,
würde ich dort mit Surrogaten (SQL Server: Identity) arbeiten
und  den Kostenstellenplan darüber abbilden.
(So hat man evtl. weitere Anwendungsmöglichkeiten gleich mit abgedeckt
und könnte sie als Faktentabelle für SSAS einbinden).

Die vierstellige Gliederung kann man entweder in 4 Spalten abbilden -
wenn der Plan schon einige Jahre überlebt hat.
Oder aber gleich eine Hierarchie aufbauen, die man zur Laufzeit expandiert.

Was die in der Eingangsfrage genannten Worst Case Zahlen angeht:
Die Zahl dürfte bei solchen Kostenrechnungen am Ende überschaubar bleiben.

Frage nach der Zahl der Kunden und der durchschnittlichen Wachstumsrate
der letzten drei Jahre und rechne 50 % Aufschlag drauf.

Gruß Elmar

Answer 8

Hallo Elmar,

Das Programm ist bereits fertiggestellt und beim Kunden installiert, also  kann ich nicht umfangreiche Aenderungen in der Struktur vornehmen. Ich mache mir bloss  um zukuenftlche Updates Gedanken ,die den Usern bei Programminteraktion behiflisch sein koennen.

 

Gruesse

Answer 9

Hallo Manolis,

naja, über die Performance (bzw. das Layout dafür) sollte man sich eigentlich
vorher Gedanken machen.

Aber wenn die Datenbank bereits installiert hast, solltest Du auch die Datenlage
ansatzweise kennen.
Über Mitschnitte der Abfragen via SQL Server Profiler oder serverseitigen Trace
kannst Du die Schwachstellen (wie langsame Abfragen, Blockierungen)  erkennen.

Und bis zu einem gewissen Grad über Indizes, umschreiben von Abfragen,
darauf reagieren.

Gruß Elmar

 

Answer 10

Hi Elmar,

also die Datenlage war mir von Anfang an wohl bekannnt und dementsptrechend wurde das Datenbankschema konzipiert. Ob nun SQL-Server bei relativ groesseren Sql-Join-Anfragen haengend bleibt waere ein Problem, das damit nicht gerechnet hatte.    

 

Gruesse

Manolis

Answer 11

Hallo Manolis,

wenn Du Tabellenaufbau, Datenvolumen und die fraglichen Abfragen postest,
könnte man mehr dazu sagen.

Da Du bisher nur Allgemeinplätze - und so waren auch meine Antworten nicht mehr)
bisher keine Informationen geliefert hast, anhand der man fundierte Aussagen
machen kann - ziehe ich mich bis Du dazu bereit bist, aus dem Thread zurück.

Gruß Elmar

Answer 12

Hi Elmar,

1Tabelle(kwd, value(XX->string(2)), FK(customerID->Customer.kwd),...),

2Tabelle(kwd, value(xx->string(2)), FK(1tabID->1Tabelle.kwd),...)

3Tabelle(kwd, value(xx->string(2)), FK(2tabID->2Tabelle.kwd),...),

4Tabelle(kwd(bigint), value(xxxx->string(4)), FK(3tabID->3Tabelle.kwd),...) 

alle Beziehungen seien  (1:100)

value- Attribute sind indexiert (sowie die FKeys)

Datenvolumen bei der 1 Tabelle 1000 Datensaetze-> dh bei der Beziehung (1:100) habe ich 10^5 Datensaetze (worst case) in der 2. Tabelle usw. 

Die Select-Anfrage->Join ANfrage ueber alle 4 Tabellen:

SELECT 4tabelle.value,<andere Columns>, FROM 4Tabelle INNER JOIN 3Tabelle ON 4Tabelle.3tabID = 3Tabelle.kwd ...usw...INNER JOIN 1Tabelle ON 2Tabelle.1tabID=1Tabelle.kwd) WHERE (1Tabelle.customerID=@id,1Tabelle.value=@xx,2Tabelle.value=@xx,3Tabelle.value=@xx,4Tabelle.value=@xxxx)

Gruesse

Answer 13

Ich sehe jetzt keine Definition von Primary Keys, weiterhin würde ich auch zusammengesetzte Indizes aus Join-Feld + Where-Bedingung aufbauen.
Setze weitere Felder mit INCLUDE dazu, wenn es das Datenvolumen zuläßt. Definiere die Indizes als Unique, wenn möglich.

Welcher Key ist geclustered?

Einen schönen Tag noch,
Christoph

---

Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu

Answer 14

Hi Christoph,

bin leider kein SQL-Server Experter...

also die kwd's sind die primary Keys. Die Indizes sind wie gesagt die  Columns "value" und die FKeys 'XtabID' bei jeder Tabelle und koennen leider nicht Unique sein.

Das Tupel (value, XtabID) sei unique ABER nicht SQL-Serverseitig sondern Anwendugsprogrammseitig. Ehhrlich gesagt weiss ich nicht wie man bei SQL-server 2005 ein Tupel von Columns als unique definiert.

Die Sache mit INCLUDE  und CLUSTER-Keys ist mir neu...wie gesagt bin kein experter

Danke

Manolis

Answer 15

Hi Manolis,
zu INCLUDE und CLUSTERED schaust Du am besten mal in der Doku nach. Hier in Kürze die Details:
CLUSTERED bewirkt die physische Sortierung der Tabelle nach diesen Schlüsseln. Beispiel: Telefonbuch nach Nachname, Vorname sortiert
In der Regel erstellt der SQL Server den Primary Key als clustered, was in der Regel auch Sinn macht.Der Clustered Index enthält also alle Spalten, da er quasi die Tabelle selbst ist.
INCLUDE gibt an, welche Spalten auch im (nicht clustered) Index enthalten sein sollen, ohne selber indiziert zu sein. Du benötigst also keinen weiteren Zugriff auf die Tabelle um an die Daten dieser Spalten zu kommen. Nachteil: Overhead für Performance und Platz, da Daten redundant gehalten werden und aktualisiert werden müssen.

Dein Statement:

SELECT 4tabelle.value,<andere Columns>, FROM 4Tabelle INNER JOIN 3Tabelle ON
4Tabelle.3tabID = 3Tabelle.kwd ...usw...INNER JOIN 1Tabelle ON
2Tabelle.1tabID=1Tabelle.kwd) WHERE
(1Tabelle.customerID=@id,1Tabelle.value=@xx,2Tabelle.value=@xx,3Tabelle.value=@xx,4Tabelle.value=@xxxx)

An Indizes würde ich also folgendes vorschlagen:

Create UNIQUE Index ix1Tabelle on 1Tabelle (customerID, value, kwd); -- kwd
ist ja bereits unique, deshalb sollte es gehen
Create UNIQUE Index ix2Tabelle on 2Tabelle (1tabID, value, kwd); -- kwd ist
ja bereits unique, deshalb sollte es gehen
Create UNIQUE Index ix3Tabelle on 3Tabelle (2tabID, value, kwd); -- kwd ist
ja bereits unique, deshalb sollte es gehen
Create UNIQUE Index ix4Tabelle on 4Tabelle (3tabID, value, kwd); -- kwd ist
ja bereits unique, deshalb sollte es gehen

Einen schönen Tag noch,
Christoph

---

Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu

Answer 16

Hallo Christoph,

danke fuer die Antwort

Hab die Indizies generieren lassen und hoffe...die Suche wird effektiver...

waere aber nicht sinnvoller die kwd's auszulassen, also nur  Create UNIQUE Index ix1Tabelle on 1Tabelle (customerID, value), da die Tupel(customerID, value),...(1tabID, value) nur einmal vorkommen duerfen? (das erreiche ich Anwendungsprogrammseitig, da ich mit Check Constraits dieser Art bei SQL Server 2005 noch nicht vertraut bin)

Gruesse

Manolis

  

 

Answer 17

Hallo Manolis,
mir war aus Deiner Beschreibung nicht klar geworden, ob Du permanent sicherstellen kannst, dass die Daten unique sind, oder dies nur am Ende einer Transaktion sicherstellst.

Die kwd wird sowieso im Index gespeichert, da es der Primary Key ist und hiermit der Satz im clustered Index gefunden wird. Die Reduzierung auf die beiden Felder (customerID, value) erhöht natürlich die Schlagkraft dieses Index, da Du jetzt auch die Eindeutigkeit dieses Tupels garantierst.

Einen schönen Tag noch,
Christoph

---

Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu

Answer 18

Hallo Christoph,

ich stelle am Ende der Insert-Transaktion fest, dass das Tupel unique ist. Die Datenbank weisst also nichts darueber. Wird die Reduzierung auf die beiden Felder  (customerID, value) das alles serverseitig machen (also ohne extra Constraint-Bedingung einzubauen),  oder muss ich das weiterhin anwendungsprogammseitig ueberpruefen?   

 Gruesse

Manolis

Answer 19

Hallo Manolis,
wenn Du einen UNIQUE Index über die beiden Felder anlegst, werden zu keinem Zeitpunkt nicht unique-Kombinationen in die Spalten geschrieben werden können. Es wird also permanent geprüft und nicht erst am Ende der Transaktion.

Du solltest in Deiner Anwendung also sicherstellen, nur Unique-Werte zu schreiben oder entsprechende Fehlermeldungen abfangen können.

Einen schönen Tag noch,
Christoph

---

Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu

Answer 20

Danke Christoph