• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

SQL Server 2005 - deleting images from a database

Database:  SQL Server 2005

We are currently trying to build a SQL statement to delete image records in a database, related to other tables related via foreign keys.  We are successful in deleting regular data from tables, but when it comes to deleting from tables with images stored inside of them, the statement is timing out on us.  I have tried many different solutions (running the SQL code in the code-behind of the windows service, increasing/decreasing the rowcount, but still have the problem of timing out.)

This query is built into a stored procedure, and being called by a windows service that we have created as well.

Here is the error we are receiving:
System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is the query being ran:
SET ROWCOUNT 50

WHILE EXISTS
(SELECT DISTINCT ID FROM Images_Table WHERE ID NOT IN (SELECT num FROM CurrentIDs))
    BEGIN
        DELETE FROM Images_Table WHERE ID NOT IN (SELECT num FROM CurrentIDs)
    END
0
rudyflyer
Asked:
rudyflyer
  • 3
  • 2
1 Solution
 
YveauCommented:
try this:

declare @count int
decalre @batch int

select  @batch = 3
,       @count = (      select count(id)
      from   Images_Table I
      left   join CurrentIDs C
      on     I.ID = C.num
      where  C.num is NULL)
if @count > @batch
begin
      select @count = @batch
end

while @count > 0
begin
      delete from Images_Table
      where ID = (select min(id)
      from   Images_Table I
      left   join CurrentIDs C
      on     I.ID = C.num
      where  C.num is NULL)

      select @count = @count - 1
end

You can set the batch size using the @batch parameter.
If you will not make it very big as in the example you will be able to delete some rows at a time.
The problem is that the connection times out before all records are deleted.
... or you should change the timeout on your connection ...

Hope this helps ...
0
 
David ToddSenior DBACommented:
Hi,

Any chance that the database is on autoshrink? That wont be helping performance any if it is.

Regards
  David
0
 
rudyflyerAuthor Commented:
1)  I don't think that our database is on autoshrink, where would I check that at?  (none of us here are database experts, but are decent at database work.  i know i personally haven't messed with autoshrink before)

2)  I will try this query out shortly, and come back afterwards.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
rudyflyerAuthor Commented:
hello,

I have an update here, I may be mis-understanding things.  I first left the batch size set as 3, and it only deleted the 3 images.  what we are wanting basically is for this to continue to loop through, until there are no more images that need to be deleted.  so, I tried to increase the batch size to 100, and got the timeout error again.
0
 
YveauCommented:
That is exactly the problem !
Don't try to remove everything at once, that will take too long for the batch to come with a result and your connection is timing out on that. So either remove everything in small batches, or increase the timeout on your connection.

Hope this helps ...
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now