Cor Ligthert [MVP]
4/17/2008 3:35:00 AM
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@discussions.microsoft.com> schreef in bericht
news:96A97F5D-0873-414C-8E3E-684FF0A39F59@microsoft.com...
> 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@discussions.microsoft.com> schreef in bericht
>> news:FD583615-A6D7-4904-973E-8AB02883D462@microsoft.com...
>> > 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@discussions.microsoft.com> wrote in message
>> >> news:A5E650CF-C2BE-4E15-9279-0B23A5540B6B@microsoft.com...
>> >> > 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??
>> >> >
>> >>
>>