Solved

SQL query with "dynamic" WHERE clause running extremely slowly

Posted on 2008-06-12
2
304 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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

627 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