Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

SQL Server R2 Stored procedure make faster

Hi Experts,

I was wondering if anyone could help me make this SP faster:

Use this ip for input: 10.124.208.208. 
 
Declare @DD_INPUT as varchar(128)
       Set @DD_INPUT = '10.124.208.208'--@INPUT
       Declare @DD_SCANDATE as Date
       set @DD_SCANDATE = (Select distinct top 1  sd.ScanDate  as [ScanDate]
                    from dflt.scandates as sd
                    Where sd.id in (Select distinct top 1 scandateid from dflt.device order by scandateid desc)
                    order by [ScanDate] desc)
 
;WITH CTE_SCANNED as (
SELECT
count(Case when   [Source type] = 'Nessus Scan' then 1 end) as [TotalScanned],
count(case when  [Source type] = 'Informational' then 1 end) as [Totalbad],
ScanDate, [Source type],[Branch Region], [Branch Network], Branch
 
From
(
SELECT Distinct dsfs.[DeviceID] as [DeviceID],  sd.ScanDate,
case when dsfs.pluginsource = 19506 then 'Nessus Scan' else 'Informational' end as [Source type], sos.OSName as [OS]
,FIP.[Branch Region], FIP.[Branch Network], FIP.Branch
  FROM [FODW_PVT].[Dflt].[Device_ScanInfo_Facts] as dsfs
  INNER JOIN (Select top 4 ID as ScandateID, Scandate from dflt.scandates order by [ScanDate] desc) as sd on sd.ScandateID = dsfs.ScandateID
  INNER JOIN [FODW_PVT].[Dflt].[Device] as d on d.deviceid = dsfs.deviceid
  inner JOIN [Dflt].[SystemsOS] as sos on d.OSID = sos.OSID  
  INNER JOIN dflt.Device_IPLong_FacilityIPRange sip on d.IPlong = sip.IPLong
  INNER JOIN dflt.FacilityIPRange as FIP on (sip.FacilityIPRangeID = FIP.ID)
 
  WHERE 
  (d.[IPAddress] = @DD_INPUT OR d.[Hostname]=@DD_INPUT) 
   AND dsfs.FactName in ( 'SMB Cannot Access Windows Registry',  'Authentication Failure', 'Nessus Windows Scan Performed Non Admin', 'SMB Registry Not Fully Accessible', 'Scan Start Date')
  AND dsfs.pluginsource in (26917, 21745, 24786, 10428, 19506)
  ) as X
 
  group by Scandate, [Branch Region], [Branch Network], Branch, 
  [Source type]
  )
 
  SELECT Sum(TotalScanned) as [TotalScanned], Sum(Totalbad) as [Totalbad], cs.ScanDate, [Branch Region], [Branch Network], Branch
  
  FROM CTE_SCANNED as cs
  GROUP BY cs.Scandate, [Branch Region], [Branch Network], Branch
  
  UNION ALL
 
  SELECT '0' as [TotalScanned] --In VS, 2 = No
  , '0' as [Totalbad] --In VS, 2 = N/A
  , sd.ScanDate,'Null' as [Branch Region], 'Null' as [Branch Network], 'Null' as Branch
  FROM (Select Top 4 ScanDate from dflt.ScanDates where scandate <= @DD_SCANDATE  order by ScanDate Desc) as sd
  LEFT JOIN (SELECT distinct top 4 Scandate from CTE_SCANNED where  scandate <= @DD_SCANDATE ) as cs on sd.ScanDate = cs.ScanDate
  WHERE cs.ScanDate is NULL

Open in new window

0
Amour22015
Asked:
Amour22015
  • 3
  • 2
  • 2
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
Could you please attach the actual execution plan? Also you are using lot of Order Bys... Order by normally degrade the performance if the index is not used by the optimizer or if the index is not there.
0
 
HuaMinChenBusiness AnalystCommented:
Hi,
Can you check if you have indexes on relevant columns on which you link the tables on INNER JOIN?
0
 
Amour22015Author Commented:
Yes I have indexes and found that was the problem, the table(s) had grown and not been indexed  for a while.

But looking at the query is there any suggestions on improving performance.  Or is the query the best one can do?


Thank you for responding.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Pawan KumarDatabase ExpertCommented:
Ohhh now i think you should Update the stats on the large column and rebuild the indexes.  use below commands..

ALTER INDEX ALL ON tablename  
REBUILD;  

GO

UPDATE STATISTICS tableName;  
GO


Also these should be part of your Maintenance Plan.
0
 
HuaMinChenBusiness AnalystCommented:
Amour,
You check your query to see if it does work quickly or not. If not, you add proper indexes.
0
 
Pawan KumarDatabase ExpertCommented:
Hi Amour22015
Any update on this?
0
 
Amour22015Author Commented:
Yes this has been done, thanks great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now