Problem mit einer TSQL Abfrage

[digga_mies]

Hi Leute,

ich habe mir eine TSQL Abfrage zusammengeschustert, die leider Gottes nicht mehr aufhören will zu laufen. Sie läuft knapp 40min ohne Ergebnis und das ist leicht bescheuert.

Innerhalb soll gezählt werden wie viele Rechner das aktuelle Virenschutzupdate installiert haben und die Gesamtzahl der Rechner in dem Land stehen. Daraus kann man dann prozentuale Anteile errechnen usw, aber das gehört ja nicht zur Abfrage.

Kann mir jemand sagen, was ich in diesem Zusammenhang falsch mache? Weil wenn ich jeweils nur einen Teil der Abfrage laufen lasse (up2date clients oder alle clients) dann läuft die abfrage in wenigen sekunden. Hat jemand eine Idee. Eigentlich dürfte es nur ein Logikfehler sein.

Grüße digga_mies

Hier die Abfrage:

DECLARE @VARIABLE1 NUMERIC(9)

SELECT @VARIABLE1 = MAX(DATVer) FROM EPOProductProperties

SET @VARIABLE1 = @VARIABLE1 - 3;


SELECT	

		EPOBranchNode.NodeName

		,COUNT(DISTINCT Rechner_aktuell.NodeName) AS Up2DateComputers

		,COUNT(DISTINCT Rechner_total.NodeName) AS TotalComputers

  FROM [ePO4_BBMAGA3].[dbo].[EPOBranchNode]

  		LEFT JOIN

			(SELECT

				[EPOLeafNode].[NodeName], [EPOBranchNode].[L1ParentID]


			FROM [EPOLeafNode]

				left join [EPOProductProperties] ON [EPOProductProperties].[ParentID] = [EPOLeafNode].[AutoID]

				left join [EPOBranchNode] ON [EPOBranchNode].[AutoID] = [EPOLeafNode].[ParentID]


			WHERE

				[EPOLeafNode].[ParentID] = EPOBranchNode.AutoID

				AND EPOProductProperties.ProductCode LIKE 'VIRUS%' AND EPOProductProperties.DATVer >= CAST(@VARIABLE1 AS nvarchar(4))

			) AS Rechner_aktuell ON Rechner_aktuell.L1ParentID = EPOBranchNode.L1ParentID

		LEFT JOIN

			(SELECT

				[EPOLeafNode].[NodeName], [EPOBranchNode].[L1ParentID]


			FROM [EPOLeafNode]

				left join [EPOProductProperties] ON [EPOProductProperties].[ParentID] = [EPOLeafNode].[AutoID]

				left join [EPOBranchNode] ON [EPOBranchNode].[AutoID] = [EPOLeafNode].[ParentID]


			WHERE

				[EPOLeafNode].[ParentID] = EPOBranchNode.AutoID

			) AS Rechner_total ON Rechner_total.L1ParentID = EPOBranchNode.L1ParentID

	WHERE EPOBranchNode.NodeName IN ('AR01','AT01','AU01','BE01','BG01','BR00','CH00','CL01','CN00','CO01','CZ00_SK00','DE00','DK01','DO01','EC01','ES00','FI01','FR00','HR01','HU00','ID01','IE01','IN01','IT01','JP01','KH01','KR01','MX01','MY01','MY02','NL01','NO01','PE01','PH01','PK01','PL01','PT01','RO01','RU01','SE02','SG01','TH01','TR01','TW01','UK01','US00','VN01','ZA01')

	GROUP BY EPOBranchNode.NodeName

[streffin]

Hi,

mach die Gruppierung mal direkt in den Subquerys.

Ich geb dir ein Beispiel was dir da die Abfrage zerhaut :

stell dir gedanklich vor, du hättest nur eine einzige L1ParentID.

Folgende Annahmen :

- es gibt nur eine einzige L1ParentID

- Subquery 1 gibt die 3 Rows zurück

- Subquery 2 gibt dir 5 Rows zurück

Folgendes würde passieren :

Du joinst auf der L1ParentID auf Subquery 1 und bekommst 3 Rows im Recordset, jeweils mit der selben L1ParentID. Jetzt joinst du auf Subquery 2 und bekommst 5 Rows vom Subquery, und joinst wieder auf der L1ParentID.

Jede deiner 3 Rows, die du bisher hattest, wird jetzt mit den 5 Rows aus Subquery 2 gejoinst, weil die Expresssion in on condition ja in jeder Row true ergibt.

--> du hast im Recordset jetzt 15 Rows, obwohl du eigentlich nur eine Row bräuchtest.

Das ist auch der Grund, warum du da count(distinct x) machst, weil du jeden Rechnernamen mehrfach in der Finalen Ergebnismenge haben wirst.

Wenn dir jetzt überlegst, was passiert wenn du von den Subquerys jeweils sagen wir 100 Rows zurückbekommst, und du mehr als eine L1ParentID betrachtest, dann weist du warum dein Query nicht funktioniert.

Mich wundert fast dass dir da nichts abschmiert wenn das die TempDB so zumüllt.

Was du tun musst ist deine Subquerys umschreiben.

(

Select Count([distinct] NodeName), L1ParentID

...

GROUP BY L1ParentID

)

Dann bekommt du für jede L1ParentID jeweils eine Anzahl, und hast nicht Millionen nutzlose Rows im Recordset. Falls du das distinct nicht mehr brauchst, lass es weg, das kostet durchaus resourcen

Gruß

Sven

[streffin]

Kleiner Nachtrag ...

In dem speziellen Fall kannst du das ganze ohne Subquerys abhandeln.

select nodename, 

         SUM(CASE WHEN EPOProductProperties.ProductCode LIKE 'VIRUS%'

                           AND  EPOProductProperties.DATVer >= CONVERT(nvarchar(4), @VARIABLE1)

                           THEN 1 ELSE 0 END

               ) [TotalComputers]

         count(NodeName) [Up2DateComputers]

FROM ...

WHERE ...

GROUP BY nodename

Alternativ wäre COUNT(CASE WHEN [expresion] THEN nodename else NULL END)

[digga_mies]

Vielen Dank :uli

Allerdings muss ich zu deinem Nachtrag sagen, das da etwas nich so ganz passt. Da fehlen mir einige Nodes und die Zahlen stimmen einfach nicht mehr überein. Mit deiner ersten Erklärung und der Gruppierung der ParentID in den Subquerys hat es wunderbar geklappt und geht ruckzuck.

Beim Nachtrag müsste man ja auch theoretisch die Namen schon mal vertauschen, weil die Up2DateComputers ja die Rechner sind, deren DATVer >= @Variable1 sind. Und wenn man dann die TotalRechner zählen will, muss man ebenfalls ein CASE WHEN definieren, oder nicht?

Deine Erklärung war aber echt super :uli

[streffin]

Hi,

die Namen waren falsch rum da hast du recht ja. Aber wenn du alle Rechner zählen willst, solltest du normalerweise kein case brauchen, das müsste mit einem count (distinct) erledigt sein. Das kommt drauf an, wie genau die Datenstruktur aufgebaut ist.

Es ging eher um das Prinzip wie du solche Abfragen auch ohne Subquerys lösen kannst, da Subquerys in aller Regel Performance kosten.

Gruß

Sven