View beschleunigen/Alternative zu View

Question

Hallo,

ich habe eine Tabelle (Organisationseinheit), die sich auf sich selbst bezieht (N:1):
Zu einer Organisationseinheit gehört immer genau eine Zweigstelle, die auch eine Organisationseinheit ist (Zweigstellen haben sich selbst als Zweigstelle).

Leider gibt es dafür aber keine FK-Beziehung, sondern das ganze läuft über die letzten beiden Stellen der Organisationseinheits-Nummer (Zeichenkette).

U.a. da das Entity Framework damit nicht umgehen kann (habe ich hier schon mal beschrieben), haben wir dafür ein View eingerichtet:

CREATE VIEW [dbo].[vORGANISATIONSEINHEIT_ZWEIGSTELLE]
AS
SELECT DISTINCT
      oe.ID AS OrganisationseinheitID,
      zwst.ID AS ZweigstelleID
FROM
      ORGANISATIONSEINHEIT zwst,
      ORGANISATIONSEINHEIT oe,
      ORG_TYPE ot
WHERE 
      zwst.OrgType = ot.OrgTypeID
      AND ot.OrgTypeName = 'Zweigstelle'
      AND RIGHT(oe.Nr, 2) = zwst.Nr

Der zusätzliche Join mit der ORG_TYPE-Tabelle ist dabei übrigens eigentlich nicht unbedingt notwendig, aber der Kunde wollte ihn gern noch drin haben...

Jedenfalls funktioniert das ganze jetzt zwar, ist jedoch furchtbar lahm.
Gibt es eine einfache Möglichkeit, das ganze zu beschleunigen?

Ansonsten müssten wir wohl einen Trigger auf die ORGANISATIONSEINHEIT-Tabelle legen, aber das fände ich auch nicht so super...

Korrekte FK-Beziehungen pflegen kann/will der Kunde nicht...

Danke für alle Tipps!

Viele Grüße

Marvin

---

Hypnose Berlin

Answer 1

Hallo Marvin,

ohne DDL-Script kann man überhaupt nichts sagen. Welche Indexe sind gesetzt? Wie sind die Indexe aufgebaut? Wieviele Daten sind in den Relationen. Wie schaut der ExecutionPlan aus?

Zwei Punkte kann ich nur sehen, der auf jeden Fall die Abfrage optimieren würden:.

Statt RIGHT (oeNr, 2) = zwst.Nr (ist nicht SARGable) verwende

oe.Nr LIKE zwst.Nr  + '%'

Verwende voll qualifizierte Objektnamen in Abfragen!

Weitere Informationen zu SARGable Argumenten, Indexseek vs. IndexScan, IO und Executionplänen findest Du in meinem Blog:

Qualifizierte Objekte: http://db-berater.blogspot.de/2012/11/auswirkung-von-vollstandig.html
Indexoptimierung (Teil 1 - 5): http://db-berater.blogspot.de/2012/11/optimierung-von-datenbankmodellen.html

---

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
SQL Server Blog (german only)

Answer 2

Hallo Uwe,

vielen Dank für die schnelle Antwort.

Das mit den voll qualifizierten Objektnamen war mir nicht bewusst (bin mehr Entwickler als DB-Mensch), das wird also auch für die Views nicht fest gespeichert?
In den Abfragen sonst sind die Objektnamen ziemlich sicher voll qualifiziert, da sie vom Entity Framework generiert werden.

Dass LIKE schneller sein soll, habe ich schon ein paar Mal gelesen - hier habe ich mich aber gefragt, was passiert, wenn "Nr" selbst Zeichen wie "%" enthält (darf eigentlich nicht sein, aber kann ja theoretisch vorkommen - wäre dann wohl eine Art SQL Injection?). Außerdem geht es um die letzten zwei Stellen - das wird normalerweise auch so passen, aber der Kunde möchte hier eigentlich schon gern im View fest verdrahten, dass es um zwei Stellen geht. Eine Zweigstelle mit einer einstelligen Nummer wäre ein Pflegefehler.
Wenn es performancemäßig aber sehr viel ausmacht, könnte ich den Kunden zu beiden Punkten ggf. auch umstimmen.

Ich glaube, es sind so ungefähr gar keine Indizes (nur PK-Constraint auf die ID) für die relevanten Spalten vorhanden (wenngleich die Tabelle recht klein ist), vermutlich wird das schon viel ausmachen...

Hier mal das CREATE-Skript für ORGANISATIONSEINHEIT:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ORGANISATIONSEINHEIT](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Nr] [nvarchar](5) NOT NULL,
	[NrExtern] [nvarchar](5) NULL,
	[Name] [nvarchar](50) NOT NULL,
	[OrgType] [tinyint] NOT NULL,
	[Status] [bit] NOT NULL,
	[Parent] [int] NULL,
	[Attestgruppe] [nvarchar](5) NULL,
 CONSTRAINT [PK_Organisasjonsenhet] 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]

GO

-- Jede Menge "EXEC sys.sp_addextendedproperty" für MS_AggregateType, MS_ColumnHidden etc. weggelassen

ALTER TABLE [dbo].[ORGANISATIONSEINHEIT]  WITH CHECK ADD  CONSTRAINT [FK_ORGANISATIONSEINHEIT_ORGTYPE] FOREIGN KEY([OrgType])
REFERENCES [dbo].[ORG_TYPE] ([OrgTypeID])
GO

ALTER TABLE [dbo].[ORGANISATIONSEINHEIT] CHECK CONSTRAINT [FK_ORGANISATIONSEINHEIT_ORGTYPE]
GO

ALTER TABLE [dbo].[ORGANISATIONSEINHEIT] ADD  CONSTRAINT [DF_ORGANISATIONSEINHEIT_Status]  DEFAULT ((1)) FOR [Status]
GO

Und für ORG_TYPE:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ORG_TYPE](
	[OrgTypeID] [tinyint] IDENTITY(1,1) NOT NULL,
	[OrgTypeExtern] [nchar](3) NULL,
	[OrgTypeNavn] [nvarchar](50) NULL,
 CONSTRAINT [PK_EINHEITSTYP] PRIMARY KEY CLUSTERED 
(
	[OrgTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Danke!

Viele Grüße
Marvin

---

Hypnose Berlin

Answer 3

Hallo Marvin

Wenn es um die Geschwindigkeit einer Abfrage geht, ist ein Ausführungsplan immer sehr hilfreich!

Ohne diesen zu kennen, möchte ich Dir folgendes vorschlagen:

- optimiere den Verbund zwischen Organsisationseinheit und Org_Typ,indem Du einen Index auf Organsisationseinheit(OrgTyp) legst (im Ausführungsplan wird hierdurch ggf. ein zeitaufwendiger TableScan verhindert und ein Indexseek ausgeführt)

CREATE INDEX IDX_OrgTyp
ON dbo.ORGANISATIONSEINHEIT(OrgType)

- um den Zugriff auf Org_Typ zum Filtern von Zweigstelle zu verbessern, kannst Du einen Index aufOrg_Typ(OrgTypeName) legen (hier würde ich mit einem Test prüfen, ob ein spürbarer Gewinn erzielt wird)

CREATE INDEX IDX_OrgTypeName
ON dbo.ORG_TYPE(OrgTypeName)

- durch ein Hinzufügen einer berechneten Spalte für die ersten zwei Stellen vonNr an Organsisationseinheit und einer anschließenden Indexierung wird vermutlich der größte zeitliche Gewinn erzielt werden

ALTER TABLE dbo.ORGANISATIONSEINHEIT
 ADD NrZweistellig AS RIGHT(2, Nr)

CREATE INDEX IDX_NrZweistellig
 ON ORGANISATIONSEINHEIT(NrZweistellig)

Sei aber immer möglichst zurückhaltend bei der Neuanlage von Indexen, da sie Speicherplatz benötigen und einen Einfluss auf INSERT-, UPDATE- und DELETE-Operationen haben.

Ich hoffe ich konnte Dir ein wenig weiterhelfen.

Mit freundlichem Gruß

Alexander

Answer 4

Hallo Marvin,

genau da, wo Du dem Kunden (aus welchen Gründen auch immer) nachgibst, wird er Dir später einen Strick draus drehen, weil das System nicht performant läuft. Du sagst Du bist Entwickler - dass, was wir hier gerade machen, ist zu 100% Entwicklung. Es ist IMHO sehr wichtig, bereits im Vorfeld das Datenmodell sehr sorgfältig zu planen.

Für das von Dir geschilderte Problem würde ich eine "Computed Column" integrieren und dieses Attribut indizieren. Dann kannst Du statt einer nicht SARGable Funktion das Attribut in der Abfrage verwenden. Mit dem nachfolgenden Script kannst Du den Unterschied erkennen.

1. Erstellung der Relation wie von Dir beschrieben (BITTE IN TESTDATENBANK AUSFÜHREN!)

DROP TABLE dbo.OrganisationsEinheit
GO
CREATE TABLE [dbo].[ORGANISATIONSEINHEIT]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Nr] [varchar](5) NOT NULL,
	[NrExtern] [varchar](5) NULL,
	[Name] [varchar](50) NOT NULL,
	[OrgType] [tinyint] NOT NULL,
	[Status] [tinyint] NOT NULL,
	[Parent] [int] NULL,
	[Attestgruppe] [varchar](5) NULL,
	[NrCalc] AS RIGHT (Nr, 2),
	CONSTRAINT [PK_Organisasjonsenhet] PRIMARY KEY CLUSTERED ([ID])
);
-- testdaten
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB023', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('ZE024', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB050', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB123', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB033', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB023', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('ZE024', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB050', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB123', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB033', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB023', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('ZE024', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB050', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB123', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB033', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB023', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('ZE024', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB050', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB123', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB033', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB023', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('ZE024', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB050', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB123', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB033', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB023', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('ZE024', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB050', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB123', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
INSERT INTO dbo.OrganisationsEinheit
(Nr, NrExtern, Name, OrgType, Status, Parent, AttestGruppe)
VALUES
('AB033', 'KA', 'Uwe Ricken', 1, 1, NULL, 'Att01')
GO

Ich habe übrigens auch die Datentypen Deines Modells geändert! Ein nvarchar belegt immer zwei Bytes und wenn Du nicht kyrillische, arabische, chinesische Zeichen verwendest, reicht ein varchar, char Datentyp (1 Byte / Zeichen)

2. Analyse von Abfrage

Zunächst lassen wir die Abfrage auf das kalkulierte Attribut in der momentanen Struktur ausführen.

SELECT * FROM dbo.OrganisationsEinheit WHERE NrCalc = '23';

Der dazugehörige Ausführungsplan (den kannst Du mit CTRL+M vor der Ausführung aktivieren).

Und nun wird im Anschuß auf das Attribut [NrCalc] ein Index gesetzt und die gleiche Abfrage erneut ausgeführt.

-- Nun noch einen Index auf die kalkulierte Spalte
CREATE INDEX ix_organisationseinheit_NrCalc ON dbo.OrganisationsEinheit (NrCalc)
INCLUDE ([Nr], [NrExtern], [Name], [OrgType], [Status], [Parent], [Attestgruppe])

Das Ergebnis der Abfrage (ExecutionPlan) sieht dann wie folgt aus:

Das Ergebnis ist nun deutlich performanter, da nun ein Indexseek verwendet wird. Ich habe die Attribute des Clustered Index zusätzlich mittels INCLUDE im Index aufgenommen, da ich somit einen Bookmark Lookup oder Indexscan verhindere.

Indexe sind nicht einfach aber wenn man das Prinzip verstanden hat, bringt die richtige Implementierung SEHR HOHE Performanceverbesserungen. Das Problem in Deinem System scheint mir aktuell, dass die Performance zwar ausreichend ist aber ...

bedenke, dass das Datenvolumen zunehmen wird!

Ich kann nur den guten Rat geben, sich intensiver mit dieser Thematik (und das besonders als Entwickler) auseinander zu setzen! Ich habe ein paar Artikel in meinem Blog zu diesem Thema verfaßt. Ist schnell zu lesen und hilft sicherlich sehr.

---

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
SQL Server Blog (german only)

Answer 5

Hallo Marvin

Wie Du aus den Beiträgen von Herrn Ricken und mir entnehmen kannst, steckt das größte Potential in der berechneten Spalte. Herr Rickens Vorschlag für eine Indexierung (Index auf die berechnete Spalte, wobei die für die Sicht relevanten Attribute mit INLUDE aufgenommen sind) ist allerdings besser als mein Vorschlag, da kein LookUp mehr nötig ist!

@Herr Ricken: Sehr guter, detaillierter Beitrag!

Mit freundlichem Gruß

 Alexander

Answer 6

@Herr Ricken: Sehr guter, detaillierter Beitrag! Alexander

Hallo Alexander,

danke für die Blumen ;)
Hier im Forum duzt man sich. Finde ich auch besser, dass läßt mich  dann auch etwas jünger erscheinen :)

---

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
SQL Server Blog (german only)

Answer 7

Hallo,

vielen Dank für die Antworten an alle, insbesondere für die ausführliche Antwort von Uwe!

Ich werde das mal ein bisschen ausprobieren und untersuchen und melde mich dann wieder.
Spontan scheinen die Indizes bei mir lokal kaum einen Unterschied zu machen (allerdings ist das auch ein Testsystem, auf der Live-DB sind ein paar hundert Benutzer).

Evtl. ist das Problem auch noch komplexer - denn das vom EF generierte Statement für das Laden von Aufträgen ist ein absolutes Monster.
Das ist es jedoch auch ohne das View... Dann aber ist die Performance ok...

Aber wie gesagt, ich untersuche das näher und melde mich dann wieder.

Vielen Dank!!

Viele Grüße
Marvin

---

Hypnose Berlin

Answer 8

Also - inzwischen bin ich etwas skeptisch, ob es denn wirklich an dem View liegt, oder ob nicht das Statement an sich das Problematische ist und das View nur der Tropfen ist, der das Fass zum Überlaufen (=Webservice-Timeout) bringt.

Das EF generiert halt ein Monster-Statement mit zig Unions, u.a. mit dem View.
Lokal lädt er dann knapp 7.000 Datensätze in ca. 2 Sekunden - allerdings unabhängig davon, ob ich nun die Indizes hinzugefügt habe oder nicht.

Wenn ich mir den Execution-Plan für das Monster-Statement anzeigen lasse, sind leider fast alle Schritte mit 0% angegeben - nur mal zwischendurch 3-4% für "Clustered Index Scan" oder "Hash Match", wo irgendwelche PK-Spalten durchsucht werden...

Ich denke mal, es würde zu weit führen, jetzt das ganze Datenmodell und Monster-Statement hier zu posten, aber wie kann ich denn selbst am besten prüfen, wo noch Optimierungspotential steckt? Das Statement wird ja leider vom EF generiert, da kann ich also nur begrenzt eingreifen...
Es ist so komplex, weil praktisch alles schon "Include"t ist, um die "Select N+1"-Problematik zu umgehen...

---

Hypnose Berlin

Answer 9

Hallo Marvin,

"Optimierungsbedarf" ist ein vielfältiger Begriff... :)

Ich würde zunächst einmal so vorgehen, daß ich mir die IO-Statistiken anschaue.

Dazu nimmst Du einfach das "Monster-Statement" und in SSMS setzt Du VOR der Ausführung den Befehl
SET STATISTICS IO ON;

Damit hast Du schon einmal einen Überblick, in welchen Relationen wieviele READS erzeugt werden. Im Anschluß würde ich mir das Statement heraussuchen, dass den meisten IO verursacht.

Ein weiterer Punkt für die Optimierung ist die Suche nach SCAN anstelle von SEEK-Statements.
Weiterhin wichtig, zu wissen, wie das Histogramm einzelner Indexe ausschaut. Dies ist z. B. wichtig, um festzustellen, ob Parameter Sniffing das System ausbremst.

Mit DBCC SHOW_STATISTICS kannst Du dir alle Informationen zu den Statistiken eines Index ausgeben lassen.
Das sind - für den ersten Versuch - wahnsinnig viele Informationen und Bezugsquellen aber für eine vernünftige Analyse wirst Du nicht umhin kommen, Dich damit auseinander zu setzen.

Eine andere Alternative wäre, für das Objekt im EF nicht die View zu nehmen sondern eine Prozdur. In dieser Prozedur kannst Du dann ev. die Datenstruktur verbessern, indem Du z. B. mit temporären Tabellen arbeitest um bereits im Vorfeld Selektionen vorzunehmen !

---

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
SQL Server Blog (german only)

Answer 10

Hallo Uwe,

noch mal danke für die ausführliche Antwort.

Das sieht dann ungefähr so aus:

(6849 row(s) affected)

Table 'KATEGORIE'. Scan count 0, logical reads 862, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AUFTRAG_KATEGORIE'. Scan count 100, logical reads 231, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WEBAPPLICATION'. Scan count 3, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RECHNUNGSSPEZ'. Scan count 3, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PREIS_AUSLAGEN'. Scan count 3, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GESAMTPREIS_TYP'. Scan count 3, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BUCHFUEHRUNG'. Scan count 3, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 6, logical reads 4512, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ORGANISATIONSEINHEIT'. Scan count 168, logical reads 390, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ORG_TYPE'. Scan count 6, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PERSON'. Scan count 0, logical reads 645, physical reads 0, read-ahead reads 0, lob logical reads 85717, lob physical reads 0, lob read-ahead reads 17916.
Table 'GEO_GEMEINDE'. Scan count 0, logical reads 516, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GEO_BUNDESLAND'. Scan count 0, logical reads 534, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GEO_REGION'. Scan count 3, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'REFERENZPROJEKT'. Scan count 3, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AUFTRAG'. Scan count 3, logical reads 7071, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KUNDE_KONTAKT'. Scan count 1, logical reads 159, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KUNDE'. Scan count 0, logical reads 321, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AUFTRAG_KUNDE'. Scan count 100, logical reads 230, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AUFTRAG_VERZEICHNIS'. Scan count 1, logical reads 599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Es sind ja nun auch wirklich sehr viele Aufträge (Tabelle AUFTRAG), die hier geladen werden.
Woran erkenne ich, welche Lesevorgänge unumgänglich und welche evtl. überflüssig sind?

Evtl. kann ich auch das LINQ-Statement anders aufbauen, um performanteres SQL generiert zu bekommen?

Ich glaube, eine SP würde ich dann eher nicht nehmen, sondern eher einen Trigger auf ORGANISATIONSEINHEIT (und ggf. ORG_TYPE) - letztlich sind Änderungen dort ja sehr selten (wann wird schon mal eine neue Organisationseinheit angelegt...), und dann hätte man eine richtige FK-Beziehung...

Aber momentan bin ich, wie gesagt, ohnehin skeptisch, ob es denn nun an dem View hängt.
Alles, was wir aktuell sicher wissen, ist, dass der Webservice mit Hinzufügen des Views trotz deutlich erhöhter Timeout-Werte immer noch zu lange für eine Antwort braucht... ohne das View geht es problemlos.

Viele Grüße

Marvin

---

Hypnose Berlin

Answer 11

Hallo Marvin,

mir fallen zwei Besonderheiten "auf dem ersten Blick" auf:

Auftrag_Kategorie hat einen SCAN_Count von 100!
Auftrag wird drei Mal gescant bei einem Volumen von ~7.000 reads = 54MB

Allgemein kann man aber ohne die Abfrage selbst und das Datenmodell nicht wirklich was sagen.
Hier ist m. E. "Feinarbeit" angesagt und man muß sich schrittweise den Problemen nähern.

Über den IO weiß man nun, welche Relationen involviert sind.
Der nächste Schritt besteht darin, die Relationen zunächst einzeln und dann in Verbindung mit ihren JOINS zu testen.

Das ist zwar mühselig aber wenn man die notwendige Zeit investiert, wird das System sicherlich performanter als es aktuell ist.
Wie bereits vorher erwähnt - eine Modularisierung ist m. E. der erste Schritt zu einer performanten Abfrage (Stored Procedure)

---

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
SQL Server Blog (german only)

Answer 12

Hallo Uwe,

danke!

Einige neue Erkenntnisse hier:
Zunächst war das Statement, das ich analysiert hatte, nicht das für den Webservice relevante (sorry) - das ist aber auch zu lahm, insofern nicht wirklich falsch...

Dann habe ich mir endlich die "richtige" Methode des Webservices im SQL Server Profiler angeguckt:
Es gibt eine Tabelle "Teilnehmer" für die M:N-Zuordnung von Personen zu Aufträgen.
Geladen werden sollen alle aktiven Aufträge (AUFTRAG.STATUS), bei denen ein bestimmter Benutzer (PERSON) aktuell (TEILNEHMER.STATUS) Teilnehmer ist.

Zunächst fiel mir auf, dass das LINQ-Statement (nicht von mir) nicht das machte, was es sollte:

var auftraege =
                (from m in _context.MITARBEITERs
                 where m.PersonID == personId &&
                       m.Status == (int) ActiveStatus.Active &&
                       m.AUFTRAG.Status == (byte) AuftragStatus.Active
                 select m.AUFTRAG)
                 .Distinct();

Es ging beim Distinct() ja darum, dass eine PERSON zu einem AUFTRAG mehrfach in der MITARBEITER-Tabelle stehen kann (Mitgliedschaft in verschiedenen Rollen). Dazu sollte der Auftrag natürlich trotzdem nur einmal geladen werden.
Im generierten SQL wurden jedoch die Aufträge selbst per DISTINCT (also quasi "SELECT DISTINCT * FROM AUFTRAG") verglichen!

Ich habe daher das Statement erst mal angepasst:

var auftraege =
                    (from o in _context.Auftrags
                    where
                        (from m in _context.MITARBEITERs
                         where m.PersonID == personId &&
                               m.Status == (int) ActiveStatus.Active
                         select m.AuftragID).Distinct()
                    .Contains(o.ID)
                    && o.Status == (byte) OppdragStatus.Active
                    select o);

Damit sieht es schon mal besser aus... Ich weiß aber nicht, ob das wirklich eine gute Lösung ist.
So z.B. scheint es ja auch zu gehen (generiert ein "TOP(1)", ich weiß nicht, ob das besser ist?:

var auftraege =
                   (from m in _context.MITARBEITERs
                    where m.PersonID == personId &&
                           m.Status == (int) ActiveStatus.Active &&
                           m.AUFTRAG.Status == (byte) OppdragStatus.Active
                     group m by m.AuftragID
                     into grp
                     select grp.First().AUFTRAG);

Jedenfalls sind es dann aber immer noch seeeeeeeehr viele weitere Abfragen (SELECT N+1):

Wir haben Lazy-Loading aktiviert und an einem AUFTRAG können jede Menge Daten hängen.
Nun ist die Persistenz-Schicht aber auch noch ordentlich getrennt, wie es sich gehört, sodass wir die generierten EF-Objekte nicht direkt nutzen, sondern alles automatisch (AutoMapper) in Datenobjekte umkopieren.
Damit wird also immer auf ALLE Properties zugegriffen und folglich alles per Lazy-Loading nachgeladen...
An einigen Stellen ist das definitiv unsinnig - dort müssen wir dann unsere Datenobjekte anpassen, so dass nicht alles kopiert wird, klar.
Aber an anderen Stellen (z.B. dort, was ich zuerst gepostet habe, das gehört zum Laden der Auftragsliste) werden wirklich fast alle Auftragseigenschaften aus anderen Tabellen (Kunde(n), Ansprechpartner, Bundesland, Region, Verantwortlicher).

Hier kommt dann auch das View ins Spiel: Jede PERSON (referenziert als MITARBEITER.PERSON und AUFTRAG.PERSON) hat eine ORGANISATIONSEINHEIT, die eine übergeordnete Einheit (ORGANISATIONSEINHEIT.ParentID) hat - und beiden ist über das View vORGANISATIONSEINHEIT_ZWEIGSTELLE eine Zweigstelle zugeordnet (via EF als ORGANISATIONSEINHEIT.Zweigstelle gemappt). Hier wird dann also zigfach auf das View zugegriffen...
Das gleiche passiert übrigens bei jedem Zugriff auf die Webanwendung, da für die Authentifizierung immer das PERSON-Objekt geladen wird und beim Umkopieren eben auch wieder auf alle Properties zugegriffen wird...

Ich habe dann erst mal für den Webservice versucht, das Nachladen zu unterbinden, indem ich praktisch alles per Include() gleich habe mitladen lassen - daraus ergab sich dann so ein Monster-Statement mit zig UNIONs wie schon bei der Auftragsliste (das, was ich vorher gepostet hatte).
Das Resultset enthält natürlich viel doppelte Werte, das ist dann wohl doch nicht so sinnvoll... Aber sinnvoll ist ja sicherlich auch nicht, wenn er mehrere hundert Statements absetzt...

Nach Analyse des Statements mittels "SET STATISTICS IO ON;" teilte mir das Management Studio aber erst mal panisch mit, dass Indizes auf FKs fehlen - das ist natürlich eine mittlere Katastrophe. Einige habe ich also erst mal nachgetragen...

Nun frage ich mich aber immer noch, wie ich denn das mit dem Lazy-Loading vs. Include() handhabe, wenn ich tatsächlich alle oder fast alle Daten benötige...
Stored Procedures finde ich nicht so schön...

Ich habe auch schon über Caching nachgedacht - z.B. PERSONen und ORGANISATIONSEINHEITen werden sich selten ändern - vielleicht kann man die generell in einen 2nd-Level-Cache packen? Gibt es dort gute funktionierende Lösungen für das EF?
Das, was ich so mit Google fand, war immer mit Warnhinweisen gespickt, es sei nicht offiziell unterstützt usw., aber vielleicht taugt das ja trotzdem was?

Vielen Dank!

Viele Grüße
Marvin

---

Hypnose Berlin