sgmunson (2/18/2015)

---

Velveeta22 (2/17/2015)

---

Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope is the simplified logic removing CROSS JOIN and CROSS APPLY will tackle whatever the timeout issue was.

DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';

DECLARE @p1 INT = 1;

DECLARE @p2 INT = 1;

DECLARE @p3 NVARCHAR(4000) = 1;

DECLARE @p4 INT = 1;

DECLARE @p5 NVARCHAR(4000) = 1;

DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';

DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';

DECLARE @p8 INT = 1;

DECLARE @p9 INT = 1;

DECLARE @p10 INT = 1;

DECLARE @p11 INT = 801;

SELECT *

FROM (

SELECT

O.Destination,

O.Message,

O.OutboxAN,

O.OutboxStatusAN,

O.OutboxTypeAN,

O.Subject,

OBD.Fax,

OBD.FileName,

OBD.OutBoxData AS OutBoxData1, -- Necessary to change header of column to OutBoxData1?

OBD.OutBoxDataAN,

OBD.Type,

O.ClientReferenceID,

ISNULL(O.CompanyClientMemberID, @p1) AS CompanyClientMemberID,

ISNULL(O.ContactClientMemberID, @p2) AS ContactClientMemberID,

ISNULL(O.ReportType, @p3) AS ReportType,

ISNULL(CM.ClientRoleID, @p4) AS CompanyClientRoleID -- only because ClientRoleID is not nullable

FROM dbo.Outbox AS O

INNER JOIN dbo.OutBoxData AS OBD

ON OBD.OutBoxAN = O.OutboxAN

LEFT JOIN dbo.ClientMember CM

ON CM.ClientMemberID = O.CompanyClientMemberID

AND CM.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'

WHERE O.OutboxStatusAN = @p11

AND O.CompanyClientMemberID IS NOT NULL -- Not firm on the business rules, is this condition required (suspect)?

) DataSet

WHERE

NOT EXISTS(

SELECT 1

FROM dbo.MemberOther MO

INNER JOIN dbo.ClientMember CM1 -- same ClientID as DataSet

ON CM1.ClientMemberID = MO.ClientMemberID

AND CM1.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'

INNER JOIN dbo.ClientRole CR -- same ClientID as DataSet

ON CR.ClientRoleID = CM1.ClientRoleID

INNER JOIN dbo.ClientRole CR1 -- same ClientID as DataSet

ON CR1.ClientID = CR.ClientID

AND CR1.DefaultBeginLevel = @p8

AND CR1.ClientRoleID = DataSet.CompanyClientRoleID -- DataSet.CompanyClientRoleID is Dat.ClientRoleID

WHERE MO.TheValue = 1

AND MO.Description = @p5

AND MO.EndEffectiveDate = @p6) -- '2099-09-09 00:00:00.000'

OR DataSet.OutboxTypeAN NOT IN (@p9,@p10)

For how small the tables are, I feel like the query has poor execution times at 6 seconds. Query plan attached, clearly has issues with indexes. Thoughts?

Did you notice the "Missing Index (Impact 32.3606): ..." notification in that SQL Plan ? Here's the recommendation:

/*

Missing Index Details from Updated_Query.sqlplan

The Query Processor estimates that implementing the following index could improve the query cost by 32.3606%.

*/

/*

USE [RedArrow]

GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[MemberOther] ([Description],[TheValue],[EndEffectiveDate])

INCLUDE ([ClientMemberID])

GO

*/

And ChrisM - This is the first .sqlplan file I've ever seen that adds up in percentages to well over 200%. Does that have anything to do with your response to the same post I'm responding to ?