Solved

SQL query with "dynamic" WHERE clause running extremely slowly

Posted on 2008-06-12
2
297 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
2 Comments
 
LVL 69

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now