Amour22015
asked on
SQL Server 2012 r2 - calculations/operation on many Temp Tables
Hi Experts,
Currently I am working on reducing the time each temp table will take:
Temp Tables each on has: Scandate, Category, Plugin, IPLong as fields:
##tmpBaseline
##tmpSpecial
##tmpAdobe
##tmpJava
##tmpMicrosoft
##tmpetc......
Each one of the temp tables has many records, having Month1, Month2, Month3, Month4, Month5, Month6 and the records are to be counted for the next step.
So for this posting. My question relates to calculations/operation. I know the below attempt query is not correct nor is the logic.
Presidencies of order:
##tmpBaseline BL = 100,000 Records
##tmpSpecial SP = 250,000
##Adobe AB = 500,000
##tmpJava JA = 1,000,000
##tmpMicrosoft MS = 600,000
##tmpetc......Etc
So:
BL = BaseLine on chart
SP - BL= Special on Chart
AB - BL- SP(less non Special)) = Adobe on Chart
Maybe like:
Because each temp table has for each month I will also need to include something like:
Scandate = (select top 1 Scandate From BL) as Month6
and so on....
The Chart will be developed in SSRS the attachment is excel example please see chart.
Please help and thanks...
Chart.xlsx
Currently I am working on reducing the time each temp table will take:
Temp Tables each on has: Scandate, Category, Plugin, IPLong as fields:
##tmpBaseline
##tmpSpecial
##tmpAdobe
##tmpJava
##tmpMicrosoft
##tmpetc......
Each one of the temp tables has many records, having Month1, Month2, Month3, Month4, Month5, Month6 and the records are to be counted for the next step.
So for this posting. My question relates to calculations/operation. I know the below attempt query is not correct nor is the logic.
Presidencies of order:
##tmpBaseline BL = 100,000 Records
##tmpSpecial SP = 250,000
##Adobe AB = 500,000
##tmpJava JA = 1,000,000
##tmpMicrosoft MS = 600,000
##tmpetc......Etc
So:
BL = BaseLine on chart
SP - BL= Special on Chart
AB - BL- SP(less non Special)) = Adobe on Chart
Maybe like:
From MS
WHERE Plugin NOT IN
((SELECT SP.Plugin From SP) And (Select BL.Plugin From BL))
Because each temp table has for each month I will also need to include something like:
Scandate = (select top 1 Scandate From BL) as Month6
and so on....
The Chart will be developed in SSRS the attachment is excel example please see chart.
Please help and thanks...
Chart.xlsx
ASKER
Hi Experts,
I have not written the whole Stored Procedure yet. Thus the reason for this post. I am looking for help to finish this query with:
calculations/operation.
Based on:
Presidencies of order:
##tmpBaseline BL = 100,000 Records
##tmpSpecial SP = 250,000
##Adobe AB = 500,000
##tmpJava JA = 1,000,000
##tmpMicrosoft MS = 600,000
##tmpetc......Etc
Please help and thanks
I have not written the whole Stored Procedure yet. Thus the reason for this post. I am looking for help to finish this query with:
calculations/operation.
Based on:
Presidencies of order:
##tmpBaseline BL = 100,000 Records
##tmpSpecial SP = 250,000
##Adobe AB = 500,000
##tmpJava JA = 1,000,000
##tmpMicrosoft MS = 600,000
##tmpetc......Etc
Please help and thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Experts,
So far what I have:
Based on:
Presidencies of order:
#tmpSpecial SP = 250,000
#tmpBaseline BL = 100,000 Records
#tmpMarker MK = 200,000
#Adobe AB = 500,000
#tmpJava JA = 1,000,000
#tmpMicrosoft MS = 600,000
#tmpMSXML MSX = 2,000
So this is the order:
SP: Special = Special on Chart
BL: ABOVE BASELINE – SPECIALIZED = ABOVE BASELINE ON CHART
AB: ADOBE – ABOVE BASELINE – SPECIALIZED = ADOBE ON CHART
MSX: MSXML – ABOVE BASELINE – SPECIALIZED = MSXML ON CHART
MS: MICROSOFT – ABOVE BASELINE – SPECIALIZED – MSXML = MICROSOFT ON CHART
JA: JAVA – ABOVE BASELINE – SPECIALIZED – MARKER FILE - Microsoft = ACTIONABLE JAVA ON CHART
MK: Marker - JAVA – ABOVE BASELINE – SPECIALIZED - Microsoflt = MARKER FILE ON CHART
TOTAL VULNERABILITIES – ABOVE BASELINE – SPECIALIZED – ALL CATEGORIES = OTHERS ON CHART
So something like:
Please help and thanks
So far what I have:
CREATE TABLE #tmpbaseline(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Specialized Catagory
IF OBJECT_ID('TempDB..#tmpSpecialized') IS NOT NULL
DROP TABLE #tmpSpecialized;
CREATE TABLE #tmpSpecialized(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Marker-File (Java)
IF OBJECT_ID('TempDB..#tmpMarkerFile') IS NOT NULL
DROP TABLE #tmpMarkerFile;
CREATE TABLE #tmpMarkerFile(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Java Category
IF OBJECT_ID('TempDB..#tmpJava') IS NOT NULL
DROP TABLE #tmpJava;
CREATE TABLE #tmpJava(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Adobe Category
IF OBJECT_ID('TempDB..#tmpAdobe') IS NOT NULL
DROP TABLE #tmpAdobe;
CREATE TABLE #tmpAdobe(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Microsoft Category
IF OBJECT_ID('TempDB..#tmpMicrosoft') IS NOT NULL
DROP TABLE #tmpMicrosoft;
CREATE TABLE #tmpMicrosoft(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- MSXML Category
IF OBJECT_ID('TempDB..#tmpMSXML') IS NOT NULL
DROP TABLE #tmpMSXML;
CREATE TABLE #tmpMSXML(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- ************************************************ Categories **********************************************************************************
-- Above Baseline Section
Insert into #tmpbaseline (Scandate, Category, Plugin, IPLong)
SELECT
rs.[ScanDate]
,'Above Baseline' as Category
,rs.[Plugin]
,rs.IPLong
FROM [Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
Inner Join dflt.scandates as sd on rs.ScanDateID = sd.ID
Left outer join [Dflt].[OperatingSystemGroups] as osg on osg.OSGroupID = rs.OSGroupID
Inner Join [FODW_PVT].dflt.[F_VAResponseActionItemNoDups]() vra
On rs.Plugin = vra.Plugin and osg.MajorGrouping = vra.Operating_Sys and sd.ID = vra.ScanDateID
where [Actionable] = 0 and vra.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By rs.[ScanDate], rs.[Plugin], rs.IPLong
-- 81,608 Dev
-- 2.03 sec Dev
-- 948,063 Prod
-- 1.52 sec Prod
-- 1,909,563 1.16 sec Based on 6 months in Dev as of 1/31/17
-- End Above_basline
-- Specialized Catagory Section
Insert into #tmpSpecialized (Scandate, Category, Plugin, IPLong)
SELECT
rs.[ScanDate]
,'Specialized' as Category
,rs.Plugin
,rs.IPLong
From Dflt.Systems as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
Left Outer join Dflt.ADComputers as adc on adc.ComputerName = rs.HostName
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc) and (adc.OUPath like '%Specialized Systems%'
Or adc.OUPath like '%Medical%') and vn.Severity in ('Critical', 'High') --and [Actionable] = 1
Group By rs.ScanDate, Rs.Plugin, rs.IPLong
-- Specialized **************************
-- Month of Dec
-- 291,337 Prod
-- .19 sec Prod Or
-- All 6 months
-- 1,719,802 Prod
-- .40 sec Prod
-- 1,707,945 records 1.08 sec in Dev as of 1/31/17
-- End Specialized Category
-- Marker-file (Java) Category Section *** Note: Still needs a lot of work... as of 1/25/17, need to add in checks for previous inputs to report
Insert into #tmpMarkerFile (Scandate, Category, Plugin, IPLong)
select
Scandate
,'Marker File (Java)' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
inner join [Dflt].[SCCM_v_R_System] as s on s.iplong = rs.iplong
inner join [Dflt].[SCCM_Resource_MarkerFile] as rm on s.ResourceID = rm.ResourceID and s.SCCM_DB = rm.SCCM_DB
inner join [Dflt].[SCCM_MarkerFile] as m on m.MarkerFileID = rm.MarkerFileID
inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin
inner Join [Dflt].[OperatingSystemGroups] OSG On s.OSGroupID = OSG.OSGroupID
left outer join [Dflt].[Ownership_Systems] as Own on Own.SystemsID = rs.SystemsID and rs.ScanDateID = own.ScandateID
left outer join Dflt.Organizations as Org on Org.OrganizationID = Own.OrganizationID
Where
m.MarkerFileName like '%java%'
and vul.[plugin name] like '%java%'
and rs.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
and vul.Severity in ('Critical', 'High')
Group By Scandate, rs.plugin, rs.IPLong
-- Marker File (Java)
-- Dec
-- 90,177 Prod
-- .3 sec Or
-- 6 month
-- 418,469 Prod
-- .15 sec
-- 418,469 records .19 sec in Dev as on 1/31/17
-- End Marker-file (Java)
-- ***************** Application Catalog *******************************
-- Java Category Section
Insert into #tmpJava (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'Java' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
INNER JOIN (SELECT distinct Plugin, Category
FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
Where Category = 'Java') as Cat On Cat.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By ScanDate, rs.Plugin, rs.IPLong
-- Java Category
-- Dec
-- 45,135 Prod
-- .1 sec Or
-- 6 month
-- 287,579 Prod
-- .8 sec
-- 287,477 records .10 sec in Dev as of 1/31/17
-- End Java Category
-- Adobe Category Section
Insert into #tmpAdobe (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'Adobe' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
INNER JOIN (SELECT Plugin, Category
FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
Where Category like '%Adobe%'
) as Cat On Cat.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By ScanDate, rs.Plugin, rs.IPLong
-- Adobe Category
-- Dec
-- 952,149 Prod
-- .26 sec Or
-- 6 month
-- 4,830,682 Prod
-- 2.24 sec
-- Temp table
-- 335 records
-- 4.02 sec
-- 4,829,949 7.21 sec in Dev as of 1/31/17
-- End Adobe Category
-- Microsoft Category ****** Note this section needs updating see notes ************* Takes way too long to render 1/27/17
Insert into #tmpMicrosoft (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'Microsoft' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
INNER JOIN (SELECT distinct Plugin, Category
FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
Where Category In ('Microsoft (Non-Patch)', 'Microsoft (Patch)')
) as Cat On Cat.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By ScanDate, rs.Plugin, rs.IPLong
-- Microsoft Category
-- Dec
-- 952,149 Prod
-- .26 sec Or
-- 6 month
-- 4,830,682 Prod
-- 2.24 sec
-- Temp table
-- 335 records
-- 4.02 sec
-- 7,867,469 13.57 Sec in Dev as of 1/31/17
-- Takes way too long to render *****************************************************
-- End Microsoft Category
-- MSXML Category ***** Note: Did not find "MSXML" in CVA_App_Catalog.Category Table
Insert into #tmpMSXML (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'MSXML' as Category
,rs.[Plugin]
,IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vul.Severity in ('Critical', 'High') and vul.[plugin name] = 'Microsoft XML Parser (MSXML) and XML Core Services Unsupported'
Group By ScanDate, rs.Plugin, rs.IPLong
-- MSXML Category
-- 233,818 Records 1.47 sec in Dev as of 1/31/17
-- End MSXML Category
Based on:
Presidencies of order:
#tmpSpecial SP = 250,000
#tmpBaseline BL = 100,000 Records
#tmpMarker MK = 200,000
#Adobe AB = 500,000
#tmpJava JA = 1,000,000
#tmpMicrosoft MS = 600,000
#tmpMSXML MSX = 2,000
So this is the order:
SP: Special = Special on Chart
BL: ABOVE BASELINE – SPECIALIZED = ABOVE BASELINE ON CHART
AB: ADOBE – ABOVE BASELINE – SPECIALIZED = ADOBE ON CHART
MSX: MSXML – ABOVE BASELINE – SPECIALIZED = MSXML ON CHART
MS: MICROSOFT – ABOVE BASELINE – SPECIALIZED – MSXML = MICROSOFT ON CHART
JA: JAVA – ABOVE BASELINE – SPECIALIZED – MARKER FILE - Microsoft = ACTIONABLE JAVA ON CHART
MK: Marker - JAVA – ABOVE BASELINE – SPECIALIZED - Microsoflt = MARKER FILE ON CHART
TOTAL VULNERABILITIES – ABOVE BASELINE – SPECIALIZED – ALL CATEGORIES = OTHERS ON CHART
So something like:
2:From MS
3:WHERE Plugin
4:NOT EXISTS
5:(
6: (SELECT NULL From SP WHERE SP.Plugin = MS.Plugin And SP.IPLong = MS.IPLong And SP.Scandate = MS.Scandate)
7: AND (SELECT NULL FROM BL WHERE BL.Plugin = MS.Plugin And SP.Scandate = MS.Scandate And SP.IPLong = MS.IPLong )
8:)
Please help and thanks
ASKER
Hi Experts,
So far I have this:
I also added:
On each end of the Select Statements (got errors otherwise).
Thank you for helping...
So far I have this:
USE FODW_PVT
-- Note SP: [App].[usp_RP_SMA_Division_6monthVulnCategoryAction]
--In the case where “CAREMANAGEMENT “ is not populated
--ABOVE BASELINE – SPECIALIZED = ABOVE BASELINE ON CHART
--ADOBE – ABOVE BASELINE – SPECIALIZED = ADOBE ON CHART
--MSXML – ABOVE BASELINE – SPECIALIZED = MSXML ON CHART
--MICROSOFT – ABOVE BASELINE – SPECIALIZED – MSXML = MICROSOFT ON CHART
--JAVA – ABOVE BASELINE – SPECIALIZED – MARKER FILE = ACTIONABLE JAVA ON CHART
--JAVA – ABOVE BASELINE – SPECIALIZED – ACTIONABLE JAVA = MARKER FILE ON CHART
--TOTAL VULNERABILITIES – ABOVE BASELINE – SPECIALIZED – ALL CATEGORIES = OTHERS ON CHART
-- turn into Temp Table if required filters are needed example Microsoft which category: If in Above basline, Specialized, Etc...
-- Categories:
--MSXML
--Microsoft
--Adobe
--Above Baseline
--Java
--Specialized
--Other
--Marker-file (Java)
-- Above_basline Category Note: bring temp table to end and do more testing... 1/27/17
-- top part needs to go to the every top
-- Temp Table Create/Drop Section:
-- Above Baseline
IF OBJECT_ID('TempDB..#tmpbaseline') IS NOT NULL
DROP TABLE #tmpbaseline;
CREATE TABLE #tmpbaseline(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Specialized Catagory
IF OBJECT_ID('TempDB..#tmpSpecialized') IS NOT NULL
DROP TABLE #tmpSpecialized;
CREATE TABLE #tmpSpecialized(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Marker-File (Java)
IF OBJECT_ID('TempDB..#tmpMarkerFile') IS NOT NULL
DROP TABLE #tmpMarkerFile;
CREATE TABLE #tmpMarkerFile(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Java Category
IF OBJECT_ID('TempDB..#tmpJava') IS NOT NULL
DROP TABLE #tmpJava;
CREATE TABLE #tmpJava(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Adobe Category
IF OBJECT_ID('TempDB..#tmpAdobe') IS NOT NULL
DROP TABLE #tmpAdobe;
CREATE TABLE #tmpAdobe(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Microsoft Category
IF OBJECT_ID('TempDB..#tmpMicrosoft') IS NOT NULL
DROP TABLE #tmpMicrosoft;
CREATE TABLE #tmpMicrosoft(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- MSXML Category
IF OBJECT_ID('TempDB..#tmpMSXML') IS NOT NULL
DROP TABLE #tmpMSXML;
CREATE TABLE #tmpMSXML(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- ************************************************ Categories **********************************************************************************
-- Above Baseline Section
Insert into #tmpbaseline (Scandate, Category, Plugin, IPLong)
SELECT
rs.[ScanDate]
,'Above Baseline' as Category
,rs.[Plugin]
,rs.IPLong
FROM [Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
Inner Join dflt.scandates as sd on rs.ScanDateID = sd.ID
Left outer join [Dflt].[OperatingSystemGroups] as osg on osg.OSGroupID = rs.OSGroupID
Inner Join [FODW_PVT].dflt.[F_VAResponseActionItemNoDups]() vra
On rs.Plugin = vra.Plugin and osg.MajorGrouping = vra.Operating_Sys and sd.ID = vra.ScanDateID
where [Actionable] = 0 and vra.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By rs.[ScanDate], rs.[Plugin], rs.IPLong
-- 1,909,563 1.16 sec Based on 6 months in Dev as of 1/31/17
-- End Above_basline
-- Specialized Catagory Section
Insert into #tmpSpecialized (Scandate, Category, Plugin, IPLong)
SELECT
rs.[ScanDate]
,'Specialized' as Category
,rs.Plugin
,rs.IPLong
From Dflt.Systems as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
Left Outer join Dflt.ADComputers as adc on adc.ComputerName = rs.HostName
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc) and (adc.OUPath like '%Specialized Systems%'
Or adc.OUPath like '%Medical%') and vn.Severity in ('Critical', 'High') --and [Actionable] = 1
Group By rs.ScanDate, Rs.Plugin, rs.IPLong
-- 1,707,945 records 1.08 sec in Dev as of 1/31/17
-- End Specialized Category
-- Marker-file (Java) Category Section
Insert into #tmpMarkerFile (Scandate, Category, Plugin, IPLong)
select
Scandate
,'Marker File (Java)' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
inner join [Dflt].[SCCM_v_R_System] as s on s.iplong = rs.iplong
inner join [Dflt].[SCCM_Resource_MarkerFile] as rm on s.ResourceID = rm.ResourceID and s.SCCM_DB = rm.SCCM_DB
inner join [Dflt].[SCCM_MarkerFile] as m on m.MarkerFileID = rm.MarkerFileID
inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin
inner Join [Dflt].[OperatingSystemGroups] OSG On s.OSGroupID = OSG.OSGroupID
left outer join [Dflt].[Ownership_Systems] as Own on Own.SystemsID = rs.SystemsID and rs.ScanDateID = own.ScandateID
left outer join Dflt.Organizations as Org on Org.OrganizationID = Own.OrganizationID
Where
m.MarkerFileName like '%java%'
and vul.[plugin name] like '%java%'
and rs.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
and vul.Severity in ('Critical', 'High')
Group By Scandate, rs.plugin, rs.IPLong
-- 418,469 records .19 sec in Dev as on 1/31/17
-- End Marker-file (Java)
-- ***************** Application Catalog *******************************
-- Java Category Section
Insert into #tmpJava (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'Java' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
INNER JOIN (SELECT distinct Plugin, Category
FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
Where Category = 'Java') as Cat On Cat.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By ScanDate, rs.Plugin, rs.IPLong
-- 287,477 records .10 sec in Dev as of 1/31/17
-- End Java Category
-- Adobe Category Section
Insert into #tmpAdobe (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'Adobe' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
INNER JOIN (SELECT Plugin, Category
FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
Where Category like '%Adobe%'
) as Cat On Cat.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By ScanDate, rs.Plugin, rs.IPLong
-- 4,829,949 7.21 sec in Dev as of 1/31/17
-- End Adobe Category
-- Microsoft Category Section
Insert into #tmpMicrosoft (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'Microsoft' as Category
,rs.[Plugin]
,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
INNER JOIN (SELECT distinct Plugin, Category
FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
Where Category In ('Microsoft (Non-Patch)', 'Microsoft (Patch)')
) as Cat On Cat.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vn.Severity in ('Critical', 'High')
Group By ScanDate, rs.Plugin, rs.IPLong
-- 7,867,469 13.57 Sec in Dev as of 1/31/17
-- Takes way too long to render *****************************************************
-- End Microsoft Category
-- MSXML Category Section
Insert into #tmpMSXML (Scandate, Category, Plugin, IPLong)
SELECT
Scandate
,'MSXML' as Category
,rs.[Plugin]
,IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs
inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
and vul.Severity in ('Critical', 'High') and vul.[plugin name] = 'Microsoft XML Parser (MSXML) and XML Core Services Unsupported'
Group By ScanDate, rs.Plugin, rs.IPLong
-- MSXML Category
-- 233,818 Records 1.47 sec in Dev as of 1/31/17
-- End MSXML Category
-- ********************** Calculations *******************************
Select
SL.Scandate,
SL.Category,
SL.Plugin,
SL.IPLong,
Count(*) as Total
From #tmpSpecialized SL -- Specialized
UNION ALL
Select
AB.Scandate,
AB.Category,
AB.Plugin,
AB.IPLong,
Count(*) as Total
From #tmpbaseline AB -- Above Base line
Where --AB.Plugin, AB.IPLong
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (AB.Plugin = SL.Plugin And AB.IPLong = SL.IPLong And AB.Scandate = SL.Scandate)
)
UNION ALL
--- Temp Table
Select
MF.Scandate,
MF.Category,
MF.Plugin,
MF.IPLong,
Count(*) as Total
From #tmpMarkerFile MF -- Marker File (Java)
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (MF.Plugin = SL.Plugin And MF.IPLong = SL.IPLong And MF.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB Where (MF.Plugin = AB.Plugin And MF.IPLong = AB.IPLong And MF.Scandate = AB.Scandate)
)
UNION ALL
---- Temp Table
Select
JA.Scandate,
JA.Category,
JA.Plugin,
JA.IPLong,
Count(*) as Total
From #tmpJava JA -- Java Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (JA.Plugin = SL.Plugin And JA.IPLong = SL.IPLong And JA.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB Where (JA.Plugin = AB.Plugin And JA.IPLong = AB.IPLong And JA.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF Where (JA.Plugin = MF.Plugin And JA.IPLong = MF.IPLong And JA.Scandate = MF.Scandate)
)
UNION ALL
-- Temp Table
Select
AD.Scandate,
AD.Category,
AD.Plugin,
AD.IPLong,
Count(*) as Total
From #tmpAdobe AD -- Adobe Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (AD.Plugin = SL.Plugin And AD.IPLong = SL.IPLong And AD.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB Where (AD.Plugin = AB.Plugin And AD.IPLong = AB.IPLong And AD.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF Where (AD.Plugin = MF.Plugin And AD.IPLong = MF.IPLong And AD.Scandate = MF.Scandate)
UNION
SELECT NULL FROM #tmpJava JA Where (AD.Plugin = JA.Plugin And AD.IPLong = JA.IPLong And AD.Scandate = JA.Scandate)
)
UNION ALL
-- Temp Table
Select
MS.Scandate,
MS.Category,
MS.Plugin,
MS.IPLong,
Count(*) as Total
From #tmpMicrosoft MS -- Microsoft Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (MS.Plugin = SL.Plugin And MS.IPLong = SL.IPLong And MS.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB Where (MS.Plugin = AB.Plugin And MS.IPLong = AB.IPLong And MS.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF Where (MS.Plugin = MF.Plugin And MS.IPLong = MF.IPLong And MS.Scandate = MF.Scandate)
UNION
SELECT NULL FROM #tmpJava JA Where (MS.Plugin = JA.Plugin And MS.IPLong = JA.IPLong And MS.Scandate = JA.Scandate)
UNION
SELECT NULL FROM #tmpAdobe AD Where (MS.Plugin = AD.Plugin And MS.IPLong = AD.IPLong And MS.Scandate = AD.Scandate)
)
UNION ALL
-- Temp Table
Select
MSX.Scandate,
MSX.Category,
MSX.Plugin,
MSX.IPLong,
Count(*) as Total
From #tmpMSXML MSX -- MSXML Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (MSX.Plugin = SL.Plugin And MSX.IPLong = SL.IPLong And MSX.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB Where (MSX.Plugin = AB.Plugin And MSX.IPLong = AB.IPLong And MSX.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF Where (MSX.Plugin = MF.Plugin And MSX.IPLong = MF.IPLong And MSX.Scandate = MF.Scandate)
UNION
SELECT NULL FROM #tmpJava JA Where (MSX.Plugin = JA.Plugin And MSX.IPLong = JA.IPLong And MSX.Scandate = JA.Scandate)
UNION
SELECT NULL FROM #tmpAdobe AD Where (MSX.Plugin = AD.Plugin And MSX.IPLong = AD.IPLong And MSX.Scandate = AD.Scandate)
UNION
SELECT NULL FROM #tmpMicrosoft MS Where (MSX.Plugin = MS.Plugin And MSX.IPLong = MS.IPLong And MSX.Scandate = MS.Scandate)
)
I also added:
Group By MSX.Scandate,
MSX.Category,
MSX.Plugin,
MSX.IPLong
On each end of the Select Statements (got errors otherwise).
Thank you for helping...
ASKER
Great Thanks for helping
Well for this case you should either use JOIN, Cross Apply or NOT EXISTS. In are slow in nature.
NOT EXISTS is much better if you are just checking for the row existence. It will exit on the first row itself from the right hand side.
Code will look like below-
Open in new window
If you want fetch data from Right hand table then you should either use JOIN or Cross Apply.
Open in new window
For the above code always use ORDER BY like below. I also suspect that you can use Cross Apply in the case.
Open in new window
Please provide the entire query so that we can check and provide you all the performance tips.
Hope it helps!