RE: Delay in calls to stored procedure
- From: "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 12 May 2005 09:58:08 -0700
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!
***************************
"Joe" wrote:
> 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
>
>
>
.
- References:
- Delay in calls to stored procedure
- From: Joe
- Delay in calls to stored procedure
- Prev by Date: RE: Returning CURSORS from ORACLE STORED FUNCTIONS
- Next by Date: RE: Turkish_CI_AS database trying to open a table
- Previous by thread: Delay in calls to stored procedure
- Next by thread: Fields Description
- Index(es):
Relevant Pages
|
