|
Subject:
|
Stored procedure timing out
|
|
Posted By:
|
pankaj_daga
|
Post Date:
|
1/1/2004 4:46:59 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
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/2/2004 9:39:57 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.
|
|
Reply By:
|
bmains
|
Reply Date:
|
1/2/2004 10:44:06 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
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
1/2/2004 11:55:54 AM
|
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:
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
|
|
Reply By:
|
pankaj_daga
|
Reply Date:
|
1/2/2004 2:53:32 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
|
