Stored procedure timing out

pankaj_daga · January 1st, 2004, 05:46 PM

Hi,

I have a stored procedure problem. I have a C++ application that calls a stored procedure. However, many times the application just terminates as the stored procedure times out. It does not happen all the times, but quite frequently (~30%). It never happens when I run the stored procedure from the SQL server osql console app. I do not understand why it should time out. Any suggestions on this would be highly appreciated.

Here is the stored procedure:

--------------------------------------------------------------------
CREATE PROCEDURE DeleteTicketData
@ticketNum int
AS
declare @testVorgangID int
-- delete errors
DELETE FROM TestVorgangEinleseFehler_T WHERE TicketNumber = @ticketNum
Select @testVorgangID = TestVorgangID from TestTickets_T WHERE TestTicketID = @ticketNum
IF (@testVorgangID IS NOT NULL)
BEGIN
  declare @testID int
  DELETE From TestVorgaenge_T where TestVorgangID = @testVorgangID
  DECLARE tests_cursor CURSOR FOR
  SELECT TestID FROM Tests_T WHERE TestVorgangID = @testVorgangID
  Open tests_cursor
  -- Perform the first fetch
  FETCH NEXT FROM tests_cursor INTO @testID
  -- as long as there are more rows to fetch.
  WHILE @@FETCH_STATUS = 0
  BEGIN
    DELETE FROM TestAktionen_T Where TestID = @testID
    DELETE FROM TestDSPACETable_T Where TestID = @testID
    DELETE FROM TestEreignisse_T Where TestID = @testID
    DELETE FROM TestZustaende_T Where TestID = @testID DELETE Tests_T WHERE CURRENT OF tests_cursor
    FETCH NEXT FROM tests_cursor INTO @testID
  END

  CLOSE tests_cursor
  DEALLOCATE tests_cursor
  END
GO
--------------------------------------------------------------------

I am totally puzzled as to why it should time out some of the times. Any suggestions would be really appreciated.

Thanks and have a great new year everyone :)

Pankaj

Imar · January 2nd, 2004, 10:39 AM

Hi there,

Why are you using a cursor for these delete queries? Some simple joins and/or IN selects should do the trick as well.

Cursors are usually pretty slow, so maybe that's what's causing the time-out.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

bmains · January 2nd, 2004, 11:44 AM

Another alternative is you could store the ID's that you want to delete into a table variable or temp table, and then do a:

DELETE FROM TestAktionen_T Where TestID in (select ID from @tablevariable)

Not sure if this is what you are looking for.....

Brian

Jeff Mason · January 2nd, 2004, 12:55 PM

DELETE operations in SQL Server can be inherently slow; you are not helping things by using a cursor as well. As Imar suggested, you don't need a cursor - simply JOIN each of your tables to the 'source' table. The results of the JOIN then specify the rows to be deleted. For example:

Code:

DELETE FROM TestAktionen_T
    INNER JOIN Tests_T ON Tests_T.TestID=TestAktionen_T.TestID
    INNER JOIN TestTickets_T ON Tests_T.TestVorgangID=TestTickets_T.TestVorgangID
WHERE TestTickets_T.TestTicketID=@ticketNum

etc.

The DELETE operation must maintain all the index entries on all the rows being deleted, if you have a lot of indexes, this will take some time. If there is other user activity, the DELETE may be blocking until it is able to obtain exclusive locks and this will take time as well.

You might try extending the query timeout on the command object.

Removing the cursor should speed things up considerably, though.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

pankaj_daga · January 2nd, 2004, 03:53 PM

Cool.

I will first try removing the cursor object. Maybe that is what is making things behave strange.

Thanks everyone for the replies. I really appreciate it. You guys are the best :-)

Pankaj