Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jvh
Starting Member
1 Post |
 Posted - 2005-10-04 : 11:12:33
|
| Hi, I have currently made an astonishing experience with deletingrows in a table where huge binary data (>250 MB) is contained in.I select one single row by its unique primary key, and the time to delete it exceeds several seconds (up to 15 secs on my machine which is not a slow one). It seems not to make a difference if I set the binary field to NULL before and delete it then.Does anyone know a nice "trick" how to deal with this in common,meaning how to speed up this to an acceptable performance. Should I change to some "newer" SQL Server? Anyone has experiences with this scenario on newer versions?Background: we want to use the database as a repository to manageall kinds of data (i.e. huge video streams). As Microsoft is showing us in their bright idea of the future (WinFS), the filesystem is out in few months and everything is managed in databases. Good idea, but if I delete a file on my filesystem having such a size, itis gone in almost no delay (ok. I have not transaction there). I would assume, the database is not that much slower at all, but I badly failed :-( and I assume my customer is astonished as well.cheersJörn |
|
|
david.poole
Starting Member
1 Post |
Posted - 2005-10-04 : 11:33:21
|
| What service pack are you running.The fix list for SP4 suggests that there are a number of fixes for slow performance.As a design preference I tend to keep large image/text fields in a separate table albeit with a one to one relationship with the main table.There is a "text in row" option. See "sp_tableoption" that allows you to specify that image data below a certain amount gets stored in the table itself rather than the pointer to the text/image data. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-05 : 01:35:41
|
| Did you use proper index in your table?Are you deleting all the records or only some records?If some records, use the index column in the where conditionMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-05 : 04:03:30
|
| I reckon its the time to copy it to the Log to allow rollback / TLog backup.Putting the LDF file on a different drive system to the MDF might helpKristen |
|
|
|
|
|
|
DELETE is quite slow (SQL Server 2000)