Professional Web Applications Themes

sp_executeSQL Performance in a Proc(?) - Microsoft SQL / MS SQL Server

I am unable to detect significant performance improvements when using the following in a proc...but I'm still implementing sp_executeSQL in hopes that there is some. This is a test proc. The production proc will be executed by a dts pkg and be called anywhere from 80 to 30000 times depending on the day and database it is updating. Is there any potential improvement in performance due to reduced load at the sever to execute the proc. My concern is that I'm using sp_executeSQL and changing the stmt each time, this is not what the proc was designed for. But, does ...

  1. #1

    Default sp_executeSQL Performance in a Proc(?)

    I am unable to detect significant performance improvements when using the
    following in a proc...but I'm still implementing sp_executeSQL in hopes that
    there is some.

    This is a test proc. The production proc will be executed by a dts pkg and be
    called anywhere from 80 to 30000 times depending on the day and database it is
    updating.

    Is there any potential improvement in performance due to reduced load at the
    sever to execute the proc.

    My concern is that I'm using sp_executeSQL and changing the stmt each time, this
    is not what the proc was designed for.

    But, does MSSQL optimize these executes within a proc so that subsequent calls
    to the PROC execute faster?

    Or, would the proc execute just as fast if I exec (select_str varchar(2500)?

    Please also consider that my production proc's are more complex and that on my
    test system, Win2000 executing against a db located on a Win98 system running
    MSSQL desktop edition, I still have better performance than I do at the client
    site, even with the same database restored from a recent backup.

    TIA

    JeffP....

    /*
    declare dbcnt int ,indxcnt int ,res int
    exec res = usp_execSQL dbcnt output ,indxcnt output
    print res
    print dbcnt
    print indxcnt
    */
    create proc usp_execSQL
    dbcnt int output
    ,indxcnt int output
    as
    set nocount on
    declare stmt nvarchar(2500) ,param nvarchar(500) ,res int ,int int
    set stmt = N'set dbcnt = (select count(*) from master..sysdatabases)'
    set param = N'dbcnt int output'
    exec res = sp_executeSQL stmt ,param ,dbcnt output
    if res = 0
    begin
    set int = res
    end
    else return -99
    set stmt = N'set indxcnt = (select count(*) from sysindexes)'
    set param = N'indxcnt int output'
    exec res = sp_executeSQL stmt ,param ,indxcnt output
    if res = 0
    begin
    set int = res
    end
    else return -88
    return (int)






    JDP@Work Guest

  2. #2

    Default Re: sp_executeSQL Performance in a Proc(?)


    "JDPWork" <JPgmtNoSpamadelphia.net> wrote in message
    news:%23Zv3KtKQDHA.704tk2msftngp13.phx.gbl...
    > I am unable to detect significant performance improvements when using the
    > following in a proc...but I'm still implementing sp_executeSQL in hopes
    that
    > there is some.
    >
    Are you using sp_executeSQL in hopes that it it will be faster than a normal
    stored procedure? Dynamis sql is always more expensive to run than compiled
    TSQL. Sometimes you can make up for it by getting better execution plans,
    but usually only for expensive queries.

    David


    David Browne Guest

  3. #3

    Default Re: sp_executeSQL Performance in a Proc(?)

    Because w/in the proc the sp_executeSQL is not expressly repeated....

    When I use sp_executeSQL w/in a proc, but change the stmt and parms, does MSSQL
    cache the stmts and use them the nxt time the PROC is run?

    Or is it like starting over and it must build the execute plan again?

    My proc's are not expensive alone, it's that the proc executes an xp_sproc that
    gives us errors when processing large amounts of data at the client site via
    DTS.

    Our inital assumptions was that the permissions error that appears is due to the
    dll being called so rapidly and often that the server where it lives, OS is
    hanging.

    So, I've done two things, I conditionally create a need to call the sub proc and
    try to execute my proceses with as little overhead as possible, not only for
    speed, but to reduce the workload at the server.

    TIA

    JeffP....


    "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in message
    news:#dRCQVLQDHA.2160TK2MSFTNGP11.phx.gbl...
    >
    > "JDPWork" <JPgmtNoSpamadelphia.net> wrote in message
    > news:%23Zv3KtKQDHA.704tk2msftngp13.phx.gbl...
    > > I am unable to detect significant performance improvements when using the
    > > following in a proc...but I'm still implementing sp_executeSQL in hopes
    > that
    > > there is some.
    > >
    >
    > Are you using sp_executeSQL in hopes that it it will be faster than a normal
    > stored procedure? Dynamis sql is always more expensive to run than compiled
    > TSQL. Sometimes you can make up for it by getting better execution plans,
    > but usually only for expensive queries.
    >
    > David
    >
    >

    JDP@Work Guest

  4. #4

    Default Re: sp_executeSQL Performance in a Proc(?)


    "JDPWork" <JPgmtNoSpamadelphia.net> wrote in message
    news:%23NrbRiLQDHA.1336TK2MSFTNGP11.phx.gbl...
    > Because w/in the proc the sp_executeSQL is not expressly repeated....
    >
    > When I use sp_executeSQL w/in a proc, but change the stmt and parms, does
    MSSQL
    > cache the stmts and use them the nxt time the PROC is run?
    >
    > Or is it like starting over and it must build the execute plan again?
    >
    It is like starting over. Except that SQLServer caches execution plans and
    can reuse them for identical queries. But SQL still has to p the sql
    statement and find the execution plan in its cache. With static SQL it's
    pd, compiled and optimized only once.

    David



    David Browne Guest

  5. #5

    Default Re: sp_executeSQL Performance in a Proc(?)


    "JDPWork" <JPgmtNoSpamadelphia.net> wrote in message
    news:%23Z$TKsMQDHA.3700tk2msftngp13.phx.gbl...
    > I'm still not clear...
    >
    > Is that only true to for sp_executeSQL or exec (mystr) as well?
    >
    > Or does exec (mystr) have an advantage?
    >
    Both.

    David


    David Browne Guest

  6. #6

    Default Re: sp_executeSQL Performance in a Proc(?)

    Allrighty then, I may be a little stuck as I seem to need to execute a dynamic
    strings based on conditionals.

    I have way too many procs already and I'm trying to encapsulate my business
    rules for each process in just one query or proc.

    I'm breaking out any universal procs, but still have multiple conditionals there
    too.

    Many of my processes would require additional table scans to see of data exists
    or log records exist and what kind depending on the action requested.....on and
    on....

    To avoid the scans, I try to update rows based on indexed fields, and get
    rowcount, if there is success, I'm done, if not I then move on down the
    hirearchy unitl it drops in lastly as an insert.

    I execute these rapidly and appear to be faster than doing table scans or if
    exists.

    TIA

    JeffP....

    "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in message
    news:eaGR4mNQDHA.3700tk2msftngp13.phx.gbl...
    >
    > "JDPWork" <JPgmtNoSpamadelphia.net> wrote in message
    > news:%23Z$TKsMQDHA.3700tk2msftngp13.phx.gbl...
    > > I'm still not clear...
    > >
    > > Is that only true to for sp_executeSQL or exec (mystr) as well?
    > >
    > > Or does exec (mystr) have an advantage?
    > >
    >
    > Both.
    >
    > David
    >
    >

    JDP@Work Guest

Similar Threads

  1. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  2. Proc::ProcessTable
    By Jens Puruckherr in forum PERL Modules
    Replies: 2
    Last Post: August 4th, 11:25 AM
  3. Replies: 1
    Last Post: November 5th, 01:03 PM
  4. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  5. How to 'return' from a Proc?
    By Kero van Gelder in forum Ruby
    Replies: 1
    Last Post: July 7th, 05:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139