Delete Takes Time when u have text datatype column

I have a table with a coulumn of Text Datatype. This column is stored with XML data. Now i need to delete records from this table using a SP. The deletion 60K records is taking 30 min almost. Generally this table will not have this much deletion. But when there is no Text data type column (i removed the column from the table for testing) the same deletion is taking only few sec. What is the techinical reason behind it.

If anyone could explain this it would be really helpful

Leena

See Text datatype in Books Online. It is one of the BLOB datatype, it depends with the option you set in the table for large objects.

Micrsoft recommand to switch over from Text/Ntext to Varchar(Max)/NVarchar(Max) - if you use sql server 2005.

|||

It really is all depending on the size of the data in your text datatype. Data is stored in ~8K pages, and if you have 80K worth of text in columns, it will require 10 pages to be deleted to delete the row. Your table will look something like (and this can get ugly for 60K rows):

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

And all of the pages would have to be deleted. I think this might be improved upon performance wise in 2005, but I am not 100% sure. There is a concept of Ghost rows that may apply here where pages are simply marked as deleted. You should also try using the varchar(max) datatype if at all possible, though it has the same issues.

|||

thanks mani and louis for quick help.

one more q..... is sp_spaceused reflect the text data storage space also. If not how can we findout that.

the table with Text data and with out text data is almost showing same space used. Why its so..

Thanks again

Leena

|||

The large datatype value is stored outside of your table row & your current tables row's keeping the pointer of the outside stored values. To change this settins use the following statement

Exec sp_tableoption N'MyTable', 'large value types out of row', OFF

Exec sp_tableoption N'MyTable', 'text in row', ON

|||

When the BLOB's are not stored in row you have a lot of random I/O going on too.

Random I/O are very expensive of course. It has to check for each record where the BLOB data is located, remove it, go to the next record, check where the BLOB data is located, remove it, ... well you get my point I hope :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com