Removing All Image Data

Question

Post reply

Removing All Image Data

Melissa Martin

Valued Member

Points: 65
More actions
March 14, 2008 at 12:36 pm

#69621

I am trying to update a million rows that contain an image column in SQL 2000. I am replacing the data in the image column with an empty string using the following commands:
SELECT @ptrval = TEXTPTR([image])
FROM table
WHERE table_ID = @df_ID
WRITETEXT table.[image] @ptrval ''
My problem is that I need to do this in a one hour maintenance window. I have tried numerous ways of accomplishing this task. I have even tried deleting in batches and reinserting. The deleting takes too long. Any ideas on how I can accomplish such a task in the maintenance window?

Viewing 12 posts - 1 through 12 (of 12 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Does it all need to be done in a single maintenance window?

If not, consider breaking it up into multiple maintenance windows.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Sergiy SSC Guru Points: 110209 More actions · Answer 2

Create a new table table_Copy

Copy all data from table to table_Copy except image.

Drop table, rename table_Copy to table.

If you've got some FK or other constraints don't forget to drop and recreate them as well.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 3

I agree with Sergiy... and, if you use SELECT INTO, you will have about 58 minutes left to ensure the FK's have been correctly instantiated and to do some regression testing.

--Jeff Moden

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

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

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

Melissa Martin Valued Member Points: 65 More actions · Answer 4

All great ideas. But, ideas i've tried. The problem is that these are not the only records in the table. I have over 3 million recs in the table, but I am only clearing out 1 million. Moving the recs to the temp table is not a problem, it's deleting only them from the permanenat table that is the issue. Here is the sql I used to accomplish these tasks.

SELECT INTO.......without image

CREATE INDEX on tmp table

DELETE FROM......WHERE NOT IN ( SELECT FROM tmp table)

INSERT INTO......SELECT from tmp table

Melissa Martin Valued Member Points: 65 More actions · Answer 5

It doesn't all need to be done in a maintenance window. I have actually tried to "trickle" it. With this way I have to make sure there are no locking problems or performance degredation of the system. It seems running it this way after about 6 hours I start to run into performance issues. CPU starts to spike and response time lengthens.

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

Alright... sorry... thought the million rows was the whole table.

You need to build an update crawler for this, especially if the table is an "online" table being accessed by others. Something like this...

--===== Limit all further queries, including deletes, to 25,000 rows

SET ROWCOUNT 25000

--===== See if any rows qualify for update. If even just one exists,

-- then there's work to do and @@ROWCOUNT will be > 0.

-- DO NOT PUT ANY CODE BETWEEN THIS SELECT AND THE WHILE LOOP OR

-- YOU'LL MESS UP THE ROWCOUNT FOR THE WHILE LOOP

SELECT TOP 1 1 FROM dbo.yourtable WHERE someconditionexists

--===== If the rowcount from the above is greater than 0,

-- then update 25,000 rows at a time until there's nothing

-- left to update

WHILE @@ROWCOUNT > 0

BEGIN

--===== This delay gives other processes breathing room

WAITFOR DELAY '00:00:10'

--===== Do the delete. Will be limited by the SET ROWCOUNT above.

-- DO NOT PUT ANY CODE BETWEEN THIS DELETE AND THE "END" OR

-- YOU'LL MESS UP THE ROWCOUNT FOR THE WHILE LOOP.

UPDATE dbo.yourtable WITH (TABLOCKX)

SET yourimagecolumn = whatever

WHERE somedontitionexists

END

--===== Restore the ability to process more than 25,000 rows

SET ROWCOUNT 0

Before running such a thing, you should experiment with how many rows you can update and how long it takes. Try 500, 1000, 5000, 10000... you get the idea. Then, set the first line of code to that. You can also experiment with the delay.

You shouldn't update for more than about a second... the TABLOCKX I used can be removed but I put it there to keep the possibility of deadlocks and rollbacks at a very minimum. Code would do an update for about 1 second, then let other stuff run for about 10 seconds so the other stuff doesn't get behind.

--Jeff Moden

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

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

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

Melissa Martin Valued Member Points: 65 More actions · Answer 7

I tried to be vague to see if anyone had other ideas. I would LOVE for the million rows to be the whole table, but there is always a complication, right. Anyway this is along the lines of what I was doing. Not sure how the exclusive lock will go over. Do you prefer using the update versus the writetext command?

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 8

Doesn't WriteText only affect one row?

--Jeff Moden

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

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

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

Melissa Martin Valued Member Points: 65 More actions · Answer 9

yeah. Good point. What about the performance issues. Have you heard of anything like that happening when running the script for a significant amount of time?

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 10

Yes... transaction log builds up... previous code tried to do it all at once and didn't let anyone else in... etc, etc. The crawler I suggest will avoid all of that all thought it will take some time... neat thing is if your conditions are right, you can restart it and it will pick right up skipping the ones that have already been done.

--Jeff Moden

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

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

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

Melissa Martin Valued Member Points: 65 More actions · Answer 11

Melissa Martin

Valued Member

Points: 65

March 17, 2008 at 7:18 am

#790318

Will give it a try and let you know. Thank You.