Solved

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

Posted on 2013-11-18
7
533 Views
Last Modified: 2013-11-24
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?
0
Comment
Question by:David L. Hansen
  • 4
  • 3
7 Comments
 
LVL 40
ID: 39658300
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?
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 39659699
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

0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 39659798
Going from 5 seconds to over 2 minutes is usually abnormal, but not unheard of.

The dba might be able to monitor the activity on the server and finds something that could be causing a bottleneck.

Since it looks as if you might be touching a lot of rows, is it possible that you get the problem mostly when there are many users on the system, while it works well when everybody is out? Locked record can slow down things a lot when you are doing batch jobs as you are doing.

Have you tried implementing everything inside of a Transaction? Because the locking mechanism is simplified during a transaction, things usually run faster. I have seen a huge set of operations go from 8 hours to less than 20 minutes simply by doing it in a transaction. It can lock out the users while it is running, but if it ends up always running in a few seconds or if you run it outside of work hours, then it could be problem solved.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 15

Author Comment

by:David L. Hansen
ID: 39660112
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?
0
 
LVL 40
ID: 39660190
"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.
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 0 total points
ID: 39661140
Found the problem!  And thank you so much for your help.  So...I have a method which runs before the stored proc.  This method parses and populates the Working_Dump table.  The first thing this parsing method does is delete all entries in the table that have the filename that it is going to parse.  Well, because of this I assumed (red flag here) that doing this would always give me a clean slate to work with (ie. an empty working_dump table).  But of course, that method doesn't clear out the table totally (but it does now) instead it just removed those rows related to that parsed file.  Anyway, I ended up with more and more stray rows in this table as I performed more and more tests.   With all of those update statements, no wonder it was struggling (there were about 10,000 such rows).  Oh, and the transaction approach seemed to help too.

Thanks!
0
 
LVL 15

Author Closing Comment

by:David L. Hansen
ID: 39672413
It works!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now