Delay in calls to stored procedure

[Joe]

I'm running through a loop calling a stored procedure each time. I prepare
the SqlCommand before entering the loop and just change the param values in
the loop before each call to ExecuteNonQuery(). The problem I get is it's
taking anywhere from 1/4 - 1/2 second before each call is made. I verified
this using the SQL Profiler and comparing the start times of each call. The
start and complete times are the same so the SP is executing very fast.

What's even weirder is that this doesn't happen all the time. Some times it
runs very, very fast.

The application is being run on the database server and I'm using the IP
address in the ConnectionString.

The loop its self it pretty simple

foreach (Item i in Items)
{
int itemRow = dataview.Find(a string) // note this dataview contains
8800 records

if (itemRow == -1)
continue;

// assign param values
....

try
{
cmd.ExecuteNonQuery()
}
catch (Exception ex)
{
LogMsg...
}
}

Any suggestions would be greatly appreciated.

-Joe

 

[Guest]

1. What kind of load does the server have under normal wear and what kind of
load under burst? (sounds like burst is the issue here)
2. Are you using locking hints in your stored procedure? I would guess no.

There are other questions that could be asked, as well, but #1 will help
determine the cause of slowdown, while 2 will help show cause. Watch for
deadlocks while you are running, as that info will tell you whether or not
locking hints will have a great impact.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************