Bulk Update Fails
-
I am trying to run a bulk update on a table by picking up the IDs that I've populated onto a table variable based on some criteria. The update just works fine if the number of records in the table variable are < = 1000. When this number increases to 5000, the update does'nt work. When I try to debug my code, I get the message box stating
"Error while executing 'A Batch Cmd' at line 0; Sql Error: 0 "The statement has been terminated."On catching this execption I get the message"Timeout expired.The Timeout period elasped prior to completion of the operation of the operation or the server is not responding.\r\n The statement has been terminated."When I try to run the stored proc from the query analyzer, it keeps processing for a long time and that I've to manually stop the execution. On trying to run the queries that I have in the stored proc individually did'nt yield me positive results either. I've ensured that my stored proc meets the specifications listed here[^] Can anyone throw some light on what can be tried to fix this? Environment: VS 2005 Pro, Sql Server 2000 -
I am trying to run a bulk update on a table by picking up the IDs that I've populated onto a table variable based on some criteria. The update just works fine if the number of records in the table variable are < = 1000. When this number increases to 5000, the update does'nt work. When I try to debug my code, I get the message box stating
"Error while executing 'A Batch Cmd' at line 0; Sql Error: 0 "The statement has been terminated."On catching this execption I get the message"Timeout expired.The Timeout period elasped prior to completion of the operation of the operation or the server is not responding.\r\n The statement has been terminated."When I try to run the stored proc from the query analyzer, it keeps processing for a long time and that I've to manually stop the execution. On trying to run the queries that I have in the stored proc individually did'nt yield me positive results either. I've ensured that my stored proc meets the specifications listed here[^] Can anyone throw some light on what can be tried to fix this? Environment: VS 2005 Pro, Sql Server 2000Can you post some of the code up here so we can take a look?
-
Can you post some of the code up here so we can take a look?
andyharman wrote:
Can you post some of the code up here
Yes,sure. The following lists the logic and flow of my proc. .... Proc declarations go here ....
Begin Transaction
Declare @tblCategory Table (CategoryID BigInt)
Insert into @tblCategory(CategoryID)
Select A.CategoryID from Shop A Where ProductName = @pProdNameIf exists(select categoryId from @tblCategory)
begin
Declare @tblLocalities Table(CategoryID BigInt, LocalityID int)
Insert into @tblLocalities (categoryid,localityid)
select B.CategoryID, B.localityId from ShopGroup B, Shop A
where A.CategoryID = B.CategoryID and A.ProductName = @pProdNameIf @@rowcount > 0
begin
Update Shop Set Ranking = 2 where categoryID in
(select t.CategoryID from @tblLocalities t)
end
end
If @@error > 0
Begin
rollback transaction
end
else
begin
commmit transaction
end -
I am trying to run a bulk update on a table by picking up the IDs that I've populated onto a table variable based on some criteria. The update just works fine if the number of records in the table variable are < = 1000. When this number increases to 5000, the update does'nt work. When I try to debug my code, I get the message box stating
"Error while executing 'A Batch Cmd' at line 0; Sql Error: 0 "The statement has been terminated."On catching this execption I get the message"Timeout expired.The Timeout period elasped prior to completion of the operation of the operation or the server is not responding.\r\n The statement has been terminated."When I try to run the stored proc from the query analyzer, it keeps processing for a long time and that I've to manually stop the execution. On trying to run the queries that I have in the stored proc individually did'nt yield me positive results either. I've ensured that my stored proc meets the specifications listed here[^] Can anyone throw some light on what can be tried to fix this? Environment: VS 2005 Pro, Sql Server 2000Yes, inserting or updating a lot of records will take a lot of time, particularly if running other queries concurrently against the same records (which may temporarily block your updates due to locks). ADO.NET stops your application from being blocked forever by imposing a configurable command timeout, which you set in the SqlCommand's CommandTimeout property. It defaults to 30 seconds, so if your command takes more than 30 seconds to execute you get a SqlException. Management Studio assumes you're more interested in the results than in a speedy response and doesn't time out.
DoEvents: Generating unexpected recursion since 1991 -
Yes, inserting or updating a lot of records will take a lot of time, particularly if running other queries concurrently against the same records (which may temporarily block your updates due to locks). ADO.NET stops your application from being blocked forever by imposing a configurable command timeout, which you set in the SqlCommand's CommandTimeout property. It defaults to 30 seconds, so if your command takes more than 30 seconds to execute you get a SqlException. Management Studio assumes you're more interested in the results than in a speedy response and doesn't time out.
DoEvents: Generating unexpected recursion since 1991Mike Dimmick wrote:
Yes, inserting or updating a lot of records will take a lot of time, particularly if running other queries concurrently against the same records
Thanks on your views Mike. I will now need to figure out a way to fine tune my proc.
Mike Dimmick wrote:
Management Studio assumes you're more interested in the results than in a speedy response and doesn't time out
Do we have any settings / system procs that will take care of the time-out issue in Sql Server 2000?
-
andyharman wrote:
Can you post some of the code up here
Yes,sure. The following lists the logic and flow of my proc. .... Proc declarations go here ....
Begin Transaction
Declare @tblCategory Table (CategoryID BigInt)
Insert into @tblCategory(CategoryID)
Select A.CategoryID from Shop A Where ProductName = @pProdNameIf exists(select categoryId from @tblCategory)
begin
Declare @tblLocalities Table(CategoryID BigInt, LocalityID int)
Insert into @tblLocalities (categoryid,localityid)
select B.CategoryID, B.localityId from ShopGroup B, Shop A
where A.CategoryID = B.CategoryID and A.ProductName = @pProdNameIf @@rowcount > 0
begin
Update Shop Set Ranking = 2 where categoryID in
(select t.CategoryID from @tblLocalities t)
end
end
If @@error > 0
Begin
rollback transaction
end
else
begin
commmit transaction
endI'm trying to understand what your code is trying to achieve. As far as I can work out, the following would do the same: UPDATE Shop SET Ranking = 2 WHERE CategoryID in ( SELECT B.CategoryID FROM Shop A INNER JOIN ShopGroup B ON A.CategoryID = B.CategoryID WHERE A.ProductName = @pProdName ) Is this any faster? How many rows does it update? What is the purpose of linking to "ShopGroup"? The next step would probably to look at the indexes. Regards Andy