So, ich habs jetzt abermals optimieren können. Die "teuren" Likes konnte ich jetzt eliminieren und gegen = tauschen. Alle großen Joins Fields sind nun indiziert. Konnte so die effektive Web-Anzeigezeit von 10-20 Sekunden auf vielleicht 200-300ms
senken. Auch wenn ich auf das Endergebnis über das Webfrontend filtere geht das gleich schnell wie ohne filter.
Ich danke euch nochmal vielmals
ALTER FUNCTION udf_Device_Server_Win_Missing_Required_SW (@Mode NVARCHAR(32))
RETURNS @MissingSW TABLE (
[Name] NVARCHAR(64)
,DisplayName NVARCHAR(256)
,[Version] NVARCHAR(64)
,Verw NVARCHAR(16)
,PatchAm NVARCHAR(64)
,DeviceType NVARCHAR(128)
,id INT
-- ,Computername NVARCHAR(64)
-- ,DisplayName2 NVARCHAR(256)
INDEX MyCIDX CLUSTERED (DisplayName,Name,Patcham,Verw)
)
AS
BEGIN
-- Find Softwarepackages
DECLARE @Required_SWPackages TABLE (
Displayname NVARCHAR(256)
,[Version] NVARCHAR(64)
,Verw NVARCHAR(16)
)
IF @mode = 'allversions'
INSERT INTO @Required_SWPackages
SELECT Installed.Displayname
,Installed.[Version]
,Req.Verw
FROM p_Device_Software_Installations Installed
INNER JOIN CFGRequiredSoftware Req ON (
Installed.Displayname LIKE Req.Displayname
AND Installed.[Version] LIKE (
CASE
WHEN Req.[Version] IS NULL
THEN '%'
WHEN Req.[Version] = ''
THEN '%'
ELSE Req.[Version]
END
)
)
GROUP BY Installed.Displayname
,Installed.[Version]
,Verw
ELSE
INSERT INTO @Required_SWPackages
SELECT Displayname
,[Version]
,Verw
FROM CFGRequiredSoftware
-- Filter Active Servers
DECLARE @Active_Windows_Servers TABLE (
[Name] NVARCHAR(64)
,Verw NVARCHAR(16)
,PatchAm NVARCHAR(255)
,DeviceType NVARCHAR(128)
,id INT
)
INSERT INTO @Active_Windows_Servers
SELECT [Name]
,Verw
,PatchAm
,DeviceType
,id
FROM View_Device_Server_Win
WHERE InstallStatus = 'active'
-- Get currently installed (filtered by Required_SWPackages)
DECLARE @Currently_Installed TABLE (
Computername NVARCHAR(64)
,DisplayName NVARCHAR(256)
,[Version] NVARCHAR(64)
,Verw NVARCHAR(16)
,DisplayNameSS NVARCHAR(256)
,VersionSS Nvarchar(64)
,VerwSS NVARCHAR(16)
Index myIndex CLUSTERED (Computername,DisplayNameSS,VersionSS,VerwSS)
)
INSERT INTO @Currently_Installed
SELECT View_Software_Installations.Computername
,View_Software_Installations.DisplayName
,View_Software_Installations.[Version]
,View_Software_Installations.Verw
,Required_SWPackages.Displayname
,Required_SWPackages.[Version]
,Required_SWPackages.Verw
FROM View_Software_Installations
Right Join @Required_SWPackages Required_SWPackages ON (
View_Software_Installations.DisplayName Like Required_SWPackages.DisplayName
AND View_Software_Installations.[Version] Like Required_SWPackages.[Version]
AND View_Software_Installations.Verw Like Required_SWPackages.Verw)
--- Creating cardesian product of active servers and required SW
DECLARE @Workinglist TABLE (
DisplayName NVARCHAR(256)
,[Version] NVARCHAR(64)
,[Name] NVARCHAR(64)
,Verw NVARCHAR(16)
,PatchAm NVARCHAR(255)
,DeviceType NVARCHAR(128)
,id INT
INDEX MyIndex CLUSTERED ([Name],Displayname)
)
INSERT INTO @Workinglist
SELECT Displayname
,[Version]
,MyServers.*
FROM @Required_SWPackages SW
--CROSS JOIN @Active_Windows_Servers MyServers
Full Outer Join @Active_Windows_Servers MyServers ON (MyServers.Verw Like SW.Verw)
-- Executing final Return Query
IF @mode = 'allversions'
INSERT INTO @MissingSW
SELECT workinglist.[Name]
,workinglist.DisplayName
,workinglist.[Version]
,workinglist.Verw
,workinglist.PatchAm
,workinglist.DeviceType
,workinglist.id
-- ,Currently_Installed.Computername
-- ,Currently_Installed.DisplayName
FROM @Workinglist workinglist
LEFT JOIN @Currently_Installed Currently_Installed ON (
workinglist.[Name] = Currently_Installed.Computername
AND workinglist.DisplayName = Currently_Installed.DisplayName
AND workinglist.[Version] = Currently_Installed.[Version]
)
WHERE Computername IS NULL
ORDER BY Name
ELSE
INSERT INTO @MissingSW
SELECT workinglist.[Name]
,workinglist.DisplayName
,workinglist.[Version]
,workinglist.Verw
,workinglist.PatchAm
,workinglist.DeviceType
,workinglist.id
-- ,Currently_Installed.Computername
-- ,Currently_Installed.DisplayName
FROM @Workinglist workinglist
LEFT JOIN @Currently_Installed Currently_Installed
ON (
workinglist.[Name] = Currently_Installed.Computername
AND (Currently_Installed.DisplayNameSS = workinglist.DisplayName)
)
WHERE Computername IS NULL
ORDER BY Name
RETURN;
END;
