Can you explain more? I did some development of similar processes, and they had such slow down...

>I think my co-worker Kevin found it.....tried inserting to a #1 and 00:25 seconds.
>
>
>>If i include the insert it is 10:00 Minutes with out the insert it is 00:18 seconds...why so slow - 42,000 records??? No indexes???
>>
>>Thanks Shawn
>>
>>

>>CREATE PROCEDURE ROP_Process_Alignment
>>@Override_Option AS TINYINT
>>AS
>>/**
>>Created By  : Shawn Dorion
>>Created On  : September 20, 2001
>>Description : This procedure will re-align the data on the transaction tables.
>>		Below is the logic for this process.
>>
>>	Logic for ROP Alignment
>>	-----------------------
>>	STEP 1
>>	Get min date from rop where switch is not aligned
>>		this is the starting date
>>
>>	STEP 2
>>	get txs from min date forward - tcnum must be employee rate and percentage
>>	for percentage take new_rop * tcnum percentage and the use this rate.
>>
>>	STEP 3
>>	get hrate for clasnum, txdate regardless of alinment and record as ROP_New
>>
>>	STEP 4
>>	get hrate for clasnum, txdate less than effectived and  alignment = 1 record as ROP_Old
>>
>>	STEP 5
>>	Tests
>>	If ROP_TX = ROP_Old		&& no chnage
>>		make ROP_Tx = ROP_New
>>	ELSE	&& ROP_TX != ROP_OLD -- Out of grade - user changed
>>		If Setting is override out of grade
>>			 MAKE ROP_Tx = ROP_New
>>		 ELSE
>>			IGNORE
>>
>>	STEP 6
>>	ADD Note to mod_log about aligment
>>
>>	RETURN completed
>>
>>	PARAMETERS DEFINITION
>>	----------------------
>>	@Override_Option - Override Option - This value will tell the procedure to update a record with or not with a new rop for records that are out of grade (user changed)
>>
>>	Called From : rop_alignment
>>**/
>>
>>SET NOCOUNT ON
>>--declare the vars need for the procedure
>>DECLARE
>>	@MinRopDate		CHAR(10),
>>	@Rop_New		MONEY,
>>	@Rop_Old		MONEY,
>>	@tx_txnum		CHAR(10),
>>	@tx_clasnum		CHAR(10),
>>	@tx_txdate		CHAR(10),
>>	@tx_Rop		MONEY,
>>	@tx_tcnum		CHAR(2),
>>	@tx_Erate		NUMERIC(10,4),
>>	@tx_Hrate		NUMERIC(10,4),
>>	@Hold_Rop		MONEY
>>
>>IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Temp_ROP_Align]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
>>BEGIN
>>--this table is dropped at the end of this process
>>--if the table is here then we have to return a failure
>>--as the process is being run by someone already
>>RETURN -999999
>>END
>>
>>CREATE TABLE [dbo].[Temp_ROP_Align]
>>	(in_use TINYINT NULL)
>>
>>--IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Temp_Process_Rop]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
>>/**
>>CREATE TABLE [dbo].[Temp_Processed_Rop]
>>	(txnum CHAR(10) NULL,
>>	rop MONEY NULL,
>>	rop_hold MONEY NULL,
>>	tx_date CHAR(10) NULL,
>>	tx_clasnum CHAR(10)
>>	)
>>
>>CREATE INDEX Processed_ROP ON [dbo].[Temp_Processed_Rop] (txnum)
>>**/
>>DELETE FROM Temp_Processed_Rop
>>
>>-- get the earliest date from ROP where the data has been realigned
>>-- 1 = not aligned, 0 = aligned
>>SELECT @MinRopDate = MIN(effectived) FROM ROP where aligned = 1
>>IF @MinROpDate IS NULL
>>  BEGIN
>>	-- set the date to the begining of time
>>	SET @MINRopDate = '1760/01/01'
>>  END
>>
>>-- Types of Erates - Earnings Rate
>>--erate = 1 - employee rate		erate = 2 - fixed rate		erate = 3 - percentage
>>--get all tcnum that are percentage and employee rate
>>SELECT code,hrate,erate INTO Temp_TimeCode FROM timename WHERE erate IN (1,3)
>>
>>DECLARE Temp_Rop_tx CURSOR FOR
>>-- get all txs that meet the time code criteria
>>SELECT t.txnum, t.clasnum, t.txdate, t.rop, t.tcnum, tc.erate, tc.hrate
>>	FROM tctxy1 t, Temp_TimeCode tc where tcnum = tc.code
>>--UNION
>>--SELECT t.txnum, t.clasnum, t.txdate, t.rop, t.tcnum, tc.erate, tc.hrate
>>--	FROM tctxy2, Temp_TimeCode tc where tcnum = tc.code
>>--UNION
>>--SELECT t.txnum, t.clasnum, t.txdate, t.rop, t.tcnum, tc.erate, tc.hrate
>>--	FROM tctxy3, Temp_TimeCode tc where tcnum = tc.code
>>--UNION
>>--SELECT t.txnum, t.clasnum, t.txdate, t.rop, t.tcnum, tc.erate, tc.hrate
>>--	FROM tctxy4, Temp_TimeCode tc where tcnum = tc.code
>>ORDER BY txdate,clasnum
>>OPTION (MAXDOP 1)
>>
>>OPEN Temp_Rop_Tx
>>-- Perform the first fetch.
>>FETCH NEXT FROM Temp_Rop_Tx
>>	INTO
>>	@tx_Txnum	, 	@tx_Clasnum	, 	@tx_Txdate	, 	@tx_Rop	,	@tx_Tcnum	,
>>	@tx_Erate	,	@tx_Hrate
>>
>>-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
>>WHILE @@FETCH_STATUS = 0
>>BEGIN
>>	-- get the new rop
>>	SELECT @Rop_New = r.hrate
>>		FROM rop r
>>		WHERE
>>		r.clasnum = @tx_clasnum AND
>>		effectived =
>>			(SELECT MAX( effectived) FROM rop r2 WHERE r2.clasnum = r.clasnum and effectived <= @Tx_txdate)
>>
>>	-- if we didn't find an rop set the rop to null
>>	IF @Rop_New IS NULL
>>		SET @Rop_New = 0
>>
>>	-- get the old rop
>>	SELECT @Rop_Old = r.hrate
>>		FROM rop r
>>		WHERE
>>		r.clasnum = @tx_clasnum AND
>>		effectived =
>>			(SELECT MAX( effectived) FROM rop r2 WHERE r2.clasnum = r.clasnum and effectived <= @Tx_Txdate and aligned = 0)
>>	-- if we didn't find an rop set the rop to null
>>	IF @Rop_Old IS NULL
>>		SET @Rop_old = 0
>>
>>	--store the orginal value used for testing comparisons
>>	SET @Hold_Rop = @Tx_Rop
>>
>>	If @Tx_Rop = @Rop_Old		-- no chnage
>>	  BEGIN
>>		IF @tx_Erate = 1		--Employee Rate
>>			SET @tx_Rop = @Rop_New
>>		ELSE				-- Percentage
>>			SET @tx_Rop = @Rop_New * @tx_Hrate
>>	  END
>>	ELSE	-- @Tx_Rop != @Old_Rop 	Out of grade - user changed
>>	  BEGIN
>>		IF @Override_Option = 1 --Setting is override out of grade
>>		  BEGIN
>>			IF @tx_Erate = 1		--Employee Rate
>>				BEGIN
>>				SET @tx_Rop = @Rop_New
>>				END
>>			ELSE				-- Percentage
>>				BEGIN
>>				SET @tx_Rop = @Rop_New * @tx_Hrate
>>				END
>>		 END
>>	  END
>>	INSERT INTO Temp_Processed_Rop (txnum, rop, rop_hold, tx_date, tx_clasnum)
>>		VALUES (@tx_txnum, @tx_rop, @Hold_Rop, @tx_Txdate, @tx_clasnum)
>>	PRINT @tx_txnum + ' - ' + @tx_Txdate + ' - ' + @tx_clasnum
>>	-- This is executed as long as the previous fetch succeeds.
>>	FETCH NEXT FROM Temp_Rop_Tx
>>		INTO
>>		@tx_Txnum	, 		@tx_Clasnum	, 		@tx_Txdate	, 		@tx_Rop	,
>>		@tx_Tcnum	, 		@tx_Erate	,		@tx_Hrate
>>END
>>
>>CLOSE Temp_Rop_Tx
>>DEALLOCATE Temp_Rop_Tx
>>
>>IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Temp_ROP_Align]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
>>DROP TABLE [dbo].[Temp_ROP_Align]
>>
>>IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Temp_TimeCode]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
>>DROP TABLE [dbo].[Temp_TimeCode]
>>
>>