Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

Question

Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

Viewing 15 posts - 31 through 45 (of 55 total)

You must be logged in to reply to this topic. Login to reply

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 1

Another poster asked for the execution plan. I now see that you can attach .sqlplan to this forum. Very cool! The version attached is from production and shows the clustered index scan on OutBoxData at 86% of cost. The sample db I just posted shows the execution plan for that same table at 20% of cost. Am I wrong in assuming that the query is not scalable for the OutBoxData table?

Using these dummy variables at the top of the original query:

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 [t4].[Destination],

[t4].[Message],

[t4].[OutboxAN],

[t4].[OutboxStatusAN],

[t4].[OutboxTypeAN],

[t4].[Subject],

[t4].[Fax],

[t4].[FileName],

[t4].[OutBoxData] AS [OutBoxData1],

[t4].[OutBoxDataAN],

[t4].[Type],

[t4].[ClientReferenceID],

[t4].[value] AS [CompanyClientMemberID],

[t4].[value2] AS [ContactClientMemberID],

[t4].[value3] AS [ReportType],

[t4].[value4] AS [CompanyClientRoleID]

FROM (

SELECT [t0].[Destination],

[t0].[Message],

[t0].[OutboxAN],

[t0].[OutboxStatusAN],

[t0].[OutboxTypeAN],

[t0].[Subject],

[t1].[Fax],

[t1].[FileName],

[t1].[OutBoxData],

[t1].[OutBoxDataAN],

[t1].[Type],

[t0].[ClientReferenceID],

(

CASE

WHEN [t0].[CompanyClientMemberID] IS NULL

THEN @p1

ELSE [t0].[CompanyClientMemberID]

END

) AS [value],

(

CASE

WHEN [t0].[ContactClientMemberID] IS NULL

THEN @p2

ELSE [t0].[ContactClientMemberID]

END

) AS [value2],

(

CASE

WHEN [t0].[ReportType] IS NULL

THEN CONVERT(NVARCHAR(50), @p3)

ELSE [t0].[ReportType]

END

) AS [value3],

(

CASE

WHEN [t3].[test] IS NULL

THEN @p4

ELSE [t3].[ClientRoleID]

END

) AS [value4],

[t1].[OutBoxAN] AS [OutBoxAN2]

FROM [dbo].[Outbox] AS [t0]

CROSS JOIN [dbo].[OutBoxData] AS [t1]

LEFT JOIN (

SELECT 1 AS [test],

[t2].[ClientMemberID],

[t2].[ClientRoleID],

[t2].[EndEffectiveDate]

FROM [dbo].[ClientMember] AS [t2]

) AS [t3]

ON ([t0].[CompanyClientMemberID] IS NOT NULL)

AND ([t3].[ClientMemberID] = ([t0].[CompanyClientMemberID]))

AND ([t3].[EndEffectiveDate] = @p0)

) AS [t4]

CROSS APPLY (

SELECT TOP (1) [t10].[test]

FROM (

SELECT NULL AS [EMPTY]

) AS [t5]

LEFT JOIN (

SELECT 1 AS [test]

FROM [dbo].[MemberOther] AS [t6],

[dbo].[ClientMember] AS [t7],

[dbo].[ClientRole] AS [t8],

[dbo].[ClientRole] AS [t9]

WHERE ([t9].[ClientRoleID] = [t4].[value4])

AND ([t6].[TheValue] = 1)

AND ([t6].[Description] = @p5)

AND ([t6].[EndEffectiveDate] = @p6)

AND ([t7].[ClientMemberID] = [t6].[ClientMemberID])

AND ([t7].[EndEffectiveDate] = @p7)

AND ([t8].[ClientRoleID] = [t7].[ClientRoleID])

AND ([t9].[ClientID] = [t8].[ClientID])

AND ([t9].[DefaultBeginLevel] = @p8)

) AS [t10]

ON 1 = 1

) AS [t11]

WHERE (

([t11].[test] IS NULL)

OR (

([t11].[test] IS NOT NULL)

AND ([t4].[OutboxTypeAN] <> @p9)

AND ([t4].[OutboxTypeAN] <> @p10)

)

AND ([t4].[OutboxStatusAN] = @p11)

AND ([t4].[OutBoxAN2] = ([t4].[OutboxAN]))

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Let me know if the following simplified version of that previous query is functionally equivalent:

DECLARE @p0 DATETIME = '2013-12-23 15:09:31.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 = '2013-12-23 15:09:31.000';

DECLARE @p7 DATETIME = '2013-12-23 15:09:31.000';

DECLARE @p8 INT = 1;

DECLARE @p9 INT = 1;

DECLARE @p10 INT = 1;

DECLARE @p11 INT = 1;

SELECT [t4].[Destination],

[t4].[Message],

[t4].[OutboxAN],

[t4].[OutboxStatusAN],

[t4].[OutboxTypeAN],

[t4].[Subject],

[t4].[Fax],

[t4].[FileName],

[t4].[OutBoxData] AS [OutBoxData1],

[t4].[OutBoxDataAN],

[t4].[Type],

[t4].[ClientReferenceID],

[t4].[value] AS [CompanyClientMemberID],

[t4].[value2] AS [ContactClientMemberID],

[t4].[value3] AS [ReportType],

[t4].[value4] AS [CompanyClientRoleID]

FROM (

SELECT [t0].[Destination],

[t0].[Message],

[t0].[OutboxAN],

[t0].[OutboxStatusAN],

[t0].[OutboxTypeAN],

[t0].[Subject],

[t1].[Fax],

[t1].[FileName],

[t1].[OutBoxData],

[t1].[OutBoxDataAN],

[t1].[Type],

[t0].[ClientReferenceID],

ISNULL([t0].[CompanyClientMemberID], @p1) AS [value],

ISNULL([t0].[ContactClientMemberID], @p2) AS [value2],

ISNULL([t0].[ReportType], CONVERT(NVARCHAR(50), @p3)) AS [value3],

CASE

WHEN [t2].[ClientMemberID] IS NULL THEN @p4

ELSE [t2].[ClientRoleID]

END AS [value4],

[t1].[OutBoxAN] AS [OutBoxAN2]

FROM [dbo].[Outbox] AS [t0]

CROSS JOIN [dbo].[OutBoxData] AS [t1]

LEFT JOIN [dbo].[ClientMember] AS [t2]

ON [t0].[CompanyClientMemberID] = [t2].[ClientMemberID]

AND [t2].[EndEffectiveDate] = @p0

) AS [t4]

LEFT JOIN (

SELECT TOP (1) 1 AS [test]

FROM [dbo].[MemberOther] AS [t6]

INNER JOIN [dbo].[ClientMember] AS [t7]

ON [t7].[ClientMemberID] = [t6].[ClientMemberID]

AND [t7].[EndEffectiveDate] = @p7

INNER JOIN [dbo].[ClientRole] AS [t8]

ON [t8].[ClientRoleID] = [t7].[ClientRoleID]

INNER JOIN [dbo].[ClientRole] AS [t9]

ON [t9].[ClientID] = [t8].[ClientID]

AND [t9].[DefaultBeginLevel] = @p8

WHERE [t6].[TheValue] = 1

AND [t6].[Description] = @p5

AND [t6].[EndEffectiveDate] = @p6

) AS [t10]

ON 1 = [t10].[test]

WHERE (

[t10].[test] IS NULL

OR (

[t10].[test] IS NOT NULL

AND [t4].[OutboxTypeAN] NOT IN (@p9, @p10)

)

AND [t4].[OutboxStatusAN] = @p11

AND [t4].[OutBoxAN2] = [t4].[OutboxAN]

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Velveeta22 (2/13/2015)
This will install a sample db, tables and some objects. I put around 20 rows of anonymized data in each table:
...[/code]

Thanks for this - there are a few inconsistencies in your sample data which you might like to fix, it would certainly help folks if you do. If you run any of the queries against this dataset, no results are returned. A couple of tweaks to the data

UPDATE [dbo].[OutBoxData] SET OutboxAN = ISNULL(OutboxDataAN,0)+1

UPDATE dbo.Outbox SET CompanyClientMemberID = CASE CompanyClientMemberID WHEN 261127 THEN 1 WHEN 261129 THEN 2 WHEN 261441 THEN 3 WHEN 261445 THEN 4 WHEN 263940 THEN 5 END

and to the parameter list

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 = '2013-12-23 15:09:31.000';

DECLARE @p7 DATETIME = '2013-12-23 15:09:31.000';

DECLARE @p8 INT = 1;

DECLARE @p9 INT = 1;

DECLARE @p10 INT = 1;

DECLARE @p11 INT = 801;

-provides a dataset which is at least good enough to test the t4 part of your query.

Here's your version and mine:

SELECT *

FROM (

SELECT [t0].[Destination],

[t0].[Message],

[t0].[OutboxAN], -- [t4].[OutboxAN]

[t0].[OutboxStatusAN],

[t0].[OutboxTypeAN],

[t0].[Subject],

[t1].[Fax],

[t1].[FileName],

[t1].[OutBoxData],

[t1].[OutBoxDataAN],

[t1].[Type],

[t0].[ClientReferenceID],

(

CASE

WHEN [t0].[CompanyClientMemberID] IS NULL

THEN @p1

ELSE [t0].[CompanyClientMemberID]

END

) AS [value],

(

CASE

WHEN [t0].[ContactClientMemberID] IS NULL

THEN @p2

ELSE [t0].[ContactClientMemberID]

END

) AS [value2],

(

CASE

WHEN [t0].[ReportType] IS NULL

THEN CONVERT(NVARCHAR(50), @p3)

ELSE [t0].[ReportType]

END

) AS [value3],

(

CASE

WHEN [t3].[test] IS NULL

THEN @p4

ELSE [t3].[ClientRoleID]

END

) AS [value4],

[t1].[OutBoxAN] AS [OutBoxAN2] -- [t4].[OutBoxAN2]

FROM [dbo].[Outbox] AS [t0]

CROSS JOIN [dbo].[OutBoxData] AS [t1]

LEFT JOIN (

SELECT 1 AS [test],

[t2].[ClientMemberID],

[t2].[ClientRoleID],

[t2].[EndEffectiveDate]

FROM [dbo].[ClientMember] AS [t2]

) AS [t3]

ON ([t0].[CompanyClientMemberID] IS NOT NULL)

AND ([t3].[ClientMemberID] = ([t0].[CompanyClientMemberID]))

AND ([t3].[EndEffectiveDate] = @p0)

) AS [t4]

WHERE ([t4].[OutBoxAN2] = ([t4].[OutboxAN]))

---------------------------------------------------------------------------------------------

SELECT

t0.Destination,

t0.[Message],

t0.OutboxAN,

t0.OutboxStatusAN,

t0.OutboxTypeAN,

t0.[Subject],

t1.Fax,

t1.[FileName],

t1.OutBoxData,

t1.OutBoxDataAN,

t1.[Type],

t0.ClientReferenceID,

[value] = ISNULL(t0.CompanyClientMemberID, @p1),

[value2] = ISNULL(t0.ContactClientMemberID, @p2),

[value3] = ISNULL(t0.ReportType, @p3),

[value4] = t3.ClientRoleID,

[OutBoxAN2] = t1.OutBoxAN

FROM dbo.Outbox AS t0

INNER JOIN dbo.OutBoxData AS t1

ON t1.OutBoxAN = t0.OutboxAN

INNER JOIN dbo.ClientMember t3

ON t3.ClientMemberID = t0.CompanyClientMemberID

AND t3.EndEffectiveDate = @p0

WHERE t0.OutboxStatusAN = @p11

15 rows are returned by each query and the results are the same. Also, the CROSS JOIN is converted into a nested loops inner join, exactly as you would expect.

I'm sure we can finish this one off quite quickly if you can fix your sample dataset so that folks can tackle the CROSS APPLY part.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 4

I have updated the data set and DECLARE variables in my posts. Thank you for those tweaks!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Velveeta22 (2/14/2015)
I have updated the data set and DECLARE variables in my posts. Thank you for those tweaks!

You're welcome - and thanks for the updates. I won't get near it now until tomorrow morning but I'm sure Steve, Jeff or someone will have a play.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 6

The most obvious problem in the Execution Plan that you provided are the two warnings (the Yellow triangles on two tables). If you hover over those icons, you'll see the warnings are about missing statistics and it'll tell you which indexes need the statistics. Before doing anything else, you need to fix those and then rerun the query with the Actual Execution plan turned on. If there's still a problem, attach that ACTUAL execution plan to a new post on this thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 7

I am not seeing any yellow yields on that previous plan, do I have an option turned off in SSMS? Index reorg/rebuild script ran over the weekend and I re-ran the query in production (.sqlplan attached). It took 6 seconds to complete which does not seem efficient, yet not terrible.

In this version the Clustered Index Seek on OutboxData is 91% of plan cost. 13,239 executions, table holds 92k rows (3 MB).

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 8

sgmunson (2/13/2015)
Let me know if the following simplified version of that previous query is functionally equivalent:
DECLARE @p0 DATETIME = '2013-12-23 15:09:31.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 = '2013-12-23 15:09:31.000';
DECLARE @p7 DATETIME = '2013-12-23 15:09:31.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 1;
SELECT [t4].[Destination],
[t4].[Message],
[t4].[OutboxAN],
[t4].[OutboxStatusAN],
[t4].[OutboxTypeAN],
[t4].[Subject],
[t4].[Fax],
[t4].[FileName],
[t4].[OutBoxData] AS [OutBoxData1],
[t4].[OutBoxDataAN],
[t4].[Type],
[t4].[ClientReferenceID],
[t4].[value] AS [CompanyClientMemberID],
[t4].[value2] AS [ContactClientMemberID],
[t4].[value3] AS [ReportType],
[t4].[value4] AS [CompanyClientRoleID]
FROM (
SELECT [t0].[Destination],
[t0].[Message],
[t0].[OutboxAN],
[t0].[OutboxStatusAN],
[t0].[OutboxTypeAN],
[t0].[Subject],
[t1].[Fax],
[t1].[FileName],
[t1].[OutBoxData],
[t1].[OutBoxDataAN],
[t1].[Type],
[t0].[ClientReferenceID],
ISNULL([t0].[CompanyClientMemberID], @p1) AS [value],
ISNULL([t0].[ContactClientMemberID], @p2) AS [value2],
ISNULL([t0].[ReportType], CONVERT(NVARCHAR(50), @p3)) AS [value3],
CASE
WHEN [t2].[ClientMemberID] IS NULL THEN @p4
ELSE [t2].[ClientRoleID]
END AS [value4],
[t1].[OutBoxAN] AS [OutBoxAN2]
FROM [dbo].[Outbox] AS [t0]
CROSS JOIN [dbo].[OutBoxData] AS [t1]
LEFT JOIN [dbo].[ClientMember] AS [t2]
ON [t0].[CompanyClientMemberID] = [t2].[ClientMemberID]
AND [t2].[EndEffectiveDate] = @p0
) AS [t4]
LEFT JOIN (
SELECT TOP (1) 1 AS [test]
FROM [dbo].[MemberOther] AS [t6]
INNER JOIN [dbo].[ClientMember] AS [t7]
ON [t7].[ClientMemberID] = [t6].[ClientMemberID]
AND [t7].[EndEffectiveDate] = @p7
INNER JOIN [dbo].[ClientRole] AS [t8]
ON [t8].[ClientRoleID] = [t7].[ClientRoleID]
INNER JOIN [dbo].[ClientRole] AS [t9]
ON [t9].[ClientID] = [t8].[ClientID]
AND [t9].[DefaultBeginLevel] = @p8
WHERE [t6].[TheValue] = 1
AND [t6].[Description] = @p5
AND [t6].[EndEffectiveDate] = @p6
) AS [t10]
ON 1 = [t10].[test]
WHERE (
[t10].[test] IS NULL
OR (
[t10].[test] IS NOT NULL
AND [t4].[OutboxTypeAN] NOT IN (@p9, @p10)
)
)
AND [t4].[OutboxStatusAN] = @p11
AND [t4].[OutBoxAN2] = [t4].[OutboxAN]

Good question on functional equivalency. I don't have all the business rules from the 3rd party, but I think this is simply a batch process that pulls Outbox.OutboxAN records that do not have a status of "pending". That seems to CROSS APPLY with the first record returned in T11 dataset, which looks to be overly complicated code to find a client with certain variables. The returned records will go back to the batch and send an email with some information with it and then the status changes to complete or another status.

I am going to re-write the whole thing.

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 9

This produces the same result set in both the test environment and production. Does anyone see any logic fail on the re-write?

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 = '2013-12-23 15:09:31.000';

DECLARE @p7 DATETIME = '2013-12-23 15:09:31.000';

DECLARE @p8 INT = 1;

DECLARE @p9 INT = 1;

DECLARE @p10 INT = 1;

DECLARE @p11 INT = 801;

SELECT

O.Destination,

O.Message,

O.OutboxAN,

O.OutboxStatusAN,

O.OutboxTypeAN,

O.Subject,

OBD.Fax,

OBD.FileName,

OBD.OutBoxData,

OBD.OutBoxDataAN,

OBD.Type,

O.ClientReferenceID,

[CompanyClientMemberID] = ISNULL(O.CompanyClientMemberID, @p1),

[ContactClientMemberID] = ISNULL(O.ContactClientMemberID, @p2),

[ReportType] = ISNULL(O.ReportType, @p3),

[CompanyClientRoleID] = ISNULL(CM.ClientRoleID, @p4)

FROM dbo.Outbox AS O

JOIN dbo.OutBoxData AS OBD

ON OBD.OutBoxAN = O.OutboxAN

LEFT JOIN dbo.ClientMember CM

ON CM.ClientMemberID = O.CompanyClientMemberID

AND CM.EndEffectiveDate = @p0

WHERE O.OutboxStatusAN = @p11

I then ran an execution plan in prod and OutboxData is 98% of the plan.

sgmunson SSC Guru Points: 110640 More actions · Answer 10

The following part of the LEFT JOIN isn't needed:

AND O.CompanyClientMemberID IS NOT NULL

That field is already part of the rest of the JOIN condition (the specific line follows) based on equality with the ClientMemberID field of the joined table, which NULL values can never achieve, not even with other NULL values.

ON CM.ClientMemberID = O.CompanyClientMemberID

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

First off, here's a modified ddl / dml script. It's a little more compressed than the original, and possibly easier to adjust for data changes:

CREATE DATABASE OutBox;

GO

USE [OutBox]

GO

---------------------------------------------------------------------------------------------------

-- [dbo].[Outbox]

---------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Outbox](

[OutboxAN] [int] IDENTITY(1,1) NOT NULL,

[OutboxTypeAN] [int] NOT NULL,

[SendingFacilityAN] [int] NULL,

[ClientReferenceID] [nvarchar](25) NULL,

[Destination] [nvarchar](255) NULL,

[Subject] [nvarchar](255) NULL,

[Message] [nvarchar](max) NULL,

[OutboxStatusAN] [int] NOT NULL,

[ErrorDescription] [nvarchar](500) NULL,

[Attempts] [int] NULL,

[OutboxedDate] [datetime] NULL,

[ServiceRecievedDate] [datetime] NULL,

[CompanyClientMemberID] [int] NULL,

[ContactClientMemberID] [int] NULL,

[ReportType] [nvarchar](50) NULL,

[OutboxDataTypeID] [int] NULL CONSTRAINT [DF__Outbox__OutboxDa__0ADD8CFD] DEFAULT ((0)),

CONSTRAINT [PK_Outbox] PRIMARY KEY NONCLUSTERED

([OutboxAN] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_Outbox_OutboxStatusAN] ON [dbo].[Outbox]

([OutboxStatusAN] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[Outbox] ON

INSERT [dbo].[Outbox] ([OutboxAN], [OutboxTypeAN], [SendingFacilityAN], [ClientReferenceID], [Destination], [Subject], [Message], [OutboxStatusAN], [ErrorDescription], [Attempts], [OutboxedDate], [ServiceRecievedDate], [CompanyClientMemberID], [ContactClientMemberID], [ReportType], [OutboxDataTypeID])

SELECT 1, 1, NULL, N'2106', N'email@address.com', N'Report For Jeff', N'Random Selection List Created on 1/22/2015 4:54:00 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 270491, N'Random Selection', 0 UNION ALL

SELECT 2, 1, NULL, N'2106', N'email@address.com', N'Report For John', N'Notification Letters Created on 1/22/2015 4:54:00 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 270491, N'Random Selection', 0 UNION ALL

SELECT 3, 1, NULL, N'2106', N'email@address.com', N'Report For Rita', N'Random Selection List Created on 1/22/2015 4:54:00 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 275189, N'Random Selection', 0 UNION ALL

SELECT 4, 1, NULL, N'2106', N'email@address.com', N'Report For Rita', N'Notification Letters Created on 1/22/2015 4:54:00 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 275189, N'Random Selection', 0 UNION ALL

SELECT 5, 1, NULL, N'2106', N'email@address.com', N'Report For Shane', N'Random Selection List Created on 1/22/2015 4:54:01 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 276021, N'Random Selection', 0 UNION ALL

SELECT 6, 1, NULL, N'2106', N'email@address.com', N'Report For Shane', N'Notification Letters Created on 1/22/2015 4:54:01 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 276021, N'Random Selection', 0 UNION ALL

SELECT 7, 1, NULL, N'2106', N'email@address.com', N'Report For Tiffany', N'Random Selection List Created on 1/22/2015 4:54:01 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 276838, N'Random Selection', 0 UNION ALL

SELECT 8, 1, NULL, N'2106', N'email@address.com', N'Report For Tiffany', N'Notification Letters Created on 1/22/2015 4:54:01 PM', 801, N'', NULL, CAST(N'2015-01-22 00:00:00.000' AS DateTime), NULL, 5, 276838, N'Random Selection', 0 UNION ALL

SELECT 9, 1, NULL, N'4537', N'email@address.com', N'Report For Laurin', N'Random Selection List Created on 2/4/2015 1:55:20 PM', 801, NULL, NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 1, 271982, N'Random Selection', 0 UNION ALL

SELECT 10, 1, NULL, N'4537', N'email@address.com', N'Report For Laurin', N'Notification Letters Created on 2/4/2015 1:55:20 PM', 801, NULL, NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 1, 271982, N'Random Selection', 0 UNION ALL

SELECT 11, 1, NULL, N'4537', N'email@address.com', N'Report For Laurin', N'Random Selection List Created on 2/4/2015 1:55:20 PM', 801, NULL, NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 2, 271984, N'Random Selection', 0 UNION ALL

SELECT 12, 1, NULL, N'4537', N'email@address.com', N'Report For Laurin', N'Notification Letters Created on 2/4/2015 1:55:20 PM', 801, NULL, NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 2, 271984, N'Random Selection', 0 UNION ALL

SELECT 13, 1, NULL, N'4539', N'email@address.com', N'Report For Arthur', N'Notification Letters Created on 2/4/2015 2:58:08 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 4, 332585, N'Random Selection', 0 UNION ALL

SELECT 14, 1, NULL, N'4540', N'email@address.com', N'Report For Arthur', N'Notification Letters Created on 2/4/2015 2:58:15 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 4, 332585, N'Random Selection', 0 UNION ALL

SELECT 15, 1, NULL, N'4541', N'email@address.com', N'Report For Ken', N'Notification Letters Created on 2/4/2015 2:58:22 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 4, 271731, N'Random Selection', 0 UNION ALL

SELECT 16, 1, NULL, N'4542', N'email@address.com', N'Report For Ken', N'Notification Letters Created on 2/4/2015 2:58:28 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 4, 271731, N'Random Selection', 0 UNION ALL

SELECT 17, 1, NULL, N'4543', N'email@address.com', N'Report For Arthur', N'Notification Letters Created on 2/4/2015 2:58:35 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 4, 343568, N'Random Selection', 0 UNION ALL

SELECT 18, 1, NULL, N'4544', N'email@address.com', N'Report For Arthur', N'Notification Letters Created on 2/4/2015 2:58:42 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 4, 343568, N'Random Selection', 0 UNION ALL

SELECT 19, 1, NULL, N'4545', N'email@address.com', N'Report For Arthur', N'Notification Letters Created on 2/4/2015 2:58:47 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 3, 265179, N'Random Selection', 0 UNION ALL

SELECT 20, 1, NULL, N'4546', N'email@address.com', N'Report For Arthur', N'Notification Letters Created on 2/4/2015 2:58:52 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 3, 265179, N'Random Selection', 0 UNION ALL

SELECT 21, 1, NULL, N'4547', N'email@address.com', N'Report For Ken', N'Notification Letters Created on 2/4/2015 2:58:57 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 3, 271740, N'Random Selection', 0 UNION ALL

SELECT 22, 1, NULL, N'4548', N'email@address.com', N'Report For Ken', N'Notification Letters Created on 2/4/2015 2:59:03 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 3, 271740, N'Random Selection', 0 UNION ALL

SELECT 23, 1, NULL, N'4549', N'email@address.com', N'Report For Arthur', N'Notification Letters Created on 2/4/2015 2:59:08 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 3, 343570, N'Random Selection', 0 UNION ALL

SELECT 24, 1, NULL, N'4550', N'email@address.com', N'Report For Arthur.', N'Notification Letters Created on 2/4/2015 2:59:13 PM', 801, N'', NULL, CAST(N'2015-02-04 00:00:00.000' AS DateTime), NULL, 3, 343570, N'Random Selection', 0

SET IDENTITY_INSERT [dbo].[Outbox] OFF

GO

---------------------------------------------------------------------------------------------------

-- [dbo].[OutBoxData]

---------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[OutBoxData](

[OutBoxDataAN] [int] IDENTITY(1,1) NOT NULL,

[OutBoxAN] [int] NULL,

[OutBoxData] [varbinary](max) NULL,

[FileName] [nvarchar](300) NULL,

[Type] [nvarchar](30) NULL,

[Fax] [nvarchar](20) NULL,

[FileCreated] [datetime] NULL,

CONSTRAINT [PK_OutBoxData] PRIMARY KEY NONCLUSTERED

([OutBoxDataAN] ASC)

WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_OutboxData_OutboxAN] ON [dbo].[OutBoxData]

([OutBoxAN] ASC)

WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[OutBoxData] ON

INSERT [dbo].[OutBoxData] ([OutBoxDataAN], [OutBoxAN], [OutBoxData], [FileName], [Type], [Fax], [FileCreated])

SELECT 1, 2, NULL, N'Fax.pdf', N'TFAX', N'Fax', NULL UNION ALL

SELECT 2, 3, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:13.860' AS DateTime) UNION ALL

SELECT 3, 4, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:14.250' AS DateTime) UNION ALL

SELECT 4, 5, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:14.250' AS DateTime) UNION ALL

SELECT 5, 6, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:14.640' AS DateTime) UNION ALL

SELECT 6, 7, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:14.657' AS DateTime) UNION ALL

SELECT 7, 8, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:15.390' AS DateTime) UNION ALL

SELECT 8, 9, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:15.390' AS DateTime) UNION ALL

SELECT 9, 10, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:15.827' AS DateTime) UNION ALL

SELECT 10, 11, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:15.827' AS DateTime) UNION ALL

SELECT 11, 12, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:16.183' AS DateTime) UNION ALL

SELECT 12, 13, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:16.183' AS DateTime) UNION ALL

SELECT 13, 14, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:16.573' AS DateTime) UNION ALL

SELECT 14, 15, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:16.573' AS DateTime) UNION ALL

SELECT 15, 16, NULL, N'AnyPDFname.pdf', N'PDF', N'', CAST(N'2014-02-12 14:02:17.200' AS DateTime)

SET IDENTITY_INSERT [dbo].[OutBoxData] OFF

GO

---------------------------------------------------------------------------------------------------

-- [dbo].[ClientMember]

---------------------------------------------------------------------------------------------------

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[ClientMember](

[ClientMemberID] [int] IDENTITY(1,1) NOT NULL,

[ClientRoleID] [int] NOT NULL,

[MemberID] [int] NOT NULL,

[BeginEffectiveDate] [datetime] NOT NULL,

[EndEffectiveDate] [datetime] NOT NULL,

[UserLogin] [nvarchar](20) NOT NULL,

[UpdateDate] [datetime] NOT NULL,

CONSTRAINT [PK_ClientMember] PRIMARY KEY NONCLUSTERED

([ClientMemberID] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_ClientMember_MemberID] ON [dbo].[ClientMember]

([EndEffectiveDate] DESC, [ClientRoleID] ASC, [MemberID] ASC)

WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 95) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [idx_ClientMember_EndEffectiveDate] ON [dbo].[ClientMember]

([EndEffectiveDate] ASC) INCLUDE ([MemberID], [ClientMemberID])

WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[ClientMember] ON

INSERT [dbo].[ClientMember] ([ClientMemberID], [ClientRoleID], [MemberID], [BeginEffectiveDate], [EndEffectiveDate], [UserLogin], [UpdateDate])

SELECT 2, 2, 675, CAST(N'2013-12-23 15:09:31.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:31.000' AS DateTime) UNION ALL

SELECT 3, 2, 676, CAST(N'2013-12-23 15:09:31.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:31.000' AS DateTime) UNION ALL

SELECT 4, 2, 677, CAST(N'2013-12-23 15:09:31.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:31.000' AS DateTime) UNION ALL

SELECT 5, 2, 678, CAST(N'2013-12-23 15:09:31.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:31.000' AS DateTime) UNION ALL

SELECT 6, 2, 679, CAST(N'2013-12-23 15:09:31.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:31.000' AS DateTime) UNION ALL

SELECT 7, 2, 680, CAST(N'2013-12-23 15:09:31.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:31.000' AS DateTime) UNION ALL

SELECT 8, 2, 681, CAST(N'2013-12-23 15:09:31.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:31.000' AS DateTime) UNION ALL

SELECT 9, 2, 682, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 10, 2, 683, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 11, 2, 684, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 12, 2, 685, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 13, 2, 686, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 14, 2, 687, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 15, 2, 688, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 16, 2, 689, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 17, 2, 690, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime) UNION ALL

SELECT 18, 2, 691, CAST(N'2013-12-23 15:09:32.000' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), N'AppLogin', CAST(N'2013-12-23 15:09:32.000' AS DateTime)

SET IDENTITY_INSERT [dbo].[ClientMember] OFF

GO

---------------------------------------------------------------------------------------------------

-- [dbo].[MemberOther]

---------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[MemberOther](

[MemberOtherID] [int] IDENTITY(1,1) NOT NULL,

[ClientMemberID] [int] NOT NULL,

[Description] [nvarchar](60) NOT NULL,

[TheValue] [bit] NOT NULL,

[BeginEffectiveDate] [datetime] NOT NULL,

[EndEffectiveDate] [datetime] NOT NULL,

[IncludeInCriteria] [bit] NOT NULL,

[UserLogin] [nvarchar](20) NOT NULL,

[UpdateDate] [datetime] NOT NULL,

[UpdateMethod] [nvarchar](10) NOT NULL,

CONSTRAINT [PK_MemberOther] PRIMARY KEY NONCLUSTERED

([MemberOtherID] ASC)

WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_MemberOther_ClientMemberID] ON [dbo].[MemberOther]

([Description] ASC, [EndEffectiveDate] DESC, [ClientMemberID] ASC)

WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[MemberOther] ON

INSERT [dbo].[MemberOther] ([MemberOtherID], [ClientMemberID], [Description], [TheValue], [BeginEffectiveDate], [EndEffectiveDate], [IncludeInCriteria], [UserLogin], [UpdateDate], [UpdateMethod])

SELECT 2, 52485, N'Auto Enroll', 0, CAST(N'2014-02-18 09:55:29.917' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-18 09:55:29.917' AS DateTime), N'Manual' UNION ALL

SELECT 3, 52489, N'Auto Enroll', 0, CAST(N'2014-02-18 09:57:35.077' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-18 09:57:35.077' AS DateTime), N'Manual' UNION ALL

SELECT 4, 52491, N'Auto Enroll', 0, CAST(N'2014-02-18 09:59:41.393' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-18 09:59:41.393' AS DateTime), N'Manual' UNION ALL

SELECT 5, 52494, N'Auto Enroll', 0, CAST(N'2014-02-18 10:01:50.063' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-18 10:01:50.063' AS DateTime), N'Manual' UNION ALL

SELECT 6, 52499, N'Auto Enroll', 0, CAST(N'2014-02-18 10:03:29.440' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-18 10:03:29.440' AS DateTime), N'Manual' UNION ALL

SELECT 7, 52503, N'Auto Enroll', 0, CAST(N'2014-02-18 10:05:36.067' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-18 10:05:36.067' AS DateTime), N'Manual' UNION ALL

SELECT 8, 52507, N'Auto Enroll', 0, CAST(N'2014-02-18 10:07:05.597' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-18 10:07:05.597' AS DateTime), N'Manual' UNION ALL

SELECT 9, 52512, N'Auto Enroll', 0, CAST(N'2014-02-18 12:09:05.750' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-20 17:53:07.963' AS DateTime), N'Manual' UNION ALL

SELECT 10, 52538, N'Auto Enroll', 0, CAST(N'2014-02-19 15:52:21.833' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-19 15:52:21.833' AS DateTime), N'Manual' UNION ALL

SELECT 11, 52540, N'Auto Enroll', 0, CAST(N'2014-02-19 15:50:31.833' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-19 15:50:31.833' AS DateTime), N'Manual' UNION ALL

SELECT 12, 52544, N'Auto Enroll', 0, CAST(N'2014-02-19 15:46:56.643' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-19 15:46:56.643' AS DateTime), N'Manual' UNION ALL

SELECT 13, 52546, N'Auto Enroll', 0, CAST(N'2014-02-19 15:45:03.570' AS DateTime), CAST(N'2099-09-09 00:00:00.000' AS DateTime), 1, N'App Login', CAST(N'2014-02-19 15:45:03.570' AS DateTime), N'Manual'

SET IDENTITY_INSERT [dbo].[MemberOther] OFF

GO

---------------------------------------------------------------------------------------------------

-- [dbo].[ClientRole]

---------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[ClientRole](

[ClientRoleID] [int] IDENTITY(1,1) NOT NULL,

[RoleID] [int] NOT NULL,

[ClientID] [int] NOT NULL,

[RoleDefault] [bit] NOT NULL,

[DefaultBeginLevel] [int] NOT NULL,

[DefaultEndLevel] [int] NOT NULL,

[IncludeInCriteria] [bit] NOT NULL,

CONSTRAINT [PK_ClientRole] PRIMARY KEY NONCLUSTERED

([ClientRoleID] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_ClientRole_MemberID] ON [dbo].[ClientRole]

([ClientID] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_ClientRole_DefaultBeginLevel] ON [dbo].[ClientRole]

([DefaultBeginLevel] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[ClientRole] ON

INSERT [dbo].[ClientRole] ([ClientRoleID], [RoleID], [ClientID], [RoleDefault], [DefaultBeginLevel], [DefaultEndLevel], [IncludeInCriteria])

SELECT 3, 69, 0, 0, 6, 2000000000, 1 UNION ALL

SELECT 4, 47, 0, 0, 6, 2000000000, 1 UNION ALL

SELECT 1, 26, 88, 0, 2, 2000000000, 1 UNION ALL

SELECT 2, 29, 88, 0, 5, 2000000000, 1 UNION ALL

SELECT 5, 25, 6352, 0, 1, 2000000000, 1 UNION ALL

SELECT 6, 26, 6352, 0, 2, 2000000000, 1 UNION ALL

SELECT 7, 49, 6352, 0, 3, 2000000000, 1 UNION ALL

SELECT 8, 48, 6352, 0, 4, 2000000000, 1 UNION ALL

SELECT 9, 29, 6352, 0, 5, 2000000000, 1 UNION ALL

SELECT 10, 46, 6352, 0, 6, 2000000000, 1 UNION ALL

SELECT 11, 47, 6352, 0, 6, 2000000000, 1 UNION ALL

SELECT 12, 28, 6352, 0, 6, 2000000000, 1 UNION ALL

SELECT 13, 58, 6352, 0, 8, 2000000000, 1 UNION ALL

SELECT 14, 67, 6352, 0, 1, 2000000000, 1 UNION ALL

SELECT 15, 69, 6352, 0, 1, 2000000000, 1

SET IDENTITY_INSERT [dbo].[ClientRole] OFF

GO

------------------------------------------------------------------------------------------------

ALTER INDEX ALL ON [dbo].[ClientMember] REBUILD

ALTER INDEX ALL ON [dbo].ClientRole REBUILD

ALTER INDEX ALL ON [dbo].MemberOther REBUILD

ALTER INDEX ALL ON [dbo].Outbox REBUILD

ALTER INDEX ALL ON [dbo].OutBoxData REBUILD

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

sgmunson (2/16/2015)
The following part of the LEFT JOIN isn't needed:
AND O.CompanyClientMemberID IS NOT NULL
That field is already part of the rest of the JOIN condition (the specific line follows) based on equality with the ClientMemberID field of the joined table, which NULL values can never achieve, not even with other NULL values.
ON CM.ClientMemberID = O.CompanyClientMemberID

Careful, Steve - this only works if cm is INNER JOINed:

DROP TABLE #Parent

CREATE TABLE #Parent (ParentID INT NOT NULL IDENTITY(1,1), ClientID INT NULL)

INSERT INTO #Parent (ClientID) VALUES (1), (2), (NULL), (4)

DROP TABLE #Client

CREATE TABLE #Client (ClientID INT NOT NULL IDENTITY(1,1), ClientName VARCHAR(10) NULL)

INSERT INTO #Client (ClientName) VALUES ('First'), ('Second')

SELECT *

FROM #Parent p

LEFT JOIN #Client c ON c.ClientID = p.ClientID

SELECT *

FROM #Parent p

LEFT JOIN #Client c ON c.ClientID = p.ClientID AND p.ClientID IS NOT NULL

SELECT *

FROM #Parent p

LEFT JOIN #Client c ON c.ClientID = p.ClientID

WHERE p.ClientID IS NOT NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Velveeta22 (2/16/2015)
This produces the same result set in both the test environment and production. Does anyone see any logic fail on the re-write?
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 = '2013-12-23 15:09:31.000';
DECLARE @p7 DATETIME = '2013-12-23 15:09:31.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT
O.Destination,
O.Message,
O.OutboxAN,
O.OutboxStatusAN,
O.OutboxTypeAN,
O.Subject,
OBD.Fax,
OBD.FileName,
OBD.OutBoxData,
OBD.OutBoxDataAN,
OBD.Type,
O.ClientReferenceID,
[CompanyClientMemberID] = ISNULL(O.CompanyClientMemberID, @p1),
[ContactClientMemberID] = ISNULL(O.ContactClientMemberID, @p2),
[ReportType] = ISNULL(O.ReportType, @p3),
[CompanyClientRoleID] = ISNULL(CM.ClientRoleID, @p4)
FROM dbo.Outbox AS O
JOIN dbo.OutBoxData AS OBD
ON OBD.OutBoxAN = O.OutboxAN
LEFT JOIN dbo.ClientMember CM
ON CM.ClientMemberID = O.CompanyClientMemberID
AND CM.EndEffectiveDate = @p0
AND O.CompanyClientMemberID IS NOT NULL
WHERE O.OutboxStatusAN = @p11
I then ran an execution plan in prod and OutboxData is 98% of the plan.

This filter

AND O.CompanyClientMemberID IS NOT NULL

won't work in the join, if the filter must be active then it must be in the WHERE clause. I'd mark it as suspect until you can show whether or not the result set should be filtered.

I think your EndEffectiveDate should be '2099-09-09 00:00:00.000', which indicates an active row in a time-expiring set.

Otherwise it's ok. Here's a full query which appears to do the job, but I'm not happy with the t11 part - it looks over-engineered and I suspect a couple of the tables could be removed from the FROM list whilst maintaining the business logic. The current sample dataset is inadequate for testing t11. Note that some of your parameters are now redundant - you don't need three of them for your enddate value.

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

t0.Destination,

t0.[Message],

t0.OutboxAN,

t0.OutboxStatusAN,

t0.OutboxTypeAN,

t0.[Subject],

t1.Fax,

t1.[FileName],

t1.OutBoxData,

t1.OutBoxDataAN,

t1.[Type],

t0.ClientReferenceID,

[value] = ISNULL(t0.CompanyClientMemberID, @p1),

[value2] = ISNULL(t0.ContactClientMemberID, @p2),

[value3] = ISNULL(t0.ReportType, @p3),

[value4] = ISNULL(t3.ClientRoleID, @p4), -- only because ClientRoleID is not nullable

[OutBoxAN2] = t1.OutBoxAN

FROM dbo.Outbox AS t0

INNER JOIN dbo.OutBoxData AS t1

ON t1.OutBoxAN = t0.OutboxAN

LEFT JOIN dbo.ClientMember t3

ON t3.ClientMemberID = t0.CompanyClientMemberID

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

WHERE t0.OutboxStatusAN = @p11

AND t0.CompanyClientMemberID IS NOT NULL

) t4

WHERE

NOT EXISTS(

SELECT 1

FROM dbo.MemberOther t6

INNER JOIN dbo.ClientMember t7 -- same ClientID as t4

ON t7.ClientMemberID = t6.ClientMemberID

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

INNER JOIN dbo.ClientRole t8 -- same ClientID as t4

ON t8.ClientRoleID = t7.ClientRoleID

INNER JOIN dbo.ClientRole t9 -- same ClientID as t4

ON t9.ClientID = t8.ClientID

AND t9.DefaultBeginLevel = @p8

AND t9.ClientRoleID = t4.value4 -- t4.value4 is dbo.ClientMember.ClientRoleID

WHERE t6.TheValue = 1

AND t6.[Description] = @p5

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

OR [t4].[OutboxTypeAN] NOT IN (@p9,@p10)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 14

sgmunson (2/16/2015)
The following part of the LEFT JOIN isn't needed:
AND O.CompanyClientMemberID IS NOT NULL
That field is already part of the rest of the JOIN condition (the specific line follows) based on equality with the ClientMemberID field of the joined table, which NULL values can never achieve, not even with other NULL values.
ON CM.ClientMemberID = O.CompanyClientMemberID

I was trying to take all the restrictions from the original ugly query and re-apply them. Good catch. Do you think I am missing any logic from the CROSS APPLY section?

I also can't figure out why 98% of the query plan is focused on OutBoxData. I threw this non-clustered on it in prod (*snicker*) and re-ran the sql plan. No luck on getting the query to use it:

CREATE NONCLUSTERED INDEX [IX_OutboxData] ON [dbo].[OutBoxData]

(

[OutBoxAN] ASC

)

INCLUDE ( [Fax],

[FileName],

[OutBoxData],

[OutBoxDataAN],

[Type]) WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) ON [PRIMARY]

GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

Can you post the *actual* plan?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden