Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query with "dynamic" WHERE clause running extremely slowly

Posted on 2008-06-12
2
Medium Priority
?
307 Views
Last Modified: 2009-12-16
Hi Experts,

I have a stored procedure on my SQL 2000 db that takes in many parameters, and I'm finding when I bypass the WHERE clause the stored proc is quick, but when i add a parameter or more, and the WHERE clause kicks in, it slows down the stored proc massively.

What is a clever way to write a stored procedure whereby its queries are based on several possible combinations of parameter values?

My procedure follows..

Please help! This is urgent.

Oh by the way, I do have indexes on the tables, and when I look at the execution plan, I get a sink in places where I suspect it's compairing the parameter value with a value in the table. It shows Hash Match/Right Outer Join, Cost: 33% (when I pass in a N for @All). When I pass in a Y for @All, the Hast Match/Right Outer Join Cost is 0%.

Thank you,
rss2

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/*
declare @hostname varchar(255), @rtn_val int, @showclosed varchar(10), @appquest varchar(255), @eventpriority varchar(255),@l2opmgr varchar(255), @event_creation_date_from datetime
--set @hostname = 'br41ngp02'-- 'ana*'--'analytics1-cdc'
--set @appquest = '16*'
--set @eventpriority = 'P1'
--set @showclosed = 'False'
--set @l2opmgr = 'David T Steiner/JPMCHASE'
--set @event_creation_date_from = '07/01/2008'
exec CAST_WEB_GET_WORK_QUEUE_ALERTS @rtn_val output,
'N',null,null,null,'All','All','All','All','All',null
,null,null,null,null,null,null,null
,null,null,null,null,null
,'All',null,null,'All',null
,null,null
print @rtn_val
*/
ALTER                                            PROCEDURE [dbo].[CAST_WEB_GET_WORK_QUEUE_ALERTS] (@rtn_val INT OUTPUT
, @ALL			VARCHAR(1) = 'Y'
, @alertview	 	VARCHAR(10) = NULL
, @hostname		VARCHAR(255) = NULL
, @appquest		VARCHAR(255) = NULL
, @eventpriority	VARCHAR(255) = 'All'
, @eventtype		VARCHAR(255) = 'All'
, @eventstatus		VARCHAR(255) = 'All'
, @L2OpMgr		VARCHAR(255) = 'All'
, @pendingstatus	VARCHAR(255) = 'All'
, @showclosed		VARCHAR(255) = NULL
, @event_creation_date_from	VARCHAR(255) = NULL
, @event_creation_date_to	VARCHAR(25) = NULL
, @date_email_sent_from		VARCHAR(25) = NULL
, @date_email_sent_to		VARCHAR(25) = NULL
, @date_email_received_from	VARCHAR(25) = NULL
, @date_email_received_to	VARCHAR(25) = NULL
, @next_review_date_from	VARCHAR(25) = NULL
, @next_review_date_to		VARCHAR(25) = NULL
, @date_closed_from		VARCHAR(25) = NULL
, @date_closed_to		VARCHAR(25) = NULL
, @twappname		VARCHAR(255) = NULL
, @hwmodel		VARCHAR(255) = NULL
, @os			VARCHAR(255) = 'All'
, @twprocess		VARCHAR(255) = NULL
, @notes		VARCHAR(255) = NULL
, @region		VARCHAR(255) = 'All'
, @environment		VARCHAR(255) = NULL
, @rowStart		INT = 1
, @rowEnd		INT = 2247 )
 
 AS
 
 
BEGIN
 
SET DATEFORMAT dmy
 
DECLARE @sp_name VARCHAR(255)
, @hostnameWILD  VARCHAR(255)
, @appquestWILD  VARCHAR(255)
 
SET @sp_name = 'CAST_WEB_GET_ALERTS'
 
IF CHARINDEX('*',@hostname,0)>0
BEGIN
	SET @hostnameWILD = REPLACE(@hostname,'*','')
END
 
IF CHARINDEX('*',@appquest,0)>0
BEGIN
	SET @appquestWILD = REPLACE(@appquest,'*','')
END
 
 
IF ISNULL(@showclosed,'False') = 'False'
BEGIN -- Do not include closed alerts
 
		IF ISNULL(@ALL,'Y') = 'Y'
		BEGIN
				SELECT IDENTITY(INT) RowNumber, y.*
				INTO #TMP
				FROM (SELECT DISTINCT z.Hostname
					, z.appquestID AS [AppQuest ID]
					, z.Application
					, z.last_pending_status AS [Pending Status]
					, z.event_type AS [Event Type]
					, z.event_status AS [Event Status]
					, z.event_priority AS [Event Priority]
					, z.Environment
					, z.L2_Op_Mgr AS [L2 Op Mgr]
					, z.event_creation_date AS [Event Creation Date]
					, z.date_email_sent AS [First Email Sent]
					, z.date_email_received AS [First Email Received]
					, z.last_chased_date AS [Last Chased Date]
					, z.date_last_responded AS [Date Last Responded]
					, z.next_review_date AS [Next Review Date]
					, z.Primary_Hostname
					, z.Primary_IP_Address
					, z.FQDNs
					, z.Hardware_Model
					, z.asset_type
					, z.asset_state
					, z.asset_status
					, z.OSName
					, z.Region
					, z.Server_Owner
					, z.TW_App_Name
					, z.TW_Process
					, z.Last_Login
					, z.Last_Login_Method
					, z.Host_in_TW_YesNo
					, z.Notes
				FROM	(
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_cmdb AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, '' AS TW_App_Name
						, '' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_cmdb ast ON asel.hostname + asel.appquest = ast.hostname_cmdb + ast.appquest
						INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      dbo.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN	      dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_cmdb
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
									and event_status = 'pending'
									and asel.event_type in (6, 7, 8, 9, 11) 
						
						UNION ALL
						
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_tw AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, ast.application_name_tw AS TW_App_Name
						, ' ' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_tw ast ON asel.hostname + asel.appquest = ast.hostname_tw + ast.appquest INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      DBO.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN	      dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_tw
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
									and event_status = 'pending'
								and asel.event_type in (1, 2, 3 ,4, 5, 10) 
						)z
						)y
 
						SELECT @rtn_val = @@ROWCOUNT
 
						SELECT Hostname
							, [AppQuest ID]
							, Application
							, [Pending Status]
							, [Event Type]
							, [Event Status]
							, [Event Priority]
							, Environment
							, [L2 Op Mgr]
							, [Event Creation Date]
							, [First Email Sent]
							, [First Email Received]
							, [Last Chased Date]
							, [Date Last Responded]
							, [Next Review Date]
							, Primary_Hostname
							, Primary_IP_Address
							, FQDNs
							, Hardware_Model
							, asset_type
							, asset_state
							, asset_status
							, OSName
							, Region
							, Server_Owner
							, TW_App_Name
							, TW_Process
							, Last_Login
							, Last_Login_Method
							, Host_in_TW_YesNo
							, Notes 
						FROM #TMP
						WHERE RowNumber >= @rowStart AND RowNumber <= @rowEnd
 
						DROP TABLE #TMP
 
		END
		ELSE
		BEGIN
				SELECT IDENTITY(INT) RowNumber, y.*
				INTO #TMP1
				FROM (
				SELECT DISTINCT z.Hostname
					, z.appquestID AS [AppQuest ID]
					, z.Application
					, z.last_pending_status AS [Pending Status]
					, z.event_type AS [Event Type]
					, z.event_status AS [Event Status]
					, z.event_priority AS [Event Priority]
					, z.Environment
					, z.L2_Op_Mgr AS [L2 Op Mgr]
					, z.event_creation_date AS [Event Creation Date]
					, z.date_email_sent AS [First Email Sent]
					, z.date_email_received AS [First Email Received]
					, z.last_chased_date AS [Last Chased Date]
					, z.date_last_responded AS [Date Last Responded]
					, z.next_review_date AS [Next Review Date]
					, z.Primary_Hostname
					, z.Primary_IP_Address
					, z.FQDNs
					, z.Hardware_Model
					, z.asset_type
					, z.asset_state
					, z.asset_status
					, z.OSName
					, z.Region
					, z.Server_Owner
					, z.TW_App_Name
					, z.TW_Process
					, z.Last_Login
					, z.Last_Login_Method
					, z.Host_in_TW_YesNo
					, CONVERT(VARCHAR(8000),z.Notes) as Notes
				FROM	(
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_cmdb AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, ' ' AS TW_App_Name
						, ' ' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_cmdb ast ON asel.hostname + asel.appquest = ast.hostname_cmdb + ast.appquest
						INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
 
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      dbo.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN       dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_cmdb 
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
									and event_status = 'pending'
									and asel.event_type in (6, 7, 8, 9, 11) 
						
						UNION ALL
 
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_tw AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, ast.application_name_tw AS TW_App_Name
						, '' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_tw ast ON asel.hostname + asel.appquest = ast.hostname_tw + ast.appquest INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      DBO.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN       dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_tw
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
								and (event_status = 'pending' or datediff(day, event_closed_date, getdate()) < 14) 
								and asel.event_type in (1, 2, 3 ,4, 5, 10) 
						)z
				WHERE 		(z.hostname = @hostname OR ISNULL(@hostname,'') = '' OR z.hostname LIKE @hostnameWILD + '%')
					AND	(z.appquestid = @appquest OR ISNULL(@appquest,'') = '' OR z.appquestid LIKE @appquestWILD + '%')
					AND	(z.EVENT_PRIORITY = @eventpriority OR @eventpriority = 'All')
					AND	(z.event_type = @eventtype OR @eventtype = 'All')
					AND	(z.event_status = @eventstatus OR @eventstatus = 'All')
					AND	(z.L2_Op_Mgr LIKE @L2OpMgr + '%' OR @L2OpMgr = 'All')
					AND	(z.last_pending_status = @pendingstatus OR @pendingstatus = 'All')
					AND	(	
							-- From:
							(@event_creation_date_from <= z.event_creation_date OR ISNULL(@event_creation_date_from,'') = '')
							AND
							-- To:
							(z.event_creation_date <= @event_creation_date_to OR ISNULL(@event_creation_date_to,'') = '')
						)
					AND	(	--z.date_email_sent = @date_email_sent OR ISNULL(@date_email_sent,'') = '')
							-- From:
							(@date_email_sent_from <= z.date_email_sent OR ISNULL(@date_email_sent_from,'')='')
							AND
							-- To:
							(z.date_email_sent <= @date_email_sent_to OR ISNULL(@date_email_sent_to,'')='')
						)
					AND 	(	--z.date_email_received = @date_email_received OR ISNULL(@date_email_received,'') = '')
 
							-- From:
							(@date_email_received_from <= z.date_email_received OR ISNULL(@date_email_received_from,'')='')
							AND
							-- To:
							(z.date_email_received <= @date_email_received_to OR ISNULL(@date_email_received_to,'')='')
						)
					AND	(	--z.next_review_date = @next_review_date OR ISNULL(@next_review_date,'') = '')
							-- From:
							(@next_review_date_from <= z.next_review_date OR ISNULL(@next_review_date_from,'')='')
							AND
							-- To:
							(z.next_review_date <= @next_review_date_to OR ISNULL(@next_review_date_to,'')='')
						)
					AND	(	--z.event_closed_date = @date_closed OR ISNULL(@date_closed,'') = '')
							-- From:
							(@date_closed_from <= z.event_closed_date OR ISNULL(@date_closed_from,'')='')
							AND
							(z.event_closed_date <= @date_closed_to OR ISNULL(@date_closed_to,'')='')
						)
					AND	(z.Application = @twappname OR ISNULL(@twappname,'') = '')
					AND	(z.OSName = @OS OR @OS = 'All')
					AND	(z.Region = @Region OR @Region = 'All')
					AND	(z.Notes = @notes OR ISNULL(@notes,'') = '')
		
 
					)y
 
						SELECT @rtn_val = @@ROWCOUNT
 
						SELECT Hostname
							, [AppQuest ID]
							, Application
							, [Pending Status]
							, [Event Type]
							, [Event Status]
							, [Event Priority]
							, Environment
							, [L2 Op Mgr]
							, [Event Creation Date]
							, [First Email Sent]
							, [First Email Received]
							, [Last Chased Date]
							, [Date Last Responded]
							, [Next Review Date]
							, Primary_Hostname
							, Primary_IP_Address
							, FQDNs
							, Hardware_Model
							, asset_type
							, asset_state
							, asset_status
							, OSName
							, Region
							, Server_Owner
							, TW_App_Name
							, TW_Process
							, Last_Login
							, Last_Login_Method
							, Host_in_TW_YesNo
							, Notes 
						FROM #TMP1
						WHERE RowNumber >= @rowStart AND RowNumber <= @rowEnd
 
						DROP TABLE #TMP1
		
		END	
 
END
ELSE -- Include closed alerts
BEGIN
 
		IF ISNULL(@ALL,'Y') = 'Y'
		BEGIN
				SELECT IDENTITY(INT) RowNumber, y.*
				INTO #TMP2
				FROM (
				SELECT DISTINCT z.Hostname
					, z.appquestID AS [AppQuest ID]
					, z.Application
					, z.last_pending_status AS [Pending Status]
					, z.event_type AS [Event Type]
					, z.event_status AS [Event Status]
					, z.event_priority AS [Event Priority]
					, z.Environment
					, z.L2_Op_Mgr AS [L2 Op Mgr]
					, z.event_creation_date AS [Event Creation Date]
					, z.date_email_sent AS [First Email Sent]
					, z.date_email_received AS [First Email Received]
					, z.last_chased_date AS [Last Chased Date]
					, z.date_last_responded AS [Date Last Responded]
					, z.next_review_date AS [Next Review Date]
					, z.Primary_Hostname
					, z.Primary_IP_Address
					, z.FQDNs
					, z.Hardware_Model
					, z.asset_type
					, z.asset_state
					, z.asset_status
					, z.OSName
					, z.Region
					, z.Server_Owner
					, z.TW_App_Name
					, z.TW_Process
					, z.Last_Login
					, z.Last_Login_Method
					, z.Host_in_TW_YesNo
					, CONVERT(VARCHAR(8000),z.Notes) as Notes
				FROM	(
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_cmdb AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, ' ' AS TW_App_Name
						, ' ' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_cmdb ast ON asel.hostname + asel.appquest = ast.hostname_cmdb + ast.appquest
						INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      dbo.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN       dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_cmdb
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
--									and ((event_status = 'closed' and datediff(day,event_closed_date,getdate()) < 14) or event_status  = 'open' or event_status = 'pending' or datediff(day, event_closed_date, getdate()) < 14) 
									and event_status = 'pending'
									-- Include closed alerts
									and asel.event_type in (6, 7, 8, 9, 11) 
						
						UNION ALL
						
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_tw AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, ast.application_name_tw AS TW_App_Name
						, ' ' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_tw ast ON asel.hostname + asel.appquest = ast.hostname_tw + ast.appquest INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      DBO.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN       dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_tw
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
									-- Include closed alerts
--									and ((event_status = 'closed' and datediff(day,event_closed_date,getdate()) < 14) or event_status  = 'open' or event_status = 'pending' or datediff(day, event_closed_date, getdate()) < 14) 
									and event_status = 'pending'
									and asel.event_type in (1, 2, 3 ,4, 5, 10) 
						)z
						)y
 
						SELECT @rtn_val = @@ROWCOUNT
 
						SELECT Hostname
							, [AppQuest ID]
							, Application
							, [Pending Status]
							, [Event Type]
							, [Event Status]
							, [Event Priority]
							, Environment
							, [L2 Op Mgr]
							, [Event Creation Date]
							, [First Email Sent]
							, [First Email Received]
							, [Last Chased Date]
							, [Date Last Responded]
							, [Next Review Date]
							, Primary_Hostname
							, Primary_IP_Address
							, FQDNs
							, Hardware_Model
							, asset_type
							, asset_state
							, asset_status
							, OSName
							, Region
							, Server_Owner
							, TW_App_Name
							, TW_Process
							, Last_Login
							, Last_Login_Method
							, Host_in_TW_YesNo
							, Notes 
						FROM #TMP2
						WHERE RowNumber >= @rowStart AND RowNumber <= @rowEnd
 
						DROP TABLE #TMP2
		END
		ELSE
		BEGIN
				SELECT IDENTITY(INT) RowNumber, y.*
				INTO #TMP3
				FROM (
				SELECT DISTINCT z.Hostname
					, z.appquestID AS [AppQuest ID]
					, z.Application
					, z.last_pending_status AS [Pending Status]
					, z.event_type AS [Event Type]
					, z.event_status AS [Event Status]
					, z.event_priority AS [Event Priority]
					, z.Environment
					, z.L2_Op_Mgr AS [L2 Op Mgr]
					, z.event_creation_date AS [Event Creation Date]
					, z.date_email_sent AS [First Email Sent]
 
					, z.date_email_received AS [First Email Received]
					, z.last_chased_date AS [Last Chased Date]
					, z.date_last_responded AS [Date Last Responded]
					, z.next_review_date AS [Next Review Date]
					, z.Primary_Hostname
					, z.Primary_IP_Address
					, z.FQDNs
					, z.Hardware_Model
					, z.asset_type
					, z.asset_state
					, z.asset_status
					, z.OSName
					, z.Region
					, z.Server_Owner
					, z.TW_App_Name
					, z.TW_Process
					, z.Last_Login
					, z.Last_Login_Method
					, z.Host_in_TW_YesNo
					, z.Notes
				FROM	(
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_cmdb AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, ' ' AS TW_App_Name
						, ' ' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_cmdb ast ON asel.hostname + asel.appquest = ast.hostname_cmdb + ast.appquest
						INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      dbo.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN       dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_cmdb
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
									-- Include closed alerts
--									and ((event_status = 'closed' and datediff(day,event_closed_date,getdate()) < 14) or event_status  = 'open' or event_status = 'pending' or datediff(day, event_closed_date, getdate()) < 14) 
									and event_status = 'pending'
									and asel.event_type in (6, 7, 8, 9, 11) 
						
						UNION ALL
						
						SELECT     ast.appquest AS AppquestID
						, sba.BusinessApplication as Application
						, ast.hostname_tw AS Hostname
						,  asct.[L2 Operate Manager Email] as L2_Op_Mgr
						,   asct.[L2 Operate Manager Name] as App_Owner
						, asel.event_status
						, aset.event_description as Event_Type
						, ev.EVENT_PRIORITY
						, asel.last_pending_status
						, asel.event_creation_date
						, asel.event_closed_date
						, asel.date_email_sent
						, asel.date_email_received
						, asel.last_chased_date
						, asel.date_last_responded
						, asel.next_review_date
						, hs.internalhostname AS Primary_Hostname
						, hs.BestIP AS Primary_IP_Address
						, ast.FQDN AS FQDNs
						, ast.environment
						, hs.model AS Hardware_Model
						, ast.asset_type_ims AS Asset_Type
						, ast.asset_state_ims AS Asset_State
						, ast.asset_status_ims AS Asset_Status
						, hs.OSName
						, hs.Region
						, hs.owner_contact AS Server_Owner
						, ast.application_name_tw AS TW_App_Name
						, ' ' AS TW_Process
						, hs.Last_Login
						, hs.Last_Login_Method
						, ast.relationship_status_tw AS Host_in_TW_YesNo
						,LEFT(asel.Notes,20) AS NOTES
						FROM         dbo.app_status_events_log asel 
						INNER JOIN
						                      dbo.app_status_tw ast ON asel.hostname + asel.appquest = ast.hostname_tw + ast.appquest INNER JOIN
						                      dbo.APP_STATUS_EVENT_TYPES aset ON asel.event_type = aset.event_ref 
						LEFT OUTER JOIN
						                      dbo.app_status_relationship_exceptions asre ON ast.hostname_tw + ast.appquest = asre.hostname + asre.appquest 
						LEFT OUTER JOIN
						                      dbo.app_status_contacts asct ON asel.appquest = asct.[App ID] 
						LEFT OUTER JOIN
						                      dbo.APP_STATUS_NOTES asno ON asel.hostname + asel.appquest = asno.HOSTNAME + asno.APPQUEST_ID
						LEFT OUTER JOIN 
								      DBO.APP_STATUS_PROCESS_STRINGS ASPS ON asel.hostname + asel.appquest = asps.hostname + asps.appquest_id
						LEFT OUTER JOIN	      dbo.[source-businessapplications] sba ON sba.appquest+sba.hostname = asel.appquest+asel.hostname
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_MAP map ON map.event_type = asel.event_type
						LEFT OUTER JOIN	      dbo.CAST_WEB_EVENT_PRIORITY_TYPE ev ON ev.evID = map.evID
						LEFT OUTER JOIN       dbo.HOST_STATUS hs ON hs.hostname = ast.hostname_tw
						WHERE     (
						                      ISNULL(ast.environment, '') LIKE '%prod%' OR
						                      ISNULL(ast.environment, '') LIKE '%bcp%')
									-- Include closed alerts
--									and ((event_status = 'closed' and datediff(day,event_closed_date,getdate()) < 14) or event_status  = 'open' or event_status = 'pending' or datediff(day, event_closed_date, getdate()) < 14) 
									and event_status = 'pending'
									and asel.event_type in (1, 2, 3 ,4, 5, 10) 
						)z
		
					WHERE 	(z.hostname = @hostname OR ISNULL(@hostname,'') = '' OR z.hostname LIKE @hostnameWILD + '%')
					AND	(z.appquestid = @appquest OR ISNULL(@appquest,'') = '' OR z.appquestid LIKE @appquestWILD + '%')
					AND	(z.EVENT_PRIORITY = @eventpriority OR @eventpriority = 'All')
					AND	(z.event_type = @eventtype OR @eventtype = 'All')
					AND	(z.event_status = @eventstatus OR @eventstatus = 'All')
					AND	(z.L2_Op_Mgr LIKE @L2OpMgr + '%' OR @L2OpMgr = 'All')
					AND	(z.last_pending_status = @pendingstatus OR @pendingstatus = 'All')
					AND	(	
							-- From:
							(@event_creation_date_from <= z.event_creation_date OR ISNULL(@event_creation_date_from,'') = '')
							AND
							-- To:
							(z.event_creation_date <= @event_creation_date_to OR ISNULL(@event_creation_date_to,'') = '')
						)
					AND	(	
							-- From:
							(@date_email_sent_from <= z.date_email_sent OR ISNULL(@date_email_sent_from,'')='')
							AND
							-- To:
							(z.date_email_sent <= @date_email_sent_to OR ISNULL(@date_email_sent_to,'')='')
						)
					AND 	(	
							-- From:
							(@date_email_received_from <= z.date_email_received OR ISNULL(@date_email_received_from,'')='')
							AND
							-- To:
							(z.date_email_received <= @date_email_received_to OR ISNULL(@date_email_received_to,'')='')
						)
					AND	(	
							-- From:
							(@next_review_date_from <= z.next_review_date OR ISNULL(@next_review_date_from,'')='')
							AND
							-- To:
							(z.next_review_date <= @next_review_date_to OR ISNULL(@next_review_date_to,'')='')
						)
					AND	(	
							-- From:
							(@date_closed_from <= z.event_closed_date OR ISNULL(@date_closed_from,'')='')
							AND
							(z.event_closed_date <= @date_closed_to OR ISNULL(@date_closed_to,'')='')
						)
					AND	(z.Application = @twappname OR ISNULL(@twappname,'') = '')
					AND	(z.OSName = @OS OR @OS = 'All')
					AND	(z.Region = @Region OR @Region = 'All')
					AND	(z.Notes = @notes OR ISNULL(@notes,'') = '')
					)y
 
						SELECT @rtn_val = @@ROWCOUNT
 
						SELECT Hostname
							, [AppQuest ID]
							, Application
							, [Pending Status]
							, [Event Type]
							, [Event Status]
							, [Event Priority]
							, Environment
							, [L2 Op Mgr]
							, [Event Creation Date]
							, [First Email Sent]
							, [First Email Received]
							, [Last Chased Date]
							, [Date Last Responded]
							, [Next Review Date]
							, Primary_Hostname
							, Primary_IP_Address
							, FQDNs
							, Hardware_Model
							, asset_type
							, asset_state
							, asset_status
							, OSName
							, Region
							, Server_Owner
							, TW_App_Name
							, TW_Process
							, Last_Login
							, Last_Login_Method
							, Host_in_TW_YesNo
							, Notes 
						FROM #TMP3
						WHERE RowNumber >= @rowStart AND RowNumber <= @rowEnd
 
						DROP TABLE #TMP3
		
		
		
		END	
 
 
 
END
 
IF @rtn_val <> 0 GOTO ErrHandler	
 
RETURN @rtn_val
 
ErrHandler:
EXEC dbo.CAST_WEB_LOG @sp_name, @rtn_val
RETURN @rtn_val
 
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

0
Comment
Question by:rss2
[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
2 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 21771589
ISNULL(ast.environment, '') LIKE '%prod%' OR
ISNULL(ast.environment, '') LIKE '%bcp%')

This is performance killer. No index can be used. Have you tried removing those 2 criterias?
0
 

Author Comment

by:rss2
ID: 21829676
You're absolutely right. Many many thanks!!!

I took an enitrely different direction. I'm not using devexpress's controls which are very fast despite all their niftiness.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

661 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