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

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

Date: Tue, 14 Sep 2004 09:28:31 -0600

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

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).

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.

"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)
>
> Frans.
>
> --
> 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: Printing to shared local printer via Terminal Services
    ... although the connection is insanely slow (10 mins to open ... client printers directly on the server. ... opens, and a test page, most of the time, does not make it through. ...
    (microsoft.public.windows.terminal_services)
  • Re: SQL_Server_does_not_exist_or_access_denied
    ... as this is a development server. ... I did note however that I can make a connection if there are no other active ... > The connection may have got lost because the client machine opens ... I was able to reduce the number of open TCP ports ...
    (microsoft.public.sqlserver.odbc)
  • Re: SQL_Server_does_not_exist_or_access_denied
    ... as this is a development server. ... I did note however that I can make a connection if there are no other active ... > The connection may have got lost because the client machine opens ... I was able to reduce the number of open TCP ports ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL_Server_does_not_exist_or_access_denied
    ... as this is a development server. ... I did note however that I can make a connection if there are no other active ... > The connection may have got lost because the client machine opens ... I was able to reduce the number of open TCP ports ...
    (microsoft.public.sqlserver.server)
  • Re: SQL_Server_does_not_exist_or_access_denied
    ... as this is a development server. ... I did note however that I can make a connection if there are no other active ... > The connection may have got lost because the client machine opens ... I was able to reduce the number of open TCP ports ...
    (microsoft.public.sqlserver.tools)
Loading