Professional Web Applications Themes

use execute command or sp_executesql or direct sql ? - Microsoft SQL / MS SQL Server

Hi I am writing stored procedures to archive unused data on the production database. So, I will copy it to a separate archiving database and then I will delete it from the production database. The stored procedures will reside on the production database. What I want to know: (basic idea of program) ----------------------------------------- ..... for every product to be archived begin tran insert into archivedb.dbo.table1 select * from table1 where product=product ...... delete from table1 where product=product ...... commit tran fetch next product ----------------------------------------- What is the differences in::::: a) using the 'execute' command to put in a variable archive ...

  1. #1

    Default use execute command or sp_executesql or direct sql ?

    Hi

    I am writing stored procedures to archive unused data on
    the production database.
    So, I will copy it to a separate archiving database and
    then I will delete it from the production database.

    The stored procedures will reside on the production
    database.
    What I want to know: (basic idea of program)
    -----------------------------------------
    .....
    for every product to be archived
    begin tran
    insert into archivedb.dbo.table1 select * from table1
    where product=product
    ......
    delete from table1 where product=product
    ......
    commit tran
    fetch next product
    -----------------------------------------

    What is the differences in:::::

    a) using the 'execute' command to put in a variable
    archive database path
    or
    b) using sp_executesql to do the same as (a)
    or
    c) using direct sql where the archive database path is
    hard coded

    One transaction could include +- 1000 records.
    This is for sql server 2000.

    I need good performance.

    Thanks for the help !
    Tania
    tania Guest

  2. #2

    Default Re: use execute command or sp_executesql or direct sql ?

    Why are you doing this one row at a time? You should be able to arhive all
    old data in simple SQL statements. See if the following article helps:

    http://vyaskn.tripod.com/sql_archive_data.htm

    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm


    "tania" <com> wrote in message
    news:01be01c360e8$0e2c9db0$gbl...
    Hi

    I am writing stored procedures to archive unused data on
    the production database.
    So, I will copy it to a separate archiving database and
    then I will delete it from the production database.

    The stored procedures will reside on the production
    database.
    What I want to know: (basic idea of program)
    -----------------------------------------
    .....
    for every product to be archived
    begin tran
    insert into archivedb.dbo.table1 select * from table1
    where product=product
    ......
    delete from table1 where product=product
    ......
    commit tran
    fetch next product
    -----------------------------------------

    What is the differences in:::::

    a) using the 'execute' command to put in a variable
    archive database path
    or
    b) using sp_executesql to do the same as (a)
    or
    c) using direct sql where the archive database path is
    hard coded

    One transaction could include +- 1000 records.
    This is for sql server 2000.

    I need good performance.

    Thanks for the help !
    Tania


    Narayana Guest

  3. #3

    Default Re: use execute command or sp_executesql or direct sql ?

    tania (com) writes: 

    Hard-coding database names is rarely a good idea, if you need to set up
    a second environment on the same server. Thus, it's better to get the
    DB name from some configuration table.

    This can be achieved in several ways. One is using dynamic SQL as you
    outlined. You can also say:

    INSERT tbl EXEC sp_in_other_db productid

    Rather than build the SQL statement dynamically, you only build the
    procedure name dynamically. The advantage here is that you need to
    grant any permissions to tables, in this would be an issue for you.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. #4

    Default Re: use execute command or sp_executesql or direct sql ?

    sp_executesql use would mean that SQL would share the query plan ( which may
    or may not be good)... depending on how many records that there are for each
    product..

    exec ('string') tells SQL to optimize each statement in its own batch

    using a straight insert lets SQL decide when and when not to share plans..

    The best thing you can do is to test with sample, representative data.
    --
    Wayne Snyder, MCDBA, SQL Server MVP
    Computer Education Services Corporation (CESC), Charlotte, NC
    www.computeredservices.com
    (Please respond only to the newsgroups.)

    I support the Professional Association of SQL Server (PASS) and its
    community of SQL Server professionals.
    www.sqlpass.org


    "tania" <com> wrote in message
    news:01be01c360e8$0e2c9db0$gbl... 


    Wayne Guest

Similar Threads

  1. Submit command to execute
    By stuberry in forum Macromedia Director Basics
    Replies: 0
    Last Post: April 15th, 11:06 AM
  2. RUN/execute a Command-Line command from an ASP page
    By Lucas Cowald in forum ASP.NET Building Controls
    Replies: 1
    Last Post: October 22nd, 04:26 PM
  3. RUN/execute a Command-Line command from an ASP page.
    By Lucas Cowald in forum ASP Components
    Replies: 7
    Last Post: October 22nd, 01:08 PM
  4. how to execute command line in ASP
    By david Wilton in forum ASP
    Replies: 2
    Last Post: August 11th, 08:16 PM

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