Re: slowing/halting stored procedure from ado.net

Marcel,

Everybody is guessing, is showing "some" code maybe an idea, by instance the way you do the way you connect, the fill and the error handling?

Cor

"MarcelG" <MarcelG@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht news:96A97F5D-0873-414C-8E3E-684FF0A39F59@xxxxxxxxxxxxxxxx
No, there is no transaction in the context.
The stored procedure produces a list of client names who placed an order in
an order entry system.


"Cor Ligthert[MVP]" wrote:

Marcel,

There is no "SqlClient.SqlTransaction" involved?

Just a gues

Cor

"MarcelG" <MarcelG@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
news:FD583615-A6D7-4904-973E-8AB02883D462@xxxxxxxxxxxxxxxx
> William,
>
> Thanks for your reply, but at the time when calling from the > application
> (through ado.net), calling the same stored procedure from SQL > Management
> Studio goes without any slowdown, subzero performance.
> There was/is no big job running on the sql server, nothing blocking > etc.
> Would a query plan for calling from ado.net be different than from > calling
> it from SQL Management Studio?
>
> BTW:loved your book!
>
> Marcel
>
> "William Vaughn [MVP]" wrote:
>
>> Erratic performance of any query is usually due to something changing.
>> For
>> example:
>> a.. A query plan that does not match the operations being requested.
>> This is caused by the system generating a QP based on a set of a
>> parameters that might work for the first invocation, but the next
>> execution (or the problem execution) the cached plan does not match >> the
>> operations dictated by the subsequent parameters or the state of the
>> statistics (what's in the DB). Best idea? Simplify the procedure >> (break
>> it
>> into smaller pieces) whose QPs are not dependent on the vagarities of >> the
>> parameters--or don't accept parameters that break the QP.
>> b.. The query (when it slows down) might be blocked by some other
>> operation that's holding a resource that's competing or colliding with
>> the
>> resources needed by the SP.
>> c.. The system might be busy when the query is being run. For >> example,
>> it might be loaded up with another application that flushes the data
>> cache, competes for disk IO, RAM or CPU time. Examples? A print job, a
>> reporting services job, a backup, almost anything that steals
>> resources--including your own application and another connection.
>>
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
>> __________________________________________________________________________
>> __________________
>> "MarcelG" <MarcelG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:A5E650CF-C2BE-4E15-9279-0B23A5540B6B@xxxxxxxxxxxxxxxx
>> > Hi,
>> > I've strange problem with an application developed with VB.NET
>> 2008/ADO.NET
>> > framework target 2.0 and SQL Server 2005.
>> > Sometimes a certain stored procedure wich normally executes in >> > seconds,
>> > suddenly takes forever (15minutes plus).
>> > When I execute the stored procedure from within SQL Server >> > Management
>> Studio
>> > it always executes fast (42000+ records in 1 second), even when from
>> ADO.NET
>> > it takes forever.
>> > When the slowdown appears, restarting the application does not help.
>> > When I do modify/execute in man.studio, the slowdown goes away, and
>> > application does it normal quick response.
>> > Activity monitor show no blocking other queries.
>> >
>> > The stored procedure is called with a command object and an >> > dataadapter
>> > filling a dataset. When I break the application in visual studio, it
>> breaks
>> > on the da.fill(ds) line.
>> > I cannot find any pattern in when the slowdown starts, sometime once >> > a
>> > week, once a day or 3 weeks without a problem.
>> > Records added to the table are about 45 records per day (mean).
>> >
>> > Can anyone shed some light on this problem??
>> >
>>


.

Relevant Pages

  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server - Filter
    ... Happy to use a stored procedure but prefer to use a View that can be linked ... >> the sql server tables from access you are pretty much defeating the idea ... > many fewer bugs if they'd just used Access queries for the most part. ... > to query data from the back-end. ...
    (comp.databases.ms-access)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... query in my query 2 example. ... Tibor Karaszi, SQL Server MVP ... Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this ... statement in the second execution with the execution plan generated for the first execution. ...
    (microsoft.public.sqlserver.tools)
  • Re: ADP Help
    ... And the stored procedure would be something like: ... > this, if all the SQL statements are on the SQL Server, how would the SQL ... I use that query as a base ... >>> query for almost all of my reports. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL Management ... Studio goes without any slowdown, ... There was/is no big job running on the sql server, ... execution the cached plan does not match the ...
    (microsoft.public.dotnet.framework.adonet)
Loading