Update event Set description = replace(description, 'dpweb1.dp', 'utdirect')

• There are no indexes on the Event table.


• There are about 1500 rows


• I am not sure how many bytes wide the table is but I added together the length of each column and got 964.


• By cost do you mean how many rows contain the text to be updated? I count 711 rows.

USE Northwind
GO
DECLARE @x int,@start datetime, @end datetime 
 SELECT @x=0, @start = Getdate()

SET NOCOUNT ON

SELECT * INTO myTable99 FROM ( 
SELECT *, REPLICATE('X',4000) AS colx FROM [order details] 
) AS xxx


SELECT COUNT(*) FROM myTable99

UPDATE myTable99 SET colx=REPLACE(colx,'X','A')

SELECT 'Time Elapsed (ss): '+ Convert(varchar(25),DATEDIFF(ss,@start,getdate()))
GO

DROP TABLE myTable99
GO

SET NOCOUNT ON

CREATE TABLE event
(
Column1 INT NOT NULL,
description VARCHAR(500) NOT NULL
)

DECLARE @int INT

SET @int = 1

WHILE @int <= 1500
BEGIN
	INSERT INTO event VALUES(@int, CONVERT(VARCHAR(5), @int) + REPLICATE('X', 50) + 'dpweb1.dp' + CONVERT(VARCHAR(5), @int) + REPLICATE('Z', 5))
	SET @int = @int + 1
END

SELECT Column1, description
FROM event

UPDATE event 
SET description = REPLACE(description, 'dpweb1.dp', 'utdirect')

SELECT Column1, description
FROM event

DROP TABLE event

quote:

---

Originally posted by jmcbride

Thanks for the help, I temporarily disabled access by users and the query ran in under a second! Sorry about the trouble.

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

---