Link to home
Start Free TrialLog in
Avatar of David L. Hansen
David L. HansenFlag for United States of America

asked on

Intermittent timeout when running a stored proc through VB.net code

I am running a stored proc (which has about 20 update statements in it) through VB code.  Sometimes it works fine...other times it times out.  The only out of the ordinary thing that is going on is that there are many more update statements than a typical stored procedure.  Those statements are quite simple though and there are only a few thousand rows being dealt with at any one time.

I'm running VS2012 against SQL Server 2005.  Also, in testing, one specific set of inputs may work then fail...or fail, then work (or fail, fail, fail, then work)... no consistency there.

Ideas?
Avatar of Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger)
Flag of Canada image

A lot of thing can change the time a specific stored procedure takes to run. Triggers can fire in some runs that did not fire the previous run because the data is not the same. If as you say, the same procedure with the same set of data has inconsistent failures, it could be a problem with the hard disk. When you say "fail", what is the exact problem?

Is SQL Server installed on a dedicated server? If other applications are running on the same computer they could take time out of the microprocessor while you are executing the procedure. An anti-virus that starts a scan of the drive or backup software that starts doing it's job in the background can slow down things a lot. These are know to create situations such as "Sometimes it works fine...other times it times out".

Couldn't you split it up into a few stored procedures with a smaller set of updates in it?

What about increasing the timeout?
Avatar of David L. Hansen

ASKER

Good thoughts :)  So, Sql Server is running on a dedicated server (it has somewhere around 24 Gig).  The error is a simple timeout -- however, when it runs successfully it only takes about 5 seconds to run.  I've increased the timeout to 2 minutes and that doesn't seem to help.  There are several daily functions that run (my guess is that we have some sort of s.p. running every few minutes here) and this s.p. of mine seems to be the only one timing out.  Here is the code by the way (I've altered a few names just for posting):
USE [CTemp]
GO

--Name and stuff here
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[pr_Silly_Updates]
(@file varchar(52))
AS
SET nocount off;

--updating the ABC information FROM this file
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCAddress1 = permAddress1 WHERE ABCAddress1 = '' and orig_Filename = @file
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCAddress2 = perAddress2 WHERE ABCAddress2 = '' and orig_Filename = @file
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCCity = permCity WHERE ABCCity = '' and orig_Filename = @file
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCcounty = permCounty WHERE ABCcounty = '' and orig_Filename = @file
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCState = permState WHERE ABCState = '' and orig_Filename = @file
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCZip = permZip WHERE ABCzip = '' and orig_Filename = @file
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCCountry = permCountry WHERE ABCCountry = '' and orig_Filename = @file

--updating ABC information FROM owner dump
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCAddress1 = (SELECT permAddress1 FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (ABCAddress1 = '' or ABCAddress1 is null)
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCAddress2 = (SELECT perAddress2 FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (ABCAddress2 = '' or ABCAddress2 is null)
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCCity = (SELECT permCity FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (ABCCity = '' or ABCCity is null)
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCCounty = (SELECT permCounty FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (ABCcounty = '' or ABCcounty is null)
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCState = (SELECT permstate FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (ABCState = '' or ABCState is null)
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCZip = (SELECT permZip FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (ABCzip = '' or ABCzip is null)
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCCountry = (SELECT permCountry FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (ABCCountry = '' or ABCCountry is null)

--updating the license plate information
UPDATE CTemp.dbo.ABC_Working_Dump SET PLnum = (SELECT PLnum FROM CTemp.dbo.ABC_Vehic_Dump WHERE ABC_Working_Dump.shipNum = ABC_Vehic_Dump.shipNum) WHERE orig_Filename = @file and (PLnum = '' or PLnum is null)
UPDATE CTemp.dbo.ABC_Working_Dump SET PLnum = (SELECT PLnum FROM CTemp.dbo.ABC_Owner_Dump WHERE ABC_Working_Dump.shipNum = ABC_Owner_Dump.shipNum) WHERE orig_Filename = @file and (PLnum = '' or PLnum is null)

--replace all nulls in the file with empty strings so that padding works correctly
UPDATE CTemp.dbo.ABC_Working_Dump SET ABCAddress1 = '' WHERE ABCAddress1 is null 
UPDATE CTemp.dbo.ABC_working_dump SET ABCAddress2 = '' WHERE ABCAddress2 is null
UPDATE CTemp.dbo.ABC_working_dump SET ABCCity = '' WHERE ABCCity is null
UPDATE CTemp.dbo.ABC_working_dump SET ABCCountry = '' WHERE ABCCountry is null
UPDATE CTemp.dbo.ABC_working_dump SET ABCCounty = '' WHERE ABCCounty is null
UPDATE CTemp.dbo.ABC_working_dump SET ABCState = '' WHERE ABCState is null
UPDATE CTemp.dbo.ABC_working_dump SET ABCZip = '' WHERE ABCZip is null

UPDATE CTemp.dbo.ABC_Working_Dump
	SET PLnum = '' 
WHERE PLnum is null	

GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger)
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your help JamesBerger, wish I could say that did it...but no.  I ran it all in a single transaction and it ran good, then ran it again and it timed out as before.  BTW, when it succeeds it is always around 5 seconds regardless of the input file (they are all roughly the same size).  I just broke up the sections in the s.p. to use transactions in three different groups and that timedout as well.  One last thing, I see the same amount of successes and timeouts whether I run in production (lots of stuff going on all the time) or in Test (a separate server with little going on there) and the database I'm running this in is hardly used by anyone (production or test).  
Does that help any?
"input file"? What do you mean by that. I see only a filename as a parameter, but "input file" usually means that you are taking some input from a file. Could the problem come from there?

Otherwise, the only see I thing from your last comment that you have the same problem now matter how and where you run the thing, is that some of the values implicated trigger something in the background.

And there is still the suggestion that "The dba might be able to monitor the activity on the server and finds something that could be causing a bottleneck". This is not my expertise, but I understand that they have tools that can do that kind of analysis.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It works!