Re: Differences in execution times for a stored procedure
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 10/04/04
- Next message: ASP Yaboh: "Re: Newbie: Stored Procedures and Or Datasets ?"
- Previous message: ASP Yaboh: "Update using DataSet & Stored Proc."
- In reply to: Vincent Keller: "Differences in execution times for a stored procedure"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 4 Oct 2004 13:32:15 -0700
When a SP is executed for the first time, SQL Server compiles a query plan
and writes it to memory. This plan is based on the parameters you supply
(the first time). The next time you execute the SP, the same query plan is
used--regardless of how well the parameters "match" the existing plan. If
the SP is complex, it could be that the plan does not match the current
statistics or parameters passed in after the initial execution. Of course,
if the SQL Server needs the memory it will flush the cache and the process
is repeated (a new plan is compiled on next execution). Other factors also
cause the plan to be recompiled and you can request a recompile on each
execution--which sometimes results in better performance.
I'll be discussing this in my workshop on the 15th in Chicago.
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. __________________________________ "Vincent Keller" <siliinvestor@hotmail.com> wrote in message news:etOsKBjqEHA.1712@tk2msftngp13.phx.gbl... >I am trying to execute a time-consuming stored procedure in my application. > When I run the stored procedure through Query Analyzer, it takes some time > but returns results in about 15-20 secs. However, when I run the same > procedure by using ExecuteScalar/ExecuteNonQuery it seems to take a lot > more > time ( ~ 2-3 mins). > > I have ensured that I am not keeping the connection open for a long time > as > well as used Indexes where required but that hasn't helped a lot. > > Are there some known performance differences in executing a query (stored > procedure) directly through Query Analyzer and through ADO.NET ? > > Any recommendations for such a scenario to speed up query performance ? > >
- Next message: ASP Yaboh: "Re: Newbie: Stored Procedures and Or Datasets ?"
- Previous message: ASP Yaboh: "Update using DataSet & Stored Proc."
- In reply to: Vincent Keller: "Differences in execution times for a stored procedure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
