Time out when updating image field

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Time out when updating image field

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2004-06-26 : 00:10:32

I am running a statement like this:

update tblLBABanqueClient set ListeNA='0', ListeCommentaire='', DateSignature='20030201 00:00:00', ListeDocHeight='0', ListeDocWidth='0', ListeDoc=Null where IDLBABanque='40' and IDRacines='101'

The problem is with the ListeDoc field. It's an Image data type field. In this example I want to erase its content but my statement take ages and the times out. What should I do ?

Jean-Luc

jean-luc
www.corobori.com

Kristen
Test

22859 Posts

Posted - 2004-06-26 : 01:06:32

Can you tell me how big is the data for that particular column/row please?


SELECT DATALENGTH(ListeDoc)
FROM   tblLBABanqueClient
WHERE      IDLBABanque='40' 
       AND IDRacines='101'

Might be an idea to know the total IMAGE size usage too please:


SELECT SUM(DATALENGTH(ListeDoc))
FROM   tblLBABanqueClient
WHERE      ListeDoc IS NOT NULL

Kristen

Corobori
Posting Yak Master

105 Posts

Posted - 2004-06-26 : 08:52:10

[code]
SELECT DATALENGTH(ListeDoc)
FROM tblLBABanqueClient
WHERE IDLBABanque='40'
AND IDRacines='101'
[/code]
1338368

quote:

Might be an idea to know the total IMAGE size usage too please:

The same as above, I am currently testing stage. Indeed I was wondering if it's possible (and how) to compress the image prior to store it in the database.

jean-luc
www.corobori.com

Kristen
Test

22859 Posts

Posted - 2004-06-26 : 10:33:11

SO its about One Meg. Not that big then ... shouldn't be timing out.

If you are literally trying to store images in the database I would advise that you don't - instead store the path / name of the file (on some central storage); but it may well be that you have other reasons for doing this.

Kristen

Corobori
Posting Yak Master

105 Posts

Posted - 2004-06-26 : 11:15:12

quote:
If you are literally trying to store images in the database I would advise that you don't - instead store the path / name of the file (on some central storage); but it may well be that you have other reasons for doing this.

They are actually reasons to do so.

So why is it timing out then ? Is there another way to purge this image field I could try ? I was thinking of going the awful way of deleting the row and adding again

BTW I am going to try using Xceed stream compression to reduce image size. Should I ?

jean-luc
www.corobori.com

Kristen
Test

22859 Posts

Posted - 2004-06-26 : 12:33:33

I'd be intersted to know why storing the image in the DB is preferable for you to using the filesystem.

I presume that you have tried the UPDATE statement from Query Analyser (rather than, say, only from an application). This would show if there was a problem in the connection to application, rather than SQL itself, and might show a more detailed error message.

There is no other way to do what you want, AFAIK; furthermore I expect that DELETE/INSERT will take just as long to sort out the Image "housekeeping" as just setting it to NULL.

Kristen

Corobori
Posting Yak Master

105 Posts

Posted - 2004-06-26 : 13:32:25

1) The application I am writing will contain some confidential data and my customers wants to minimize the possibility of seeing images, actually scanned documents, all over the place. Also the backup and restore functions that we have would be much more complex than they are now. BTW I didn't tell that the application is a standalone thing running MSDE. According to what I found out I can compress the data to a decent size (about 100k) so it might help.

2) Yes I did try in the Query analyser and yes it was taking so long as in the app

3) Your point is good. Strange enough Inserting the image, also with and Update query, just take no time at all.

jean-luc
www.corobori.com

Kristen
Test

22859 Posts

Posted - 2004-06-26 : 15:00:08

I suppose the location where the IMAGE stuff is being stored is not "slow" is it? Can't think how it could be - but if its across a network share or somesuch that could be trouble (but I doubt you can configure SQL to do this).

There is some hint that will allow the image to be stored within the PAGE if it is small.

quote:

http://www.sql-server-performance.com/sql_2000.asp
This is especially true if the TEXT, NTEXT, or IMAGE data is not large, and if the data is frequently accessed. In this case, it is often better to store this data in the table itself, instead of separate data pages. The reason for this is because it is faster for SQL Server to retrieve data directly from the table than retrieving it from separate data pages, as described above.

Starting with SQL Server 2000, you now have the option to store TEXT, NTEXT, and IMAGE data directly in a table instead of in separate data pages. If you want to turn this option on, you will have to set the "text in row" table option to permit this. For example:

sp_tableoption 'tablename', 'text in row', 'on'

or

sp_tableoption 'tablename', 'text in row', 'size'

WHERE 'size' is a value ranging from 24 to 7,000 bytes.

Might be worth trying adding a row with an image of various sizes and seeing if the DELETE is still slow

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources