Hello,

Currently I have the following SQL that runs for 5-8 hours daily on a
SQL Server 2000 / Windows 2000 server.

DECLARE PAD_Cursor CURSOR DYNAMIC FOR
SELECT 
	WNI_PARTNER_ACCOUNT_NUMBER, 
	ACCESS_CONTROL_ID,
	SEQUENCE_NUMBER,
	SERVICE_START_DATE,
	SERVICE_STATUS,
	OLD_ACCESS_CONTROL_ID
FROM 
	UTV_PARTNER_ACCOUNT_DETAIL_ALL
ORDER BY 
	WNI_PARTNER_ACCOUNT_NUMBER, 
	SEQUENCE_NUMBER
FOR UPDATE 

OPEN PAD_Cursor

FETCH NEXT FROM PAD_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
	UPDATE UTV_PARTNER_ACCOUNT_DETAIL_ALL
	SET ORIGINAL_ACCESS_CONTROL_ID = 
		isnull(
		case 	when OLD_ACCESS_CONTROL_ID IS NULL 
			then  ACCESS_CONTROL_ID
			else 
				(select max(lookup.ORIGINAL_ACCESS_CONTROL_ID)
				from UTV_PARTNER_ACCOUNT_DETAIL_ALL lookup
				where lookup.ACCESS_CONTROL_ID =
UTV_PARTNER_ACCOUNT_DETAIL_ALL.OLD_ACCESS_CONTROL_ID AND
				lookup.WNI_PARTNER_ACCOUNT_NUMBER =
UTV_PARTNER_ACCOUNT_DETAIL_ALL.WNI_PARTNER_ACCOUNT_NUMBER)
		end
		,ACCESS_CONTROL_ID)
	WHERE CURRENT OF PAD_CURSOR;

	FETCH NEXT FROM PAD_Cursor
END

CLOSE PAD_Cursor
DEALLOCATE PAD_Cursor


This is the DDL for the table in question.

CREATE TABLE [dbo].[utv_partner_account_detail_all] (
	[WNI_PARTNER_ACCOUNT_NUMBER] [int] NOT NULL ,
	[WNI_PARTNER_NAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ACCESS_CONTROL_ID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SUBSCRIBER_ID] [numeric](11, 0) NULL ,
	[SERVICE_PREFIX] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SERVICE_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OFFER_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SERVICE_MIRRORED_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RECORD_STATUS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SERVICE_STATUS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SERVICE_START_DATE] [datetime] NULL ,
	[OLD_ACCESS_CONTROL_ID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ACCESS_CONTROL_REPLACEMENT_DT] [datetime] NULL ,
	[SERVICE_END_DATE] [datetime] NULL ,
	[CREATED_DT] [datetime] NULL ,
	[ACCESS_CONTROL_TYPE] [varchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IRD_MANUFACTURER] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IRD_MODEL_NUMBER] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IRD_SERIAL_NUMBER] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ROW_SOURCE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SEQUENCE_NUMBER] [bigint] NOT NULL ,
	[ORIGINAL_ACCESS_CONTROL_ID] [char] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
	[ORIGINAL_SERVICE_START_DATE] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[utv_partner_account_detail_all] ADD 
	CONSTRAINT [IX_utv_partner_account_detail_all] UNIQUE  NONCLUSTERED 
	(
		[WNI_PARTNER_ACCOUNT_NUMBER],
		[SEQUENCE_NUMBER]
	) WITH  FILLFACTOR = 60  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_utv_partner_account_detail_all_1] ON
[dbo].[utv_partner_account_detail_all]([WNI_PARTNER_ACCOUNT_NUMBER],
[ORIGINAL_ACCESS_CONTROL_ID], [SEQUENCE_NUMBER]) ON [PRIMARY]
GO


The volume in this table is approximately 3.7 million rows daily. The
server is an 8-way with 3G ram and is about 3 yo.

I am looking for a new SQL statement that will run in under 1 hour on
the same system and matches the output of the original dynamic SQL.
Please note the cursor is DYNAMIC and FOR UPDATE and the following
UPDATE is a self JOIN among other things.

The change can be anything that will run on SS2K such as UPDATE, a
WHILE loop, Temp Tables, stored procedures, Clustered Indexes,
ROWLOCK, LOOP JOIN, ROWCOUNT 1, etc. Of course, certain things (or
combinations) fare better than others on SS2K, so I will be happy to
give a 100% tip for anyone that gets this to run in less than 30
minutes with matching output.

---

Clarification of Question by sunandwavs-ga on 02 Dec 2003 15:12 PST

Please educate (aka correct) me where needed, as I am on a steep
learning curve. I believe the UPDATE is a correlated self-join
sub-query. I've tried doing a pure UPDATE like the following:

UPDATE UTV_Partner_Account_Detail_All
SET Original_Access_Control_Id = ISNULL((
  SELECT MAX(Lookup.Original_Access_Control_Id)
  FROM UTV_Partner_Account_Detail_All Lookup
  WHERE Lookup.Access_Control_Id          =
UTV_Partner_Account_Detail_All.Original_Access_Control_Id
  AND   Lookup.WNI_Partner_Account_Number =
UTV_Partner_Account_Detail_All.WNI_Partner_Account_Number
),Access_Control_Id)

and the output does not match that of the cursors (although it runs in
20mins). I am guessing this is due to default transaction isolation
locks for UPDATE in SS2K. If this is correct, would setting the
transaction isolation level to READ UNCOMMITTED temporarily and MAXDOP
to 1 (to induce serial behavior) help?

---

Request for Question Clarification by mathtalk-ga on 05 Dec 2003 07:55 PST

Hi, sunandwavs-ga:

I'm a little confused about the indexes on this table.

You have a unique constraint on:

WNI_PARTNER_ACCOUNT_NUMBER, SEQUENCE_NUMBER

but then you also put an index on:

WNI_PARTNER_ACCOUNT_NUMBER, ORIGINAL_ACCESS_CONTROL_ID, SEQUENCE_NUMBER

My guess is that the difference between the (fast) UPDATE statement
and the results produced by the cursor have to do with nonuniqueness
in the self-join.

In the cursor it looks like you squeeze out some multiplicity by using
the max(lookup.ORIGINAL_ACCESS_CONTROL_ID) construction.

regards, mathtalk-ga

---

Clarification of Question by sunandwavs-ga on 05 Dec 2003 11:25 PST

Hi Mathtalk

I was hoping to hear from you. Your reputation preceeds you!

Unfortunately the logic and related knowledge about this job are
undocumented and lost with my predecessor. So I am flying by the
breeches here.

Based on my analysis on the table data, sequence_number(NONULL)is
unique across the entire table and looks to be a valid PK/UK
candidate. Perhaps this could serve as a (correlating self) join key.
If the current indexes are not helpful, what index do you recommend I
create that would have the correct ordinality?

On a issue related note, I've just learned about the OPTION (LOOP
JOIN) T-SQL clause. Combined with MAXDOP 1, could this help in
Febrezeing this mess?

Thanks,
--Paul

UPDATE UTV_Partner_Account_Detail_All
SET Original_Access_Control_Id =
isnull(SubQuery.maxORIGINAL_ACCESS_CONTROL_ID,
UTV_PARTNER_ACCOUNT_DETAIL_ALL.access_control_ID)
FROM UTV_PARTNER_ACCOUNT_DETAIL_ALL
	INNER JOIN (SELECT max(lookup.ORIGINAL_ACCESS_CONTROL_ID) as
maxORIGINAL_ACCESS_CONTROL_ID, lookup.WNI_PARTNER_ACCOUNT_NUMBER,
lookup.ACCESS_CONTROL_ID
			FROM UTV_PARTNER_ACCOUNT_DETAIL_ALL as lookup
			INNER JOIN UTV_PARTNER_ACCOUNT_DETAIL_ALL ON 
				lookup.ACCESS_CONTROL_ID =
UTV_PARTNER_ACCOUNT_DETAIL_ALL.OLD_ACCESS_CONTROL_ID
				AND lookup.WNI_PARTNER_ACCOUNT_NUMBER
=	UTV_PARTNER_ACCOUNT_DETAIL_ALL.WNI_PARTNER_ACCOUNT_NUMBER
			GROUP BY  lookup.WNI_PARTNER_ACCOUNT_NUMBER, lookup.ACCESS_CONTROL_ID
		) as SubQuery ON SubQuery.WNI_PARTNER_ACCOUNT_NUMBER =
UTV_PARTNER_ACCOUNT_DETAIL_ALL.WNI_PARTNER_ACCOUNT_NUMBER
			AND UTV_PARTNER_ACCOUNT_DETAIL_ALL.OLD_ACCESS_CONTROL_ID =
SubQuery.ACCESS_CONTROL_ID


Without a dataset its hard to test properly.

Hi Nick,

Should I test your sql with the existing table DDL?

Thanks!