Professional Web Applications Themes

Stored procedure takes longer time to execute - ASP Database

Hi, I have a stored procedure which is used in a ASP page. The execution of the procedure from query yser takes approximately 5 minutes. But, when it is executed thr an ASP page, it takes very long to respond and i get a "Page cannot be displayed" error. I am not able to figure out where the problem is, IIS, ASP or SQL Server. Thanks in advance Saurabh...

  1. #1

    Default Stored procedure takes longer time to execute

    Hi,

    I have a stored procedure which is used in a ASP page. The execution of the
    procedure from query yser takes approximately 5 minutes. But, when it is
    executed thr an ASP page, it takes very long to respond and i get a "Page
    cannot be displayed" error. I am not able to figure out where the problem
    is, IIS, ASP or SQL Server.

    Thanks in advance

    Saurabh
    Saurabh Guest

  2. #2

    Default Re: Stored procedure takes longer time to execute

    Saurabh wrote: 

    You are likely to be falling victim to a behavior known as "parameter
    sniffing" in SQL Server. See if this helps:
    http://tinyurl.com/f9r2

    HTH,
    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  3. #3

    Default Re: Stored procedure takes longer time to execute

    If the stored procedure already takes 5 minutes, you need to work on getting
    that time down before trying to execute it from an ASP page. Most browser
    will quit trying after 5 minutes. Can you show the stored procedure code
    and help explain why it should take 5 minutes from any tool?

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Saurabh" <microsoft.com> wrote in message
    news:com... 
    the 
    is 


    Aaron Guest

  4. #4

    Default Re: Stored procedure takes longer time to execute

    Hi bob,

    Thanks for the reply.. I tried your solution it has increased my SP
    performance, But problem with the ASP page remains the same. Please guide....

    thanks,
    Saurabh Birla

    "Bob Barrows [MVP]" wrote:
     
    >
    > You are likely to be falling victim to a behavior known as "parameter
    > sniffing" in SQL Server. See if this helps:
    > http://tinyurl.com/f9r2
    >
    > HTH,
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >[/ref]
    Saurabh Guest

  5. #5

    Default Re: Stored procedure takes longer time to execute

    You might consider looking at some of the suggestions here.
    http://www.aspfaq.com/2424#db

    Or, post the code that is taking so long to run, and we might be able to
    give you specific suggestions.

    Imagine calling a mechanic and saying, "Why is my car not running?"

    "I don't know; I can't even see your car. Shall I start guessing at
    potential reasons, or do you want to show me the car now?"

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

Similar Threads

  1. execute stored procedure
    By Buda123 in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 29th, 09:26 PM
  2. Execute permission denied on 'stored procedure' dbo
    By Prasath in forum ASP.NET Web Services
    Replies: 0
    Last Post: October 13th, 08:57 AM
  3. dsa.msc-> Sending To Home Folders Takes Longer Time!!
    By Hareth in forum Windows Server
    Replies: 1
    Last Post: July 6th, 03:00 AM
  4. Problem with execute to make a stored procedure
    By Cecil Westerhof in forum ASP Database
    Replies: 6
    Last Post: March 1st, 05:29 PM
  5. How can I execute a stored procedure in .net?
    By FireListen in forum ASP.NET General
    Replies: 1
    Last Post: June 27th, 01:39 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