Professional Web Applications Themes

Problem with sp - Microsoft SQL / MS SQL Server

Hi, I execute a sp that use 40 tables and other sp. If I execute this sp in the production db I have at execution time the 40 s. If I backup and restore the db in the same server but with an other db name I have execution time the 40 s, but if I execute the same sp the next day the execution time is 6- 10s. After the execution time is 6-10s. The execution time in the production db is everytime 40s. In the night the only job is the backup the production db. Why I have ...

  1. #1

    Default Problem with sp

    Hi,
    I execute a sp that use 40 tables and other sp.
    If I execute this sp in the production db I have at
    execution time the 40 s.

    If I backup and restore the db in the same server but with
    an other db name I have execution time the 40 s, but if I
    execute the same sp the next day the execution time is 6-
    10s. After the execution time is 6-10s.
    The execution time in the production db is everytime 40s.

    In the night the only job is the backup the production db.

    Why I have this problem?
    Thank you very much.

    Arianna
    Arianna Guest

  2. #2

    Default Re: Problem with sp

    Try running sp_updatestats in the production db.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Arianna" <ariannaduoactionlabs.it> wrote in message
    news:40c401c34099$b1fe68c0$a401280aphx.gbl...
    > Hi,
    > I execute a sp that use 40 tables and other sp.
    > If I execute this sp in the production db I have at
    > execution time the 40 s.
    >
    > If I backup and restore the db in the same server but with
    > an other db name I have execution time the 40 s, but if I
    > execute the same sp the next day the execution time is 6-
    > 10s. After the execution time is 6-10s.
    > The execution time in the production db is everytime 40s.
    >
    > In the night the only job is the backup the production db.
    >
    > Why I have this problem?
    > Thank you very much.
    >
    > Arianna

    Andrew J. Kelly Guest

  3. #3

    Default Re: Problem with sp

    Better way would be to create the stored procedure as follows

    CREATE PROCEDURE PROCNAME WITH RECOMPILE

    I suggested you this option because i beleive that in your stored procedure
    you use lot of conditional statements.

    Thanks,
    Jagan Mohan
    Software Engineer
    Scape Velocity

    "Arianna" <ariannaduoactionlabs.it> wrote in message
    news:40c401c34099$b1fe68c0$a401280aphx.gbl...
    > Hi,
    > I execute a sp that use 40 tables and other sp.
    > If I execute this sp in the production db I have at
    > execution time the 40 s.
    >
    > If I backup and restore the db in the same server but with
    > an other db name I have execution time the 40 s, but if I
    > execute the same sp the next day the execution time is 6-
    > 10s. After the execution time is 6-10s.
    > The execution time in the production db is everytime 40s.
    >
    > In the night the only job is the backup the production db.
    >
    > Why I have this problem?
    > Thank you very much.
    >
    > Arianna

    Jagan Mohan Guest

Similar Threads

  1. contribute problem - access denied file may not existpermission problem
    By Al1973 in forum Macromedia Contribute Connection Administrtion
    Replies: 6
    Last Post: September 17th, 04:16 PM
  2. Replies: 2
    Last Post: September 30th, 12:13 PM
  3. Replies: 0
    Last Post: August 23rd, 11:56 AM
  4. Replies: 0
    Last Post: August 2nd, 03:03 PM
  5. Replies: 2
    Last Post: July 17th, 07:27 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