Prev: Whoever posted this
Next: Help with duplicate fields
From: mirthcyy on 3 Mar 2010 11:57
hi guys,

I need to remove old data on regular basis from a big table (over 50
million) with a large text column. That column has average around
3000 charaters. The deletion is painful. It takes a minute or more to
delete just 5000 rows. Since there should be no down time for
production, all I can do now is to delete in small batches. And the
table is not partitioned so I can't use partition switch.

Do you know if there's any better way to do the deletion? Should I
convert this text field to varchar(max)?

Thanks.
From: yssr83 on 3 Mar 2010 13:03
Hi,

If it alwaays needs to be live, then is suppose kind do much. But if
it's not.

You can take a backup, truncate, replace with wanted records.
drop and re create indexes and constraints.

and try other options

For now. try to a performance test by changing the data type to
nvarchar(max) on a test machine before...

Sriram
www.sqllike.com
From: Andrew J. Kelly on 3 Mar 2010 15:42
Why don't you partition this? But the most likely cause of the slowness is
not having the log file isolated from the data and on a fast disk array
since this action is fully logged.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"mirthcyy" <mirthcyy(a)gmail.com> wrote in message
news:c12620c4-06e9-4fb2-8276-53eea6fb154f(a)i25g2000yqm.googlegroups.com...
> hi guys,
>
> I need to remove old data on regular basis from a big table (over 50
> million) with a large text column. That column has average around
> 3000 charaters. The deletion is painful. It takes a minute or more to
> delete just 5000 rows. Since there should be no down time for
> production, all I can do now is to delete in small batches. And the
> table is not partitioned so I can't use partition switch.
>
> Do you know if there's any better way to do the deletion? Should I
> convert this text field to varchar(max)?
>
> Thanks.

From: mirthcyy on 3 Mar 2010 16:56
Log and data files are on separate disk drive.

Actually I tried to partition the table. Since the deletion needs to
be done on regular basis, I tried to implement an automatic sliding
window partition process. Partition is based on an int field. when I
recreate clustered index with the partition, it doesn't take that long
but splitting the partition takes forever to finish. It just doesn't
make sense.





On Mar 3, 2:42 pm, "Andrew J. Kelly" <sqlmvpnooos...(a)shadhawk.com>
wrote:
> Why don't you partition this?  But the most likely cause of the slowness is
> not having the log file isolated from the data and on a fast disk array
> since this action is fully logged.
>
> --
>
> Andrew J. Kelly   SQL MVP
> Solid Quality Mentors
>
 | 
Pages: 1
Prev: Whoever posted this
Next: Help with duplicate fields