Re: Pass-thru SQL performance vs Stored Proc

From: Max (maxl_at_msn.com)
Date: 01/13/05 

Date: 13 Jan 2005 07:04:14 -0800

You have your code right where it belongs.

Worse case is the stored procedures you write to do Dynamic SQL will
perform equally to building SQL dynamically in your application and
then executing it. Best case is you are able to maximize the benefits
of sp_executesql, thus every execution subsequent to the first
execution uses a cached plan that is optimal for the execution.

If you haven't, read up on sp_executesql; especially by searching this
group for advice freely given in previous posts.

With SQL 2005, this gets even more interesting because your stored
procedure could be a hook to a .NET assembly...

Relevant Pages

  • Getting values into VB from an Output parameter of a stored procedure
    ... I have a stored procedures on sql 2000 which has both input & output ... error occurs during execution of the any of tsql commands sp generates an ... Commit Tran ...
    (microsoft.public.vb.general.discussion)
  • Re: "Resultcache" effect in 10.2.0.4 ??
    ... as I said, if the exact same SQL is run with the ... execution is slow and the second is fast... ... or even if the subquery is altered in a small way (example, ... the execution plan as the same in both executions, ...
    (comp.databases.oracle.server)
  • Re: Performance problem on initial call of stored procedure
    ... It's the behaviour like SQL works. ... creates or validates access plans. ... For dynamic embedded SQL no access plan is stored in the program object ... After the first execution the data path will be closed again. ...
    (comp.sys.ibm.as400.misc)
  • Re: JDBC and Stored procedure performance problems
    ... After the SQL statement is executed. ... The second execution may be faster, but the access plan and the access ... After the second execution the data path stays open, ... Further the wait time may depend on the query engine that is used. ...
    (comp.sys.ibm.as400.misc)
  • Re: Poor performance after upgrading to sql server 2005
    ... I've included the SQL 2005 execution plan but I do not know ... <RunTimeInformation> ... <DefinedValues> ...
    (microsoft.public.sqlserver.setup)