SQL Performance optimisation - selecting as a column

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
websssAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
websss, if your issue has been solved please select the comment or comments that helped you out solving this.
Cheers
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Better provide us the full query otherwise will can only guess.
0
 
Pawan KumarDatabase ExpertCommented:
Please provide full query ?
0
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.

 
websssAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
websssAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
websssAuthor Commented:
Yes I did do that
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
websssAuthor Commented:
Hi vitor ,
find the attached execution plan.
ExecutionPlan.sqlplan
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
websssAuthor Commented:
Ok thanks let me do that
0
 
websssAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
websssAuthor Commented:
This one works < DATEADD(HOUR, -48, GETDATE())  but  >DATEADD(HOUR, -48, GETDATE())  does not work
0
 
websssAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
You can verify against the data ...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
websssAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
websssAuthor Commented:
it returns this:   2016-11-17 11:30:59.000
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
websssAuthor Commented:
Thank you so much Vitor,finally I have been able to come with a filter that returns the data
0
 
websssAuthor Commented:
Thanks vitor for your assistance.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
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.

All Courses

From novice to tech pro — start learning today.