rss2
asked on
SQL query with "dynamic" WHERE clause running extremely slowly
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
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
GOASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I took an enitrely different direction. I'm not using devexpress's controls which are very fast despite all their niftiness.