Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 09/15/04
- Next message: William \(Bill\) Vaughn: "Re: Best Practices - Connecting to Oracle from a fat client"
- Previous message: W.G. Ryan eMVP: "Re: Update from source to destination table"
- In reply to: Jon Rista: "Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)"
- Next in thread: Jon Rista: "Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)"
- Reply: Jon Rista: "Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 14 Sep 2004 17:33:39 -0700
It's not necessary to transact any statement that can be executed in
isolation. In SQL Server, there is an "implied" transaction that's handled
automatically by the server. Transactions are needed when you have two or
more changes to make that must be made together--and rolled back together if
either fails.
As far as performance, I would hit the SQL Server list... they might be able
to provide more insight.
hth
-- ____________________________________ 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:edgkr4pmEHA.3608@TK2MSFTNGP09.phx.gbl... > Eh, I recind my last statement. Adding the transaction to the QA sp causes > the processing time to increase astronomically. The first time I ran, I > did not have all the account and payment information loaded. > > Are there any resources on the net about optimizing transacted processes > in SQL Server? Tips on what kinds of queries to transact and not transact? > > "Jon Rista" <jrista@hotmail.com> wrote in message > news:%23atgq0pmEHA.3452@TK2MSFTNGP15.phx.gbl... >>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 >>>> >>>> >>> >>> >> >> > >
- Next message: William \(Bill\) Vaughn: "Re: Best Practices - Connecting to Oracle from a fat client"
- Previous message: W.G. Ryan eMVP: "Re: Update from source to destination table"
- In reply to: Jon Rista: "Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)"
- Next in thread: Jon Rista: "Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)"
- Reply: Jon Rista: "Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
