SQL Performance optimisation - selecting as a column

Better provide us the full query otherwise will can only guess.

Please provide full query ?

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

Are you having performance issues right now?

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

Select allOpen in new window

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

Thanks all
I used a sql performance monitor tool, here are the results

I'm think Scotts script is the most performant?

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.

Yes I did do that

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.

Hi vitor ,
find the attached execution plan.
ExecutionPlan.sqlplan

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; 

Select allOpen in new window

Ok thanks let me do that

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?

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 !!

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()) 

Select allOpen in new window

This one works < DATEADD(HOUR, -48, GETDATE())  but  >DATEADD(HOUR, -48, GETDATE())  does not work

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()

You can verify against the data ...

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

Select allOpen in new window

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

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

Select allOpen in new window

it returns this:   2016-11-17 11:30:59.000

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

Select allOpen in new window

Thank you so much Vitor,finally I have been able to come with a filter that returns the data

Thanks vitor for your assistance.

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