Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server R2 Stored procedure make faster

Posted on 2016-10-31
7
Medium Priority
?
76 Views
Last Modified: 2016-11-03
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
Comment
Question by:Amour22015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41867760
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
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41867855
Hi,
Can you check if you have indexes on relevant columns on which you link the tables on INNER JOIN?
0
 

Author Comment

by:Amour22015
ID: 41869188
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 32

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41869452
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
 
LVL 11

Accepted Solution

by:
HuaMinChen earned 1000 total points
ID: 41869457
Amour,
You check your query to see if it does work quickly or not. If not, you add proper indexes.
0
 
LVL 32

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41871607
Hi Amour22015
Any update on this?
0
 

Author Closing Comment

by:Amour22015
ID: 41871845
Yes this has been done, thanks great
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question