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

x
?
Solved

SQL Performance optimisation - selecting as a column

Posted on 2016-11-16
29
Medium Priority
?
92 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
[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
  • 12
  • 11
  • 5
  • +1
29 Comments
 
LVL 52

Expert Comment

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

Expert Comment

by:Pawan Kumar
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
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.

 
LVL 32

Expert Comment

by:Pawan Kumar
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 52

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 32

Expert Comment

by:Pawan Kumar
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 70

Expert Comment

by:Scott Pletcher
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 52

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 52

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 52

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
 

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 32

Expert Comment

by:Pawan Kumar
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 52

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 32

Expert Comment

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

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 52

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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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 52

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

597 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