Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jon Rista (jrista_at_hotmail.com)
Date: 09/14/04 

Date: Tue, 14 Sep 2004 14:54:02 -0600

I created another stored procedure that starts a transaction, executes the
other stored procedures, and commits the transaction. The processing time in
QA, when I run this sp, increased by 9 seconds, but that was all.

Is there any kind of optimization or configuration I can implement in my
application that might affect the speed of executing the sp's from ADO.NET?

Would ADO.NET cause SQL Server to execute a stored procedure that does not
return a result set to perform its operations differently than if the same
procedure was caled from QA with the same arguments?

If anyone can, I'd appreciate links to resources that might possibly hold
some answers. Thanks.

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:uTGY1SpmEHA.2684@TK2MSFTNGP12.phx.gbl...
> When a SP is executed, it uses a cached query plan. This plan is created
> the first time the SP is executed. Subsequent executions might be called
> with the same type of parameters so the plan is appropriate for the
> parameters. However, if the parameters change, the optimal query plan
> that's generated (on first call) can also change. If a subsequent
> execution would work better with a different query plan, it won't run as
> quickly as it might if given a custom query plan (as with forced
> recompile).
>
> Yes, transaction enlistment does impact performance--sometimes quite
> significantly.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
>
> "Jon Rista" <jrista@hotmail.com> wrote in message
> news:%23K7q25nmEHA.3172@TK2MSFTNGP15.phx.gbl...
>> Frans, I do start a transaction in my code, actually. And I do not start
>> any transaction through QA. Can a transaction really cause this much of a
>> performance hit? Were talking anywhere from a 100% to 240% difference in
>> execution time between QA and the application.
>>
>> I understand that transactions add overhead, but moving from
>> approximately 1 minute time to process the whole thing on QA to 60-120
>> minutes through my application sounds extreme.
>>
>> I'm using the Sql data objects, rather than the OleDb objects. Would
>> using OleDb provide any improvement? (I kind of doubt it, since the Sql
>> objects are supposed to be optimized to directly access SQL Server,
>> bypassing the additional overhead of OleDb.)
>>
>> I'm just floored by this...I've never encountered anything like it.
>>
>> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
>> news:xn0dn9j78mlt3q001@msnews.microsoft.com...
>>> Jon Rista wrote:
>>>
>>>> Hi Frans, and thanks for the reply. My application currently maintains
>>>> a
>>>> open connection throughout this process. It opens it when the process
>>>> is
>>>> triggered, and closes it when the process is complete, due to the
>>>> volume of
>>>> queries run in what is supposed to be a short time.
>>>>
>>>> When it comes to query strings....my current string is as follows:
>>>>
>>>> workstation ID=DEVELOPER1;packet size=4096;integrated
>>>> security=SSPI;data
>>>> source=ARMDEV;persist security info=False;initial catalog=CertusARM
>>>
>>> indeed a weird connection string. I don't think the workstation id is
>>> the
>>> culpit here. (although you can't add it via QA).
>>>
>>>> I'm not sure if the workstation ID will cause any problems, but the
>>>> connection is set up through a component, and workstation ID gets added
>>>> regardless (pisses me off, too).
>>>
>>> Do you get the connection string from an external component? You could
>>> try
>>> to chop it off and use a better connection string. (but again, I don't
>>> think
>>> it's it, as most processing is done on the server, and the server is
>>> slow as
>>> it seems)
>>>
>>>> I have run some profiling on the server. Not much happens when I
>>>> profile
>>>> the process as it runs from my application. You'll see the stored
>>>> procedure
>>>> start, and you'll see each statement start, but they all take an
>>>> extremely
>>>> long time. The process is just faster through QA.
>>>
>>> Ok, one question remains: do you start a transaction in your process
>>> which
>>> you don't start in the QA session?
>>>
>>> Frans.
>>>
>>>>
>>>> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in
>>>> message
>>>> news:xn0dn9amtaxbcj002@msnews.microsoft.com...
>>>> >
>>>> > Jon Rista wrote:
>>>> >> I'm using ADO.NET in a windows service application to perform a
>>>> >> process
>>>> >> on
>>>> >> SQL Server 2000. This process runs very quickly if run through Query
>>>> >> Analyser or Enterprise Manager, but takes an excessively long time
>>>> >> when
>>>> >> run
>>>> >> through my application. To be more precise, executing stored
>>>> >> procedures
>>>> >> and
>>>> >> views through Query Analyser take between 10 and 20 seconds to
>>>> >> complete.
>>>> >> The
>>>> >> same exact stored procedures and views, run in the same exact order,
>>>> >> through
>>>> >> my program, take anywhere from 30 minutes to 2 hours to complete,
>>>> >> and the
>>>> >> system that runs SQL Server (a 4-cpu Xeons system with 2gigs of
>>>> >> physical
>>>> >> ram) is pegged at 25% cpu usage (the query uses 100% of a single
>>>> >> cpu's
>>>> >> worth
>>>> >> of processing power). I am at a complete loss as to why such a vast
>>>> >> difference in execution time would occurr, but here are some
>>>> >> details.
>>>> > >
>>>> >> The windows service executes on a workstation.
>>>> >> SQL Server 2000 executes on a server different from the workstation
>>>> >> through
>>>> >> a 100mbps ethernet network.
>>>> >> Query Analyser/Enterprise Manager run on the same workstation as the
>>>> >> windows
>>>> >> service.
>>>> > (snip)
>>>> >
>>>> > In theory, a proc executed from ADO.NET should perform the same as
>>>> > when it
>>>> > is called from QA. The problem is: ADO.NET calls a proc differently
>>>> > (uses
>>>> > exec sp_executesql. Check with SqlServer profiler). This might be a
>>>> > difference.
>>>> >
>>>> > Also, QA keeps the connection open. While this might not be a
>>>> > difference
>>>> > per
>>>> > se, but it can be that your application opens/closes a connection
>>>> > each
>>>> > time.
>>>> >
>>>> > Also check if you can put extra options in the connection string when
>>>> > you
>>>> > look at the QA connection options.
>>>> >
>>>> > QA uses OleDb, however what you're doing is not provider bound, as
>>>> > all
>>>> > actions are executed on the server...
>>>> >
>>>> > Have you performed any profiling on teh server? (use the performance
>>>> > counters added by sqlserver for detailed analysis)
>>>
>>>
>>> --
>>> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
>>> My .NET Blog: http://weblogs.asp.net/fbouma
>>> Microsoft C# MVP
>>
>>
>
>

Relevant Pages

  • Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... there is an "implied" transaction that's handled ... I would hit the SQL Server list... ... However, if the parameters change, the optimal query plan ... difference in execution time between QA and the application. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Effecting run time change in behavior
    ... We have implemented a OO language called Indus that is an extension of ... separates program execution from execution environment (that enables ... the transaction management problem has come up wrt to the e) ... These tx keywords in turn invoke the Transaction manager (a separate ...
    (comp.object)
  • Re: Problems with views from Firebird
    ... > else 'Execution succeeded ... I don't understand why he used an explicit transaction for a single ... > that the system has to be rebooted after every report. ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • error 7390 when isolation level is set to serializable...
    ... I am trying to replicate the execution of a stored procedure and hence am using isolation level of serializable as recommended by BOL for purge operations. ... SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ...
    (microsoft.public.sqlserver.connect)
  • error 7390 when isolation level is set to serializable...
    ... I am trying to replicate the execution of a stored procedure and hence am using isolation level of serializable as recommended by BOL for purge operations. ... SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ...
    (microsoft.public.sqlserver.server)