Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Performance problems joining to a CTE

Author  Topic 

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-17 : 06:21:59
Hi all,

I've got a problem that I just can't seem to understand - let alone fix.

Scenario:

An employee can have a number of benefit records that cover a time (so they start on a date and end on a date). Benefit records have a status that is either 'L' - Live, 'D' - Closed, 'V' - Voided, 'X' - Test

The L and D records are concurrent (they don't overlap). A void record is one that has been superseded and the dates will overlap.

Here is an example of the benefit records for an employee:

Id | EmployeeID | Status | StartDate
1  | 1          | D      | 2008-01-01
2  | 1          | D      | 2008-04-01
3  | 1          | V      | 2009-01-01
4  | 1          | V      | 2009-01-01
5  | 1          | D      | 2009-01-01
6  | 1          | L      | 2009-02-01

benefits hang off the benefit records but they aren't important right now.

There are a lot of time where we would want to compare a benefit record against the one that came before it. The business Rule to find the desired previous benefit record is:

"Get the first non void form starting before the form you want. If there is no non void form get the first void form (by highest date, then Id) before the form we want"

Now -- at the moment our system uses a scaler function to find this previous Benefit Record. This leads to code such as this:

SELECT [blah]
FROM
    employeeBenefitRecord ebr
    JOIN employeeBenefitRecord ebr2 ON ebr2.[ID] = xGetPreviousEmployeebenefitRecord (ebr.[ID])

Which might look OK but is in reality a row by row operation.

I thought that it would be better to write a CTE which uses a set based method to derive the previous forms. This is the CTE:
;WITH

	ebrOrder (
		[employeeId]
		, [ebrId]
		, [status]
		, [startDate]
		, [actualOrder]
		, [logicalOrder]
		)
	AS (
		SELECT
			[employeeID]
			, [ebrId]
			, [status]
			, [startDate]
			, [actualOrder]
			, CASE [status]
				WHEN 'V' THEN [ActualOrder] - [splitOrder]
				ELSE [splitOrder]
			END
		FROM
			(
			SELECT
				[employeeID] AS [employeeId]
				, [Id] AS [ebrID]
				, [status] AS [status]
				, [startDate] AS [startDate]
				, ROW_NUMBER() OVER(PARTITION BY [employeeID] ORDER BY [startDate] DESC, [Id] DESC) AS [actualOrder]
				, ROW_NUMBER() OVER(PARTITION BY [employeeID], CASE [status] WHEN 'V' THEN 1 ELSE 0 END ORDER BY [startDate] DESC, [Id] DESC) AS [splitOrder]
			FROM
				dbo.employeeBenefitRecord
			WHERE
				[status] <> 'X'
			)
			splits
		)


	, previous_ebr (
		[ebrId]
		, [prevEbrId]
		)
	AS (

		SELECT
				ebrFirst.[ebrId]
				, CASE
					WHEN logicalOrder.[ebrID] IS NULL THEN actualOrder.[ebrID]
					ELSE logicalOrder.[ebrID]
				END
			FROM
				ebrOrder ebrFirst

				-- Find next ebr in logical order
				LEFT JOIN ebrOrder logicalOrder ON
					logicalOrder.[employeeID] = ebrFirst.[employeeID]
					AND logicalOrder.[logicalOrder] = ebrFirst.[logicalOrder] + 1
					AND logicalOrder.[status] <> 'V'

				-- find next ebr in Actual order
				LEFT JOIN ebrOrder actualOrder ON
					actualOrder.[employeeId] = ebrFirst.[employeeId]
					AND actualOrder.[startDate] = ebrFirst.[startDate]
					AND actualOrder.[actualOrder] = ebrFirst.[actualOrder] + 1
		)

Now this works great -- it brings back all the right information in a fraction of the time it would take to run the function on each ebr one at a time.

All is good I thought.

Now here is the problem:

When I join like this:

SELECT
	'CTE' AS [Type]
	, ebr.[employeeID] AS [employeeID]
	, ebr.[Id] AS [ebrID]
	, ebr.[status] AS [ebrStatus]
	, ebr2.[ID]
	, ebr2.[status]
FROM
	employeeBenefitRecord ebr 
	INNER JOIN previous_ebr pe ON pe.[EbrId] = ebr.[Id]
	INNER JOIN employeeBenefitRecord ebr2 ON ebr2.[ID] = pe.[prevEbrID]
ORDER BY
	ebr.[employeeID] ASC
	, ebr.[ID] ASC
GO

Then this select statement takes FOREVER to run. Around 5 minutes for 9000 ebrs. However if I join once to the employeeBenefitRecord either on prevEbrId or ebrId only once then the information is returned in only one second.

So to reiterate This:

SELECT
	ebr.[ID]
	, pe.[prevEbrID]
FROM
	employeeBenefitRecord ebr
	JOIN previous_ebr pe ON pe.[ebrId] = ebr.[Id]

takes less than 1 second to run and gives me the correct previous Ebr Id's

However, adding the second join to employeeBenefitRecord takes the time up to 5 Minutes!!!!!

How do I get round this. I'm sure that my approach makes sense but at the moment it is actually slower than the function!




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-17 : 06:30:56
Further info:

The clustered index on employeeBenefitRecord is the [Id] Column -- and I've rebuilt all the indexes on the table to check if that was the problem -- it isn't.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-17 : 06:39:27
Here's the execution plan for the double joined select statement.
NB -- Edit to hide databaseName

NB -- Removed XML execution plan -- it was making the thread load extremely slowly
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-17 : 07:10:53
There is a table spool -> nested loops (inner join) -> right outer join -> right outer join on the execution plan that seems to be taking all the time.

The table spool results in a completely pointless cross join on 11076 rows! -- the actual rows after that is astronomical.

I've changed the query to this: 

SELECT
	'CTE' AS [Type]
	, ebr.[employeeID] AS [employeeID]
	, ebr.[Id] AS [ebrID]
	, ebr.[status] AS [ebrStatus]
	, ebr2.[ID]
	, ebr2.[status]
FROM
	employeeBenefitRecord ebr 
	INNER JOIN previous_ebr pe ON pe.[EbrId] = ebr.[Id]

	INNER JOIN employeeBenefitRecord ebr2 ON
		ebr2.[ID] = pe.[prevEbrID]
		AND ebr2.[employeeID] = ebr.[employeeId]
GO

This produces a totally different execution plan that is much more sensible. (now it is nice and fast) The [employeeId] isn't logically required though (because the prevEbrId HAS to belong to the same employee) so I'm a little annoyed about that.

Anyone know why the table spool (which doesn't exist in the second execution plan) produces this crazy cross join?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2009-07-17 : 16:12:23
If the execution plan is that complicated maybe the only solution is to use some intermediate/temp tables?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-17 : 16:41:32
How do you identify the record "wanted"?
quote:
Get the first non void form starting before the form you want.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-17 : 16:56:36
Is this the expected output based on your provided sample data?
/*
1. Get the first non void form starting before the form you want.
2. If there is no non void form get the first void form (by highest date, then Id) before the form we want

'L' - Live, 'D' - Closed, 'V' - Voided, 'X' - Test
*/
DECLARE	@Sample TABLE
	(
		ID INT NOT NULL,
		EmployeeID INT NOT NULL,
		[Status] CHAR(1) NOT NULL,
		StartDate DATETIME NOT NULL
	)

INSERT	@Sample
SELECT	1, 1, 'D', '2008-01-01' UNION ALL
SELECT	2, 1, 'D', '2008-04-01' UNION ALL
SELECT	3, 1, 'V', '2009-01-01' UNION ALL
SELECT	4, 1, 'V', '2009-01-01' UNION ALL
SELECT	5, 1, 'D', '2009-01-01' UNION ALL
SELECT	6, 1, 'L', '2009-02-01'

SELECT	*
FROM	@Sample

;WITH Yak (ID, PrevID)
AS	(
		SELECT		s.ID,
				f.ID
		FROM		(
					SELECT	ID
					FROM	@Sample
					WHERE	[Status] <> 'X'
				) AS s
		CROSS APPLY	(
					SELECT TOP 1	g.ID
					FROM		@Sample AS g
					WHERE		g.ID < s.ID
							AND g.[Status] <> 'X'
					ORDER BY	CASE g.[Status]
								WHEN 'V' THEN 1
								ELSE 0
							END,
							g.StartDate DESC,
							g.ID DESC
				) AS f
	)

SELECT	ID,
	PrevID
FROM	Yak


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 05:16:07
Hi Peso,

Yes that's the output I want (except that Id 1 needs to return a NULL -- though I guess that doesn't matter too much as it could be assumed in a left join). Also -- you need to include a match on employeeId from s and g otherwise it doesn't work for more than one employee. Your revised method is 

-- CROSS APPLY
;WITH Yak (ID, PrevID)
AS	(
		SELECT		s.ID,
				f.ID
		FROM		(
					SELECT	ID
						, employeeID
					FROM	@Sample
					WHERE	[Status] <> 'X'
				) AS s
		CROSS APPLY	(
					SELECT TOP 1	g.ID
					FROM		@Sample AS g
					WHERE		g.ID < s.ID
								AND g.[Status] <> 'X'
								AND g.[employeeId] = s.[employeeId]
					ORDER BY	CASE g.[Status]
								WHEN 'V' THEN 1
								ELSE 0
							END,
							g.StartDate DESC,
							g.ID DESC
				) AS f
	)

SELECT	ID,
	PrevID
FROM	Yak
ORDER BY
	[Id]


The performance of the CTE isn't the problem for me. The ROW_NUMBER() method I posted is a lot faster than your CROSS APPLY on my test databases (around 10,000 rows in the employeeBenefitRecord table with average of 4 employeeBenefitRecord per employee).

Each method does produce the same results which is good. The CROSS APPLY still does a sub-query on every row in the result set doesn't it? which is what I was hoping to avoid in the first place.

The problem only seems to happen when you try to join the result set from the CTE to 2 copies of the employeebenefitRecord.

For this JOIN 

FROM
    employeeBenefitRecord ebr
    JOIN previous_ebr pe ON pe.[ebrId] = ebr.[ID]
    JOIN employeeBenefitRecord ebr2 ON ebr2.[Id] = pe.[prevEbrID]
[code]

The execution plan says that it performs a CROSS JOIN on ebr to ebr2 and then filters the results based on the previous_ebr. That's not what I was expecting. I was expecting a hash map inner join from ebr to pe to ebr2.

My end goal is to implement the CTE in a view so the code changes required are simple for the engineers to understand (just replace the calls in joins to the function with the view as an intermediate table).

Now -- if I simply replace the INNER JOINS with LEFT JOINS everything works as it should (and the ROW_NUMBER() method is very quick). I'll be using LEFT JOINS in production so that makes me happy.

However, I really want to know why this is happening. Here is some test cases with a lot more data. You should see that with the LEFT JOIN the ROW_NUMBER() method outperforms your CROSS APPLY by a factor of 20. You'll also be able to see from the execution plan (if you get the same plan I do) that your method doesn't ever generate a CROSS JOIN while mine does-- but only for the INNER JOIN[code]
IF OBJECT_ID('tempDb..#employeeBenefitRecord') IS NOT NULL DROP TABLE #employeeBenefitRecord

CREATE TABLE #employeeBenefitRecord (
	[ID] VARCHAR(200)
	, [employeeId] VARCHAR(200)
	, [startDate] VARCHAR(200)
	, [status] VARCHAR(200)
	)
GO

INSERT INTO #employeeBenefitRecord
          SELECT '50878', '4929', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '51514', '4929', '2006-10-01T00:00:00', 'D'
UNION ALL SELECT '52192', '4929', '2007-02-01T00:00:00', 'D'
UNION ALL SELECT '56167', '4929', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58706', '4929', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '59779', '4929', '2007-12-01T00:00:00', 'D'
UNION ALL SELECT '60759', '4929', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '65108', '4929', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65712', '4929', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67271', '4929', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50885', '4953', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '51515', '4953', '2006-10-01T00:00:00', 'D'
UNION ALL SELECT '51980', '4953', '2007-01-01T00:00:00', 'D'
UNION ALL SELECT '50879', '4953', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56168', '4953', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59081', '4953', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60779', '4953', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64814', '4953', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65713', '4953', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '50884', '4954', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50789', '4954', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56169', '4954', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58718', '4954', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60780', '4954', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64815', '4954', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65714', '4954', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67272', '4954', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50793', '4955', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56170', '4955', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58516', '4955', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60781', '4955', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64816', '4955', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65715', '4955', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67273', '4955', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50830', '4957', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56171', '4957', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59279', '4957', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60782', '4957', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64817', '4957', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65716', '4957', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '50843', '4958', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56172', '4958', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59273', '4958', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60783', '4958', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64818', '4958', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65717', '4958', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67274', '4958', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50804', '4959', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56173', '4959', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '50794', '4960', '2006-07-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '56174', '4960', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58512', '4960', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60784', '4960', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64819', '4960', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65718', '4960', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67275', '4960', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49532', '4961', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56175', '4961', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59073', '4961', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64891', '4961', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65719', '4961', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67276', '4961', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51032', '4962', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49533', '4962', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56176', '4962', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58404', '4962', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65004', '4962', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65720', '4962', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67277', '4962', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51031', '4963', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50781', '4963', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '57568', '4963', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '51030', '4964', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49535', '4964', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56178', '4964', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59110', '4964', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64065', '4964', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65721', '4964', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67278', '4964', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49536', '4965', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56179', '4965', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59021', '4965', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64066', '4965', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65722', '4965', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67279', '4965', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51029', '4966', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49537', '4966', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56180', '4966', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '51028', '4967', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49538', '4967', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56181', '4967', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58787', '4967', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64067', '4967', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65723', '4967', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67280', '4967', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49539', '4968', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56182', '4968', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59130', '4968', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60180', '4968', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '61129', '4968', '2008-04-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '64953', '4968', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65724', '4968', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67281', '4968', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51027', '4969', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50763', '4969', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51026', '4970', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49541', '4970', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56183', '4970', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58448', '4970', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64070', '4970', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65725', '4970', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67072', '4970', '2008-12-01T00:00:00', 'D'
UNION ALL SELECT '67282', '4970', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51025', '4971', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49542', '4971', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56184', '4971', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59135', '4971', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64996', '4971', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65726', '4971', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67283', '4971', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51024', '4972', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50766', '4972', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56185', '4972', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58952', '4972', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '51023', '4974', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50773', '4974', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51022', '4975', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49546', '4975', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56186', '4975', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59327', '4975', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65104', '4975', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65727', '4975', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67284', '4975', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51021', '4976', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49547', '4976', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56187', '4976', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59337', '4976', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '61232', '4976', '2008-04-01T00:00:00', 'D'
UNION ALL SELECT '64073', '4976', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65728', '4976', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67285', '4976', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49548', '4977', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '52469', '4977', '2007-03-09T00:00:00', 'D'
UNION ALL SELECT '57375', '4977', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58887', '4977', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64075', '4977', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65729', '4977', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67286', '4977', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49549', '4978', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56189', '4978', '2007-05-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '58813', '4978', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65057', '4978', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65730', '4978', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67287', '4978', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51020', '4979', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49550', '4979', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56190', '4979', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '57940', '4979', '2007-07-01T00:00:00', 'D'
UNION ALL SELECT '58785', '4979', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64077', '4979', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65290', '4979', '2008-06-01T00:00:00', 'D'
UNION ALL SELECT '65731', '4979', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '68462', '4979', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51019', '4980', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49551', '4980', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56191', '4980', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59129', '4980', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64078', '4980', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65732', '4980', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67289', '4980', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51018', '4981', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49552', '4981', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51017', '4982', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49553', '4982', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51016', '4983', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49554', '4983', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56193', '4983', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59389', '4983', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65016', '4983', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65733', '4983', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67290', '4983', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51015', '4984', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50762', '4984', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51014', '4985', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49556', '4985', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51013', '4986', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49557', '4986', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56194', '4986', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59299', '4986', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64081', '4986', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65734', '4986', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67291', '4986', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51012', '4987', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50778', '4987', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56195', '4987', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '51011', '4988', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49559', '4988', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '57569', '4988', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58073', '4988', '2007-08-01T00:00:00', 'D'
UNION ALL SELECT '59567', '4988', '2007-11-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '64082', '4988', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65735', '4988', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67292', '4988', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51010', '4989', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50770', '4989', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51009', '4990', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49561', '4990', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56197', '4990', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58590', '4990', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64083', '4990', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65736', '4990', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67293', '4990', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51008', '4991', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49562', '4991', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56198', '4991', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '49563', '4992', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56199', '4992', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59232', '4992', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64084', '4992', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65737', '4992', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '51007', '4993', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49564', '4993', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56200', '4993', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58766', '4993', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64085', '4993', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65738', '4993', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '68463', '4993', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51006', '4994', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49565', '4994', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56201', '4994', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59423', '4994', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65091', '4994', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65739', '4994', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '68464', '4994', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51005', '4995', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '51135', '4995', '2006-08-01T00:00:00', 'D'
UNION ALL SELECT '49566', '4995', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56202', '4995', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58456', '4995', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60164', '4995', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '61121', '4995', '2008-04-01T00:00:00', 'D'
UNION ALL SELECT '64088', '4995', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65740', '4995', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67296', '4995', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51004', '4996', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49567', '4996', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56203', '4996', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58721', '4996', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65036', '4996', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65741', '4996', '2008-10-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '68465', '4996', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51950', '4997', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '54941', '4997', '2007-03-01T00:00:00', 'D'
UNION ALL SELECT '50624', '4997', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51146', '4997', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51147', '4997', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51148', '4997', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51947', '4997', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51948', '4997', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51949', '4997', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56204', '4997', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58588', '4997', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65140', '4997', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65742', '4997', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '49568', '4998', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56205', '4998', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59516', '4998', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '59039', '4998', '2007-11-01T00:00:00', 'V'
UNION ALL SELECT '64090', '4998', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65743', '4998', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '68466', '4998', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51003', '4999', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49569', '4999', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56206', '4999', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '51002', '5000', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49570', '5000', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56207', '5000', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59504', '5000', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '58474', '5000', '2007-11-01T00:00:00', 'V'
UNION ALL SELECT '64091', '5000', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65744', '5000', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67193', '5000', '2009-02-01T00:00:00', 'D'
UNION ALL SELECT '67299', '5000', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49571', '5001', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56208', '5001', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59153', '5001', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '51001', '5002', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49572', '5002', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56209', '5002', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59519', '5002', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '59285', '5002', '2007-11-01T00:00:00', 'V'
UNION ALL SELECT '64092', '5002', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65745', '5002', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67300', '5002', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51000', '5003', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49573', '5003', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56210', '5003', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58538', '5003', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64093', '5003', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65746', '5003', '2008-10-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '67301', '5003', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50999', '5004', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49574', '5004', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56211', '5004', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '50998', '5005', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49575', '5005', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56212', '5005', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59365', '5005', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64094', '5005', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65747', '5005', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67302', '5005', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50997', '5006', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49576', '5006', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56213', '5006', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58510', '5006', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64096', '5006', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65748', '5006', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67303', '5006', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50996', '5007', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49577', '5007', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56214', '5007', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58669', '5007', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65187', '5007', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '50995', '5008', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '52405', '5008', '2007-03-05T00:00:00', 'D'
UNION ALL SELECT '52491', '5008', '2007-03-14T00:00:00', 'D'
UNION ALL SELECT '50815', '5008', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '52352', '5008', '2007-03-05T00:00:00', 'V'
UNION ALL SELECT '57695', '5008', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '57696', '5008', '2007-05-02T00:00:00', 'D'
UNION ALL SELECT '57839', '5008', '2007-06-01T00:00:00', 'D'
UNION ALL SELECT '57941', '5008', '2007-07-01T00:00:00', 'D'
UNION ALL SELECT '58303', '5008', '2007-10-01T00:00:00', 'D'
UNION ALL SELECT '58640', '5008', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '56215', '5008', '2007-05-01T00:00:00', 'V'
UNION ALL SELECT '64098', '5008', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65749', '5008', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67304', '5008', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50994', '5009', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50755', '5009', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '50987', '5010', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '51854', '5010', '2006-12-01T00:00:00', 'D'
UNION ALL SELECT '51983', '5010', '2007-01-01T00:00:00', 'D'
UNION ALL SELECT '49580', '5010', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56216', '5010', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '49581', '5011', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56217', '5011', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58539', '5011', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64945', '5011', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65750', '5011', '2008-10-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '50923', '5012', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49582', '5012', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56218', '5012', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '49583', '5013', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56219', '5013', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59031', '5013', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64369', '5013', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65751', '5013', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '51079', '5015', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '52470', '5015', '2007-03-09T00:00:00', 'D'
UNION ALL SELECT '52492', '5015', '2007-03-14T00:00:00', 'D'
UNION ALL SELECT '49585', '5015', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '50883', '5015', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '57587', '5015', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59287', '5015', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65045', '5015', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65752', '5015', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67216', '5015', '2009-03-01T00:00:00', 'D'
UNION ALL SELECT '67305', '5015', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50882', '5016', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '52471', '5016', '2007-03-09T00:00:00', 'D'
UNION ALL SELECT '49586', '5016', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56221', '5016', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58038', '5016', '2007-07-01T00:00:00', 'D'
UNION ALL SELECT '58074', '5016', '2007-08-01T00:00:00', 'D'
UNION ALL SELECT '58613', '5016', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64872', '5016', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65753', '5016', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '68430', '5016', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49587', '5017', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49588', '5018', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56223', '5018', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58518', '5018', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60274', '5018', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '60047', '5018', '2008-03-01T00:00:00', 'V'
UNION ALL SELECT '60048', '5018', '2008-03-01T00:00:00', 'V'
UNION ALL SELECT '64069', '5018', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65754', '5018', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67307', '5018', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50758', '5019', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56224', '5019', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58714', '5019', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65093', '5019', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65755', '5019', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67308', '5019', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51091', '5020', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '51720', '5020', '2006-11-01T00:00:00', 'D'
UNION ALL SELECT '54938', '5020', '2007-03-01T00:00:00', 'D'
UNION ALL SELECT '50845', '5020', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51053', '5020', '2006-07-01T00:00:00', 'V'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '56225', '5020', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58417', '5020', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60275', '5020', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64074', '5020', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '51090', '5021', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '51302', '5021', '2006-08-01T00:00:00', 'D'
UNION ALL SELECT '54942', '5021', '2007-03-01T00:00:00', 'D'
UNION ALL SELECT '50850', '5021', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51054', '5021', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56226', '5021', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '57807', '5021', '2007-06-01T00:00:00', 'D'
UNION ALL SELECT '59179', '5021', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60276', '5021', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64079', '5021', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65756', '5021', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67309', '5021', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '49592', '5022', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50779', '5023', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '51309', '5023', '2006-08-01T00:00:00', 'D'
UNION ALL SELECT '51696', '5023', '2006-09-01T00:00:00', 'D'
UNION ALL SELECT '51870', '5023', '2006-12-01T00:00:00', 'D'
UNION ALL SELECT '51306', '5023', '2006-08-01T00:00:00', 'V'
UNION ALL SELECT '51310', '5023', '2006-09-01T00:00:00', 'V'
UNION ALL SELECT '49594', '5024', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49595', '5025', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '57441', '5025', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58774', '5025', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60277', '5025', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '64087', '5025', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65757', '5025', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67236', '5025', '2009-03-01T00:00:00', 'D'
UNION ALL SELECT '67310', '5025', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51089', '5026', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '54930', '5026', '2007-03-01T00:00:00', 'D'
UNION ALL SELECT '49596', '5026', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '51055', '5026', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56230', '5026', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '49597', '5027', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49598', '5028', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49599', '5029', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '56232', '5029', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58908', '5029', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64095', '5029', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65758', '5029', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67311', '5029', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50993', '5030', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49600', '5030', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56233', '5030', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59374', '5030', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64099', '5030', '2008-05-01T00:00:00', 'D'
GO

INSERT INTO #employeeBenefitRecord
          SELECT '65759', '5030', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67312', '5030', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '51077', '5031', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '54937', '5031', '2007-03-01T00:00:00', 'D'
UNION ALL SELECT '49601', '5031', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '50992', '5031', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '57406', '5031', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59503', '5031', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '58446', '5031', '2007-11-01T00:00:00', 'V'
UNION ALL SELECT '64100', '5031', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65760', '5031', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67313', '5031', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50991', '5032', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49602', '5032', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '57445', '5032', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58804', '5032', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64101', '5032', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65761', '5032', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67314', '5032', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50990', '5033', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '54946', '5033', '2007-03-01T00:00:00', 'D'
UNION ALL SELECT '49603', '5033', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '54944', '5033', '2007-03-01T00:00:00', 'V'
UNION ALL SELECT '54945', '5033', '2007-03-01T00:00:00', 'V'
UNION ALL SELECT '56236', '5033', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58491', '5033', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '64102', '5033', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65762', '5033', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '50989', '5034', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49604', '5034', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '57489', '5034', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59237', '5034', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '65142', '5034', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '65763', '5034', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67315', '5034', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50768', '5035', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50988', '5036', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49606', '5036', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '57469', '5036', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '59044', '5036', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '60104', '5036', '2008-03-01T00:00:00', 'D'
UNION ALL SELECT '65125', '5036', '2008-05-01T00:00:00', 'D'
UNION ALL SELECT '66880', '5036', '2008-10-01T00:00:00', 'D'
UNION ALL SELECT '67316', '5036', '2009-05-01T00:00:00', 'L'
UNION ALL SELECT '50986', '5037', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '49607', '5037', '2006-07-01T00:00:00', 'V'
UNION ALL SELECT '56239', '5037', '2007-05-01T00:00:00', 'D'
UNION ALL SELECT '58363', '5037', '2007-11-01T00:00:00', 'D'
UNION ALL SELECT '50750', '5038', '2006-07-01T00:00:00', 'D'
UNION ALL SELECT '50985', '5039', '2006-07-01T00:00:00', 'D'
GO

-- LEFT JOIN TESTS ------------------------------------------------------------

-- CROSS APPLY (LEFT JOIN)
;WITH Yak (ID, PrevID)
AS	(
		SELECT		s.ID,
				f.ID
		FROM		(
					SELECT	ID
							, employeeID
					FROM	#employeeBenefitRecord
					WHERE	[Status] <> 'X'
				) AS s
		CROSS APPLY	(
					SELECT TOP 1	g.ID
					FROM		#employeeBenefitRecord AS g
					WHERE		g.ID < s.ID
								AND g.[Status] <> 'X'
								AND g.[employeeId] = s.[employeeId]
					ORDER BY	CASE g.[Status]
								WHEN 'V' THEN 1
								ELSE 0
							END,
							g.StartDate DESC,
							g.ID DESC
				) AS f
	)

SELECT
	ebr.[Id]
	, ebr2.[ID]
FROM
	#employeeBenefitRecord ebr
	LEFT JOIN Yak y ON y.[ID] = ebr.[ID]
	LEFT JOIN #employeeBenefitRecord ebr2 ON ebr2.[ID] = y.[prevId]
ORDER BY
	ebr.[Id]

-- ROWNUMBER() (LEFT JOIN)
;WITH ebrOrder (
	[employeeId]
	, [ebrId]
	, [status]
	, [startDate]
	, [actualOrder]
	, [logicalOrder]
	)
AS (
	SELECT
		[employeeID]
		, [ebrId]
		, [status]
		, [startDate]
		, [actualOrder]
		, CASE [status]
			WHEN 'V' THEN [ActualOrder] - [splitOrder]
			ELSE [splitOrder]
		END
	FROM
		(
		SELECT
			[employeeID] AS [employeeId]
			, [Id] AS [ebrID]
			, [status] AS [status]
			, [startDate] AS [startDate]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID] ORDER BY [startDate] DESC, [Id] DESC) AS [actualOrder]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID], CASE [status] WHEN 'V' THEN 1 ELSE 0 END ORDER BY [startDate] DESC, [Id] DESC) AS [splitOrder]
		FROM
			#employeeBenefitRecord
		WHERE
			[status] <> 'X'
		)
		splits
	)

, prevEbr(
	[Id]
	, [prevId]
	)
	AS (
		SELECT
			ebrFirst.[ebrId]
			, CASE
				WHEN logicalOrder.[ebrID] IS NULL THEN actualOrder.[ebrID]
				ELSE logicalOrder.[ebrID]
			END
		FROM
			ebrOrder ebrFirst

			-- Find next ebr in logical order
			LEFT JOIN ebrOrder logicalOrder ON
				logicalOrder.[employeeID] = ebrFirst.[employeeID]
				AND logicalOrder.[logicalOrder] = ebrFirst.[logicalOrder] + 1
				AND logicalOrder.[status] <> 'V'

			-- find next ebr in Actual order
			LEFT JOIN ebrOrder actualOrder ON
				actualOrder.[employeeId] = ebrFirst.[employeeId]
				AND actualOrder.[startDate] = ebrFirst.[startDate]
				AND actualOrder.[actualOrder] = ebrFirst.[actualOrder] + 1
	)
SELECT
	ebr.[Id]
	, ebr2.[ID]
FROM
	#employeeBenefitRecord ebr
	JOIN prevEbr y ON y.[ID] = ebr.[ID]
	LEFT JOIN #employeeBenefitRecord ebr2 ON ebr2.[ID] = y.[prevId]
ORDER BY
	ebr.[ID]



-- INNER JOIN TESTS -----------------------------------------------------------

-- CROSS APPLY (INNER JOIN)
;WITH Yak (ID, PrevID)
AS	(
		SELECT		s.ID,
				f.ID
		FROM		(
					SELECT	ID
							, employeeID
					FROM	#employeeBenefitRecord
					WHERE	[Status] <> 'X'
				) AS s
		CROSS APPLY	(
					SELECT TOP 1	g.ID
					FROM		#employeeBenefitRecord AS g
					WHERE		g.ID < s.ID
								AND g.[Status] <> 'X'
								AND g.[employeeId] = s.[employeeId]
					ORDER BY	CASE g.[Status]
								WHEN 'V' THEN 1
								ELSE 0
							END,
							g.StartDate DESC,
							g.ID DESC
				) AS f
	)

SELECT
	ebr.[Id]
	, ebr2.[ID]
FROM
	#employeeBenefitRecord ebr
	JOIN Yak y ON y.[ID] = ebr.[ID]
	JOIN #employeeBenefitRecord ebr2 ON ebr2.[ID] = y.[prevId]
ORDER BY
	ebr.[Id]

-- ROWNUMBER() (INNER JOIN)
;WITH ebrOrder (
	[employeeId]
	, [ebrId]
	, [status]
	, [startDate]
	, [actualOrder]
	, [logicalOrder]
	)
AS (
	SELECT
		[employeeID]
		, [ebrId]
		, [status]
		, [startDate]
		, [actualOrder]
		, CASE [status]
			WHEN 'V' THEN [ActualOrder] - [splitOrder]
			ELSE [splitOrder]
		END
	FROM
		(
		SELECT
			[employeeID] AS [employeeId]
			, [Id] AS [ebrID]
			, [status] AS [status]
			, [startDate] AS [startDate]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID] ORDER BY [startDate] DESC, [Id] DESC) AS [actualOrder]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID], CASE [status] WHEN 'V' THEN 1 ELSE 0 END ORDER BY [startDate] DESC, [Id] DESC) AS [splitOrder]
		FROM
			#employeeBenefitRecord
		WHERE
			[status] <> 'X'
		)
		splits
	)

, prevEbr(
	[Id]
	, [prevId]
	)
	AS (
		SELECT
			ebrFirst.[ebrId]
			, CASE
				WHEN logicalOrder.[ebrID] IS NULL THEN actualOrder.[ebrID]
				ELSE logicalOrder.[ebrID]
			END
		FROM
			ebrOrder ebrFirst

			-- Find next ebr in logical order
			LEFT JOIN ebrOrder logicalOrder ON
				logicalOrder.[employeeID] = ebrFirst.[employeeID]
				AND logicalOrder.[logicalOrder] = ebrFirst.[logicalOrder] + 1
				AND logicalOrder.[status] <> 'V'

			-- find next ebr in Actual order
			LEFT JOIN ebrOrder actualOrder ON
				actualOrder.[employeeId] = ebrFirst.[employeeId]
				AND actualOrder.[startDate] = ebrFirst.[startDate]
				AND actualOrder.[actualOrder] = ebrFirst.[actualOrder] + 1
	)
SELECT
	ebr.[Id]
	, ebr2.[ID]
FROM
	#employeeBenefitRecord ebr
	JOIN prevEbr y ON y.[ID] = ebr.[ID]
	JOIN #employeeBenefitRecord ebr2 ON ebr2.[ID] = y.[prevId]
ORDER BY
	ebr.[ID]


Any ideas re the behaviour of the INNER JOIN?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 05:21:59
quote:
Originally posted by denis_the_thief

If the execution plan is that complicated maybe the only solution is to use some intermediate/temp tables?


Hi Dennis,

Yes that does work however I want to avoid intermediate temp tables at all costs. They introduce cache plan recompiles for one thing which kill performance when scaling up.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-20 : 05:24:08
Which indexes are present on the table?
The CROSS APPLY depends heavily on indexes, and will under certain circumstances be faster than ROW_NUMBER() with left join.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 06:04:43
Indexes in play 


/****** Object:  Index [IX_EmployeeBenefitRecord]    Script Date: 07/20/2009 10:58:28 ******/
CREATE NONCLUSTERED INDEX [IX_EmployeeBenefitRecord] ON [dbo].[EmployeeBenefitRecord] 
(
	[employeeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]



/****** Object:  Index [IX_EmployeeBenefitRecord_EBRid_BGid]    Script Date: 07/20/2009 10:58:56 ******/
CREATE NONCLUSTERED INDEX [IX_EmployeeBenefitRecord_EBRid_BGid] ON [dbo].[EmployeeBenefitRecord] 
(
	[id] ASC,
	[benefitGroupId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]



/****** Object:  Index [IX_EmployeeBenefitRecord_Eid_BGid_Stat]    Script Date: 07/20/2009 10:59:14 ******/
CREATE NONCLUSTERED INDEX [IX_EmployeeBenefitRecord_Eid_BGid_Stat] ON [dbo].[EmployeeBenefitRecord] 
(
	[employeeId] ASC,
	[benefitGroupId] ASC,
	[status] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]



/****** Object:  Index [IX_EmployeeBenefitRecord_Eid_BGid_Stat_StDt]    Script Date: 07/20/2009 10:59:40 ******/
CREATE NONCLUSTERED INDEX [IX_EmployeeBenefitRecord_Eid_BGid_Stat_StDt] ON [dbo].[EmployeeBenefitRecord] 
(
	[employeeId] ASC,
	[benefitGroupId] ASC,
	[status] ASC,
	[id] ASC,
	[startDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]



/****** Object:  Index [IX_EmployeeBenefitRecord_StDt]    Script Date: 07/20/2009 10:59:59 ******/
CREATE NONCLUSTERED INDEX [IX_EmployeeBenefitRecord_StDt] ON [dbo].[EmployeeBenefitRecord] 
(
	[startDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]



/****** Object:  Index [PK_EmployeeBenefitRecord]    Script Date: 07/20/2009 11:00:15 ******/
ALTER TABLE [dbo].[EmployeeBenefitRecord] ADD  CONSTRAINT [PK_EmployeeBenefitRecord] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]


We currently only perform index rebuilds once a week which in my estimation is inadequate. However, our infrastructure dep has a "only change something if something is 100% broken policy" (sigh) so sql that can survive situations where the indexes aren't 100% could well be the way to go.

Do you get that cross join on your database with my sample temp table under the INNER JOIN condition?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-20 : 06:23:11
Here is my result, using only a clustered index over {EmployeeID, StartDate, Status}.
-- Peso (16 ms cpu, 15 ms duration, 1022 reads)
/*
Table 'EmployeeBenefitRecord'. Scan count 501, logical reads 1022, physical reads 0, read-ahead reads 0.

StmtText
  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([master].[dbo].[EmployeeBenefitRecord].[ID], [master].[dbo].[EmployeeBenefitRecord].[EmployeeID]))
       |--Clustered Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_Yak]), WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
       |--Sort(TOP 1, ORDER BY:([Expr1008] ASC, [g].[StartDate] DESC, [g].[ID] DESC))
            |--Compute Scalar(DEFINE:([Expr1007]=[master].[dbo].[EmployeeBenefitRecord].[ID] as [g].[ID], [Expr1008]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status] as [g].[Status]='V' THEN (1) ELSE (0) END))
                 |--Clustered Index Seek(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_Yak] AS [g]), SEEK:([g].[EmployeeID]=[master].[dbo].[EmployeeBenefitRecord].[EmployeeID]),  WHERE:([master].[dbo].[EmployeeBenefitRecord].[ID] as [g].[ID]<[master].[dbo].[EmployeeBenefitRecord].[ID] AND ([master].[dbo].[EmployeeBenefitRecord].[Status] as [g].[Status]<'X' OR [master].[dbo].[EmployeeBenefitRecord].[Status] as [g].[Status]>'X')) ORDERED FORWARD)                 
*/

;WITH Yak (ID, PrevID)
AS	(
		SELECT		s.ID,
				f.ID
		FROM		(
					SELECT	ID,
						EmployeeID
					FROM	EmployeeBenefitRecord 
					WHERE	[Status] <> 'X'
				) AS s
		OUTER APPLY	(
					SELECT TOP 1	g.ID
					FROM		EmployeeBenefitRecord AS g
					WHERE		g.ID < s.ID
							AND g.[Status] <> 'X'
							AND g.EmployeeID = s.EmployeeID
					ORDER BY	CASE g.[Status]
								WHEN 'V' THEN 1
								ELSE 0
							END,
							g.StartDate DESC,
							g.ID DESC
				) AS f
	)

SELECT		ID,
		PrevID
FROM		Yak

-- Transact Charlie (15 ms cpu, 14 ms duration, 25 reads)
/*
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'EmployeeBenefitRecord'. Scan count 5, logical reads 25, physical reads 0, read-ahead reads 0.

StmtText
  |--Hash Match(Right Outer Join, HASH:([ebr2].[ID])=([Expr1048]), RESIDUAL:([master].[dbo].[EmployeeBenefitRecord].[ID] as [ebr2].[ID]=[Expr1048]))
       |--Clustered Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_Yak] AS [ebr2]))
       |--Compute Scalar(DEFINE:([Expr1048]=CASE WHEN [Expr1019] IS NULL THEN [Expr1030] ELSE [Expr1019] END))
            |--Hash Match(Right Outer Join, HASH:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [master].[dbo].[EmployeeBenefitRecord].[StartDate], [Expr1027])=([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [master].[dbo].[EmployeeBenefitRecord].[StartDate], [Expr1050]), RESIDUAL:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID]=[master].[dbo].[EmployeeBenefitRecord].[EmployeeID] AND [master].[dbo].[EmployeeBenefitRecord].[StartDate]=[master].[dbo].[EmployeeBenefitRecord].[StartDate] AND [Expr1027]=[Expr1050]))
                 |--Compute Scalar(DEFINE:([Expr1030]=[master].[dbo].[EmployeeBenefitRecord].[ID]))
                 |    |--Sequence Project(DEFINE:([Expr1027]=row_number))
                 |         |--Segment
                 |              |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                 |                   |--Clustered Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_Yak]), WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
                 |--Compute Scalar(DEFINE:([Expr1050]=[Expr1008]+(1)))
                      |--Hash Match(Right Outer Join, HASH:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [Expr1051])=([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [Expr1052]), RESIDUAL:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID]=[master].[dbo].[EmployeeBenefitRecord].[EmployeeID] AND [Expr1051]=[Expr1052]))
                           |--Compute Scalar(DEFINE:([Expr1051]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN [Expr1016]-[Expr1017] ELSE [Expr1017] END))
                           |    |--Filter(WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'V'))
                           |         |--Compute Scalar(DEFINE:([Expr1019]=[master].[dbo].[EmployeeBenefitRecord].[ID]))
                           |              |--Sequence Project(DEFINE:([Expr1017]=row_number))
                           |                   |--Segment
                           |                        |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [Expr1015] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                           |                             |--Sequence Project(DEFINE:([Expr1016]=row_number))
                           |                                  |--Segment
                           |                                       |--Filter(WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
                           |                                            |--Compute Scalar(DEFINE:([Expr1015]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN (1) ELSE (0) END))
                           |                                                 |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                           |                                                      |--Clustered Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_Yak]))
                           |--Compute Scalar(DEFINE:([Expr1052]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN [Expr1008]-[Expr1009] ELSE [Expr1009] END+(1)))
                                |--Hash Match(Inner Join, HASH:([ebr].[ID])=([master].[dbo].[EmployeeBenefitRecord].[ID]))
                                     |--Clustered Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_Yak] AS [ebr]))
                                     |--Sequence Project(DEFINE:([Expr1009]=row_number))
                                          |--Segment
                                               |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [Expr1007] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                                                    |--Sequence Project(DEFINE:([Expr1008]=row_number))
                                                         |--Segment
                                                              |--Filter(WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
                                                                   |--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN (1) ELSE (0) END))
                                                                        |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                                                                             |--Clustered Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_Yak]))
*/

;WITH ebrOrder (
	[employeeId]
	, [ebrId]
	, [status]
	, [startDate]
	, [actualOrder]
	, [logicalOrder]
	)
AS (
	SELECT
		[employeeID]
		, [ebrId]
		, [status]
		, [startDate]
		, [actualOrder]
		, CASE [status]
			WHEN 'V' THEN [ActualOrder] - [splitOrder]
			ELSE [splitOrder]
		END
	FROM
		(
		SELECT
			[employeeID] AS [employeeId]
			, [Id] AS [ebrID]
			, [status] AS [status]
			, [startDate] AS [startDate]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID] ORDER BY [startDate] DESC, [Id] DESC) AS [actualOrder]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID], CASE [status] WHEN 'V' THEN 1 ELSE 0 END ORDER BY [startDate] DESC, [Id] DESC) AS [splitOrder]
		FROM
			employeeBenefitRecord
		WHERE
			[status] <> 'X'
		)
		splits
	)

, prevEbr(
	[Id]
	, [prevId]
	)
	AS (
		SELECT
			ebrFirst.[ebrId]
			, CASE
				WHEN logicalOrder.[ebrID] IS NULL THEN actualOrder.[ebrID]
				ELSE logicalOrder.[ebrID]
			END
		FROM
			ebrOrder ebrFirst

			-- Find next ebr in logical order
			LEFT JOIN ebrOrder logicalOrder ON
				logicalOrder.[employeeID] = ebrFirst.[employeeID]
				AND logicalOrder.[logicalOrder] = ebrFirst.[logicalOrder] + 1
				AND logicalOrder.[status] <> 'V'

			-- find next ebr in Actual order
			LEFT JOIN ebrOrder actualOrder ON
				actualOrder.[employeeId] = ebrFirst.[employeeId]
				AND actualOrder.[startDate] = ebrFirst.[startDate]
				AND actualOrder.[actualOrder] = ebrFirst.[actualOrder] + 1
	)
SELECT
	ebr.[Id]
	, ebr2.[ID]
FROM
	employeeBenefitRecord ebr
	JOIN prevEbr y ON y.[ID] = ebr.[ID]
	LEFT JOIN employeeBenefitRecord ebr2 ON ebr2.[ID] = y.[prevId]
As you can see, they are very close to each other in time, but the reads differ great. Your suggestion uses only 25 reads, and my suggestion uses 1022 reads (which makes sense due to the OUTER APPLY).
I've included the execution plans as well.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-20 : 06:28:14
There is a bug filed with Microsoft Connect, that Worktables are not included in the statistics.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387326&wa=wsignin1.0


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 06:37:58
Peso -- does that mean that my statistics are artificially low as the worktable isn't brought back?

Thanks for the link on statistics also.

I'm afraid that I can't mess with the clustered index on this table. The knock on changes to foreign keys would just be too much. The changes would never be approved.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-20 : 06:57:50
Here is same execution again, with my index dropped and your indexes added.
-- Peso (16 ms cpu, 16 ms duration, 1011 reads)
/*
Table 'EmployeeBenefitRecord'. Scan count 501, logical reads 1011, physical reads 0, read-ahead reads 0.

StmtText
  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([master].[dbo].[EmployeeBenefitRecord].[ID], [master].[dbo].[EmployeeBenefitRecord].[EmployeeID]))
       |--Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_EmployeeBenefitRecord_Eid_BGid_Stat]),  WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
       |--Sort(TOP 1, ORDER BY:([Expr1006] ASC, [g].[StartDate] DESC, [g].[ID] DESC))
            |--Compute Scalar(DEFINE:([Expr1005]=[master].[dbo].[EmployeeBenefitRecord].[ID] as [g].[ID], [Expr1006]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status] as [g].[Status]='V' THEN (1) ELSE (0) END))
                 |--Index Seek(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_EmployeeBenefitRecord_Eid_BGid_Stat_StDt] AS [g]), SEEK:([g].[EmployeeID]=[master].[dbo].[EmployeeBenefitRecord].[EmployeeID]),  WHERE:([master].[dbo].[EmployeeBenefitRecord].[ID] as [g].[ID]<[master].[dbo].[EmployeeBenefitRecord].[ID] AND ([master].[dbo].[EmployeeBenefitRecord].[Status] as [g].[Status]<'X' OR [master].[dbo].[EmployeeBenefitRecord].[Status] as [g].[Status]>'X')) ORDERED FORWARD)
*/

;WITH Yak (ID, PrevID)
AS	(
		SELECT		s.ID,
				f.ID
		FROM		(
					SELECT	ID,
						EmployeeID
					FROM	EmployeeBenefitRecord 
					WHERE	[Status] <> 'X'
				) AS s
		OUTER APPLY	(
					SELECT TOP 1	g.ID
					FROM		EmployeeBenefitRecord AS g
					WHERE		g.ID < s.ID
							AND g.[Status] <> 'X'
							AND g.EmployeeID = s.EmployeeID
					ORDER BY	CASE g.[Status]
								WHEN 'V' THEN 1
								ELSE 0
							END,
							g.StartDate DESC,
							g.ID DESC
				) AS f
	)

SELECT		ID,
		PrevID
FROM		Yak

-- Transact Charlie (16 ms cpu, 13 ms duration, 16 reads)
/*
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'EmployeeBenefitRecord'. Scan count 5, logical reads 16, physical reads 0, read-ahead reads 0.

StmtText
  |--Hash Match(Right Outer Join, HASH:([ebr2].[ID])=([Expr1043]), RESIDUAL:([master].[dbo].[EmployeeBenefitRecord].[ID] as [ebr2].[ID]=[Expr1043]))
       |--Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_EmployeeBenefitRecord_StDt] AS [ebr2]))
       |--Compute Scalar(DEFINE:([Expr1043]=CASE WHEN [Expr1016] IS NULL THEN [Expr1026] ELSE [Expr1016] END))
            |--Hash Match(Right Outer Join, HASH:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [master].[dbo].[EmployeeBenefitRecord].[StartDate], [Expr1023])=([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [master].[dbo].[EmployeeBenefitRecord].[StartDate], [Expr1045]), RESIDUAL:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID]=[master].[dbo].[EmployeeBenefitRecord].[EmployeeID] AND [master].[dbo].[EmployeeBenefitRecord].[StartDate]=[master].[dbo].[EmployeeBenefitRecord].[StartDate] AND [Expr1023]=[Expr1045]))
                 |--Compute Scalar(DEFINE:([Expr1026]=[master].[dbo].[EmployeeBenefitRecord].[ID]))
                 |    |--Sequence Project(DEFINE:([Expr1023]=row_number))
                 |         |--Segment
                 |              |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                 |                   |--Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_EmployeeBenefitRecord_Eid_BGid_Stat_StDt]),  WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
                 |--Compute Scalar(DEFINE:([Expr1045]=[Expr1006]+(1)))
                      |--Hash Match(Right Outer Join, HASH:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [Expr1046])=([master].[dbo].[EmployeeBenefitRecord].[EmployeeID], [Expr1047]), RESIDUAL:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID]=[master].[dbo].[EmployeeBenefitRecord].[EmployeeID] AND [Expr1046]=[Expr1047]))
                           |--Compute Scalar(DEFINE:([Expr1046]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN [Expr1013]-[Expr1014] ELSE [Expr1014] END))
                           |    |--Filter(WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'V'))
                           |         |--Compute Scalar(DEFINE:([Expr1016]=[master].[dbo].[EmployeeBenefitRecord].[ID]))
                           |              |--Sequence Project(DEFINE:([Expr1014]=row_number))
                           |                   |--Segment
                           |                        |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [Expr1012] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                           |                             |--Sequence Project(DEFINE:([Expr1013]=row_number))
                           |                                  |--Segment
                           |                                       |--Filter(WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
                           |                                            |--Compute Scalar(DEFINE:([Expr1012]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN (1) ELSE (0) END))
                           |                                                 |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                           |                                                      |--Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_EmployeeBenefitRecord_Eid_BGid_Stat_StDt]))
                           |--Compute Scalar(DEFINE:([Expr1047]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN [Expr1006]-[Expr1007] ELSE [Expr1007] END+(1)))
                                |--Hash Match(Inner Join, HASH:([ebr].[ID])=([master].[dbo].[EmployeeBenefitRecord].[ID]))
                                     |--Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_EmployeeBenefitRecord_StDt] AS [ebr]))
                                     |--Sequence Project(DEFINE:([Expr1007]=row_number))
                                          |--Segment
                                               |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [Expr1005] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                                                    |--Sequence Project(DEFINE:([Expr1006]=row_number))
                                                         |--Segment
                                                              |--Filter(WHERE:([master].[dbo].[EmployeeBenefitRecord].[Status]<>'X'))
                                                                   |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [master].[dbo].[EmployeeBenefitRecord].[Status]='V' THEN (1) ELSE (0) END))
                                                                        |--Sort(ORDER BY:([master].[dbo].[EmployeeBenefitRecord].[EmployeeID] ASC, [master].[dbo].[EmployeeBenefitRecord].[StartDate] DESC, [master].[dbo].[EmployeeBenefitRecord].[ID] DESC))
                                                                             |--Index Scan(OBJECT:([master].[dbo].[EmployeeBenefitRecord].[IX_EmployeeBenefitRecord_Eid_BGid_Stat_StDt]))
*/

;WITH ebrOrder (
	[employeeId]
	, [ebrId]
	, [status]
	, [startDate]
	, [actualOrder]
	, [logicalOrder]
	)
AS (
	SELECT
		[employeeID]
		, [ebrId]
		, [status]
		, [startDate]
		, [actualOrder]
		, CASE [status]
			WHEN 'V' THEN [ActualOrder] - [splitOrder]
			ELSE [splitOrder]
		END
	FROM
		(
		SELECT
			[employeeID] AS [employeeId]
			, [Id] AS [ebrID]
			, [status] AS [status]
			, [startDate] AS [startDate]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID] ORDER BY [startDate] DESC, [Id] DESC) AS [actualOrder]
			, ROW_NUMBER() OVER(PARTITION BY [employeeID], CASE [status] WHEN 'V' THEN 1 ELSE 0 END ORDER BY [startDate] DESC, [Id] DESC) AS [splitOrder]
		FROM
			employeeBenefitRecord
		WHERE
			[status] <> 'X'
		)
		splits
	)

, prevEbr(
	[Id]
	, [prevId]
	)
	AS (
		SELECT
			ebrFirst.[ebrId]
			, CASE
				WHEN logicalOrder.[ebrID] IS NULL THEN actualOrder.[ebrID]
				ELSE logicalOrder.[ebrID]
			END
		FROM
			ebrOrder ebrFirst

			-- Find next ebr in logical order
			LEFT JOIN ebrOrder logicalOrder ON
				logicalOrder.[employeeID] = ebrFirst.[employeeID]
				AND logicalOrder.[logicalOrder] = ebrFirst.[logicalOrder] + 1
				AND logicalOrder.[status] <> 'V'

			-- find next ebr in Actual order
			LEFT JOIN ebrOrder actualOrder ON
				actualOrder.[employeeId] = ebrFirst.[employeeId]
				AND actualOrder.[startDate] = ebrFirst.[startDate]
				AND actualOrder.[actualOrder] = ebrFirst.[actualOrder] + 1
	)
SELECT
	ebr.[Id]
	, ebr2.[ID]
FROM
	employeeBenefitRecord ebr
	JOIN prevEbr y ON y.[ID] = ebr.[ID]
	LEFT JOIN employeeBenefitRecord ebr2 ON ebr2.[ID] = y.[prevId]
The reason for my suggestion being so fast even if there are more reads are that I get an INDEX SEEK for the PrevID.
You get 4 INDEX SCAN for the PrevID calculation.
We both get an additional INDEX SCAN for ID, which makes sense since there are no predicates for that SELECT.

It doesn't seem you get parallellism in your query, otherwise using ROW_NUMBER() on a table with clustered index and parallellism can return wrong number.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128744


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 07:11:05
Sorry Peso. Please correct me if I'm being dense here?

Is your recommendation to use your method rather than mine? Obviously we want to avoid the Scans as much as possible but from your testing (and mine) it looks like my method is still quicker (just). Do you think your method is more scale-able. Also how do you think it will work if the indexes are a little out of date.

However, I don't really care which method is better as they are both far, far, far better than what the engineers are currently using. Whichever method I choose has to be able to be explained adequately to a bunch of java guys who don't really like to get their hand's dirty with sql. To be frank I'll be surprised if they have ever used either of the following:

CTE
OUTER APPLY

So I'm going to have fun explaining them. I think they might well understand your method more intuitively.

I guess that my method has the dubious advantage that it doesn't get too badly hurt if the indexes are out of date.

Am I right in thinking that these approaches have very similar performance?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I just saw you posted a forum link after edit -- reading now. -- seems to apply to only 2008 but that seems a HUGE bug
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-20 : 07:37:11
Yes, they seem to have similar performance regarding CPU and DURATION.
Your suggestion has a much better READ count than mine.
I don't know if 500 sample records are enough to represent your reality.

Neither can't I tell which is better, because I don't know enough about your application.
But, it seems that one INDEX SEEK is better than four INDEX SCAN.
Do you experience locking problems?

I don't know what you mean by "Index go out of date"?
Indexes can't get out of date, they are up-to-date.

The about 200 statistics metrics on a table however can be out-of-date.
Statistics are, among other things, used to determine the type of JOIN used; HASH, LOOP or MERGE.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 08:11:34
Sorry for being woolly -- I meant that the index statistics are often out of date (quite fragmented). We don't maintain our indexes properly but infrastructure doesn't want to know unless something is broken right now.... we only update index stats once a week which is really not even close to being enough. I'd like to do it every hour / every 2 hours with tara's script but no dice.

I've scaled the test cases up to 11,000 rows of data. I'm getting this kind of result fairly consistently on this size of dataset which could be considered a slightly above average client for us.

===============================================================================
OUTER APPLY

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'EmployeeBenefitRecord'. Scan count 11091, logical reads 22617, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 840 ms,  elapsed time = 249 ms.


===============================================================================
Rownumber

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'EmployeeBenefitRecord'. Scan count 25, logical reads 407, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 439 ms,  elapsed time = 261 ms.



So it looks like your method has the edge on this size of data.

However, I tested on the bigest client we have at 279,000 rows and get this consistently

===============================================================================
OUTER APPLY

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'EmployeeBenefitRecord'. Scan count 279432, logical reads 859335, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 14530 ms,  elapsed time = 5112 ms.


===============================================================================
Rownumber

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'EmployeeBenefitRecord'. Scan count 25, logical reads 5084, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 7188 ms,  elapsed time = 4360 ms.

Where the ROW_NUMBER seems to edge it again.


Here's the result on 11,000 rows for the old function. Good for a laugh! 

SELECT
	ebr.[ID]
	, ebr2.[ID]
FROM
	employeeBenefitREcord ebr
	LEFT JOIN employeeBenefitRecord ebr2 ON ebr2.[ID] = dbo.xGetPreviousEmployeeBenefitRecordID(ebr.[Id])

===============================================================================
FUNCTION

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'EmployeeBenefitRecord'. Scan count 2, logical reads 36, physical reads 1, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 497407 ms,  elapsed time = 498834 ms.


I'm not even going to try and run the function on our biggest client.

Thanks for the discussion Peso. Once again it's been interesting and very useful. I probably will go with my ROW_NUMBER() approach for the moment just because I think it is more index statistic independent.

I'm very happy though as it's close in performance to your best recommendation.

Cheers again!




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-20 : 08:24:35
It would be cool to test for 11,000 and 279,000 clients with my suggestion of clustered index.
Can you copy the data to a new table and have my clustered index instead?
And run our suggestions on that table?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 08:29:34
Of course I would be happy to do that!

You wan't only one clustered index on empId, StartDate, Status?

What about ID?

post the index you want and I will run.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-07-20 : 08:40:53
Just realised how stupid that question seems.

IT should have been:

So you only want one index? A clustered index on (empId, StartDate, Status?)

What about ID?

post the index you want and I will run.

-- Obviously you can only have 1 clustered index so the previous question didn't really read very well.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site