Solved

SQL Performance optimisation - selecting as a column

Posted on 2016-11-16
29
44 Views
Last Modified: 2016-12-02
Hi

I have a statement with 5 inner joins, and now I need to add the following to be returned as a column

select count(*) from wlt_tblAlerts_CommentsAndDismissed where ifkAlertCapturedID = [ifkAlertCapturedID] and vComment is not null) as NoOfcomments

Open in new window


I'm concerned about performance
Would it be better to use SUM OF or something else?

All the above does is return a count of how many comments there are for a certain ID
0
Comment
Question by:websss
  • 12
  • 11
  • 5
  • +1
29 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41889337
Better provide us the full query otherwise will can only guess.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41889338
Please provide full query ?
0
 

Author Comment

by:websss
ID: 41889348
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON
GO

CREATE PROCEDURE [dbo].[On_ScreenAlerts]
(
  @CurrentloggedInUser as int=0
)
AS
SELECT ac.ipkAlertCapturedID
      ,ac.ifkAlertID
     -- ,[ifkCommonTrackingID]
      ,iSoftwarePriority
	  ,ct.dGPSDateTime
	  ,ct.vEventName
	  ,ct.vTextMessage
	  ,a.Name
	  ,an.ifkUserId
	  ,al.vAlertName
	  ,(select count(*) from wlt_tblAlerts_CommentsAndDismissed cd inner join wlt_tblAlerts_Captured ac on  cd.ifkAlertCapturedID=ac.ipkAlertCapturedID where ac.ipkAlertCapturedID=cd.ifkAlertCapturedID and vComment is not null) as NoOfcomments  --THIS IS THE CODE IN QUESTION


  FROM dbo.wlt_tblAlerts_Captured ac
  inner join wlt_tblAlerts_NotifyDetails an on ac.ifkAlertID = an.ifkAlertID
  inner join dbo.tblCommonTrackingData ct on ac.ifkCommonTrackingID = ct.ipkCommanTrackingID
  inner join wlt_tblDevices d on d.ImeiNumber = ct.vpkDeviceID
  inner join dbo.wlt_tblAssets a on a.Id = d.ifk_AssignedAssetId
  inner join wlt_tblAlerts al on an.ifkAlertID = al.ipkAlertID
  WHERE 
  an.bOnScreenNotification = 1 
  and ac.bIsDismissed = 0
  and ct.dDbReceivedTime between  DATEADD(HOUR, -48, GETDATE()) and getDate()
  --and an.ifkUserId = @CurrentloggedInUser
  
  order by ct.dGPSDateTime desc

Open in new window

0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41889354
Try ... It should not take much time..

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON
GO

CREATE PROCEDURE [dbo].[On_ScreenAlerts]
(
  @CurrentloggedInUser as int=0
)
AS
SELECT ac.ipkAlertCapturedID
      ,ac.ifkAlertID
     -- ,[ifkCommonTrackingID]
      ,iSoftwarePriority
	  ,ct.dGPSDateTime
	  ,ct.vEventName
	  ,ct.vTextMessage
	  ,a.Name
	  ,an.ifkUserId
	  ,al.vAlertName
	  ,b.NoOfcomments 	  
  FROM dbo.wlt_tblAlerts_Captured ac
  inner join wlt_tblAlerts_NotifyDetails an on ac.ifkAlertID = an.ifkAlertID
  inner join dbo.tblCommonTrackingData ct on ac.ifkCommonTrackingID = ct.ipkCommanTrackingID
  inner join wlt_tblDevices d on d.ImeiNumber = ct.vpkDeviceID
  inner join dbo.wlt_tblAssets a on a.Id = d.ifk_AssignedAssetId
  inner join wlt_tblAlerts al on an.ifkAlertID = al.ipkAlertID
  CROSS APPLY
  ( select count(*) NoOfcomments from wlt_tblAlerts_CommentsAndDismissed cd 
	WHERE cd.ifkAlertCapturedID = ac.ipkAlertCapturedID 
	  AND vComment is not null ) b
  WHERE 
  an.bOnScreenNotification = 1 
  and ac.bIsDismissed = 0
  and ct.dDbReceivedTime between  DATEADD(HOUR, -48, GETDATE()) and getDate()
  --and an.ifkUserId = @CurrentloggedInUser
  
  order by ct.dGPSDateTime desc

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41889383
Try with this SELECT:
;WITH CTE_CommentsAndDismissed
AS
	(select ifkAlertCapturedID, count(1) AS NoOfcomments
	from wlt_tblAlerts_CommentsAndDismissed
	where vComment is not null
	group by ifkAlertCapturedID
	)
SELECT ac.ipkAlertCapturedID
      ,ac.ifkAlertID
     -- ,[ifkCommonTrackingID]
      ,iSoftwarePriority
	  ,ct.dGPSDateTime
	  ,ct.vEventName
	  ,ct.vTextMessage
	  ,a.Name
	  ,an.ifkUserId
	  ,al.vAlertName
	  ,cd.NoOfcomments
  FROM dbo.wlt_tblAlerts_Captured ac
	  inner join wlt_tblAlerts_NotifyDetails an on ac.ifkAlertID = an.ifkAlertID
	  inner join dbo.tblCommonTrackingData ct on ac.ifkCommonTrackingID = ct.ipkCommanTrackingID
	  inner join wlt_tblDevices d on d.ImeiNumber = ct.vpkDeviceID
	  inner join dbo.wlt_tblAssets a on a.Id = d.ifk_AssignedAssetId
	  inner join wlt_tblAlerts al on an.ifkAlertID = al.ipkAlertID
	  inner join CTE_CommentsAndDismissed cd on cd.ifkAlertCapturedID=ac.ipkAlertCapturedID 
  WHERE an.bOnScreenNotification = 1 
	  and ac.bIsDismissed = 0
	  and ct.dDbReceivedTime between  DATEADD(HOUR, -48, GETDATE()) and getDate()
	--and an.ifkUserId = @CurrentloggedInUser
order by ct.dGPSDateTime desc

Open in new window

Are you having performance issues right now?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41889406
Only if you are running on the Development then run below before running the queries, then you will get exact timings

DO NOT RUN BELOW COMMANDS ON Production or Pre production ..

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

GO

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41890077
CREATE PROCEDURE ...
AS
SET NOCOUNT ON;

SELECT ac.ipkAlertCapturedID
      ,ac.ifkAlertID
     -- ,[ifkCommonTrackingID]
      ,iSoftwarePriority
        ,ct.dGPSDateTime
        ,ct.vEventName
        ,ct.vTextMessage
        ,a.Name
        ,an.ifkUserId
        ,al.vAlertName
        ,cd.NoOfcomments
  FROM dbo.wlt_tblAlerts_Captured ac
 inner join (
      select ifkAlertCapturedID, count(*) as NoOfcomments
      from wlt_tblAlerts_CommentsAndDismissed
      where vComment is not null
      group by ifkAlertCapturedID
  ) as cd on cd.ifkAlertCapturedID=ac.ipkAlertCapturedID

  inner join wlt_tblAlerts_NotifyDetails an on ac.ifkAlertID = an.ifkAlertID
  inner join dbo.tblCommonTrackingData ct on ac.ifkCommonTrackingID = ct.ipkCommanTrackingID
  inner join wlt_tblDevices d on d.ImeiNumber = ct.vpkDeviceID
  inner join dbo.wlt_tblAssets a on a.Id = d.ifk_AssignedAssetId
  inner join wlt_tblAlerts al on an.ifkAlertID = al.ipkAlertID
  WHERE
  an.bOnScreenNotification = 1
  and ac.bIsDismissed = 0
  and ct.dDbReceivedTime between  DATEADD(HOUR, -48, GETDATE()) and getDate()
  --and an.ifkUserId = @CurrentloggedInUser
 
  ORDER BY ct.dGPSDateTime DESC
0
 

Author Comment

by:websss
ID: 41890996
Thanks all
I used a sql performance monitor tool, here are the results

I'm think Scotts script is the most performant?
script times
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891008
Did you run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS between the different tests? Otherwise the first test won't be able to use the information in the cache and the next tests will do so.
0
 

Author Comment

by:websss
ID: 41891013
Yes I did do that
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891020
Ok. I'm expecting the differences but by your values I would say that you might have some missing indexes.
Can you provide a Query Plan? Don't need to provide a plan for each of the scripts but only from one of them.
0
 

Author Comment

by:websss
ID: 41891045
Hi vitor ,
find the attached execution plan.
ExecutionPlan.sqlplan
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891061
I guess tblCommonTrackingData.dDbReceivedTime isn't indexed, right?
You also have a warning saying there's no statistics on wlt_tblAlerts_CommentsAndDismissed.ifkAlertCapturedID. You can run the following command and then re test:
UPDATE STATISTICS wlt_tblAlerts_CommentsAndDismissed; 

Open in new window

0
 

Author Comment

by:websss
ID: 41891079
Ok thanks let me do that
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:websss
ID: 41891101
Hi Vitor,
When I use this filter  : and ct.dGPSDateTime between  DATEADD(HOUR, -48, GETDATE()) and getDate() thie script returns no data,but  when I say and ct.dGPSDateTime is not null ,then the script returns data.what colud be the issue?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41891109
It means that data will not there for that where condition. NOT NULL - means it will all the data where dGPSDateTime is there.

Also If you don't have proper indexes. Stats are not regularly updated and indexes are not rebuild regularly then there is not point in checking the performance. You can take any query, it will be slow after few days. This is because you don't have maintenance plan in place.

Every database should have maintenance plan in place. !!

Hope it helps !!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891110
What the following returns?
SELECT COUNT(1)
FROM tblCommonTrackingData 
WHERE dDbReceivedTime IS NOT NULL

SELECT COUNT(1)
FROM tblCommonTrackingData 
WHERE dDbReceivedTime < DATEADD(HOUR, -48, GETDATE()) 

Open in new window

0
 

Author Comment

by:websss
ID: 41891207
This one works < DATEADD(HOUR, -48, GETDATE())  but  >DATEADD(HOUR, -48, GETDATE())  does not work
0
 

Author Comment

by:websss
ID: 41891210
could this be an issue with the join statement because The following statement works
select * from tblCommonTrackingData where dGPSDateTime between  DATEADD(HOUR, -96, GETDATE()) and getDate()
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41891216
You can verify against the data ...
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891259
This one works < DATEADD(HOUR, -48, GETDATE())  but  >DATEADD(HOUR, -48, GETDATE())  does not work
This means you don't have data from the last 48 hours.
You can check the last entry by running the following:
SELECT MAX(dDbReceivedTime ) LastEntry
FROM tblCommonTrackingData

Open in new window

0
 

Author Comment

by:websss
ID: 41891299
The data is there: the current data is 2016-11-17 14:04:29.000 this is from SELECT MAX(dGPSDateTime ) LastEntry
FROM tblCommonTrackingData .
would you have any ideas as to why a statement like this
 ct.dDbReceivedTime between  DATEADD(HOUR, -48, GETDATE()) and getDate() would fail to return when the data is there,
any advice
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891306
Let see what happens when you join the table with wlt_tblAlerts_Captured:
SELECT MAX(ct.dDbReceivedTime ) LastEntry
FROM tblCommonTrackingData ct
    INNER JOIN wlt_tblAlerts_Captured ac ON ac.ifkCommonTrackingID = ct.ipkCommanTrackingID

Open in new window

0
 

Author Comment

by:websss
ID: 41891312
it returns this:   2016-11-17 11:30:59.000
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891320
Ok. You'll need to add the other filter and so on until you reach the moment the return date is older than 48 hours so you'll find the issue.
This should be the next test:
SELECT MAX(ct.dDbReceivedTime ) LastEntry
FROM tblCommonTrackingData ct
    INNER JOIN wlt_tblAlerts_Captured ac ON ac.ifkCommonTrackingID = ct.ipkCommanTrackingID
WHERE ac.bIsDismissed = 0

Open in new window

0
 

Author Comment

by:websss
ID: 41891372
Thank you so much Vitor,finally I have been able to come with a filter that returns the data
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41908747
websss, if your issue has been solved please select the comment or comments that helped you out solving this.
Cheers
1
 

Author Closing Comment

by:websss
ID: 41909767
Thanks vitor for your assistance.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41909815
No problem, websss but you should mark the comment with the solution and not the one you marked.
Please call for a request for attention so a moderator can reopen this question so you can mark a more appropriated comment.

Cheers
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now