Professional Web Applications Themes

stored procedure much slower then same query - Microsoft SQL / MS SQL Server

Hi, I have a select query that usesjoins, union and order by, the query uses only selects no inserts/updates nor deletes. when run as an SQL batch it produces results for about 40 secs. When I put the same query into a stored procedure and I run the procedure ( source is the query and nothing more, no transactions) it does not finish in more than 6 minutes, ( actually I interrupt the execution of the stored proc after 5-6 minutes). Locks do not seem to be a problem. Result is the same even if the query is run after ...

  1. #1

    Default stored procedure much slower then same query


    Hi,
    I have a select query that usesjoins, union and order by, the query uses
    only selects no inserts/updates nor deletes. when run as an SQL batch it
    produces results for about 40 secs. When I put the same query into a stored
    procedure and I run the procedure ( source is the query and nothing more, no
    transactions) it does not finish in more than 6 minutes, ( actually I
    interrupt the execution of the stored proc after 5-6 minutes). Locks do not
    seem to be a problem. Result is the same even if the query is run after
    server restart with no other users connected.. Removing the union clause do
    not seem to help

    Any ideas about what the reason for that might be and how to handle the
    problem would be appreciated.

    environment is SQL server 2000 standard edition no SPs ,Windows 2000 Server
    SP4 at most 2-3 users connected, users are not running tasks that require
    lots of resources.


    Test Guest

  2. #2

    Default Re: stored procedure much slower then same query

    Have you tried recompile the stored procedure?

    "Test" <com> wrote in message
    news:phx.gbl... 
    stored 
    no 
    not 
    do 
    Server 


    Adriano Guest

  3. #3

    Default Re: stored procedure much slower then same query

    The procedure is brand new.

    "Adriano Galle Dal Prá" <palm.com.br> wrote in message
    news:%23l6y$phx.gbl... [/ref]
    uses 
    > stored [/ref]
    more, 
    > not 
    > do 
    > Server [/ref]
    require 
    >
    >[/ref]


    Test Guest

  4. #4

    Default Re: stored procedure much slower then same query

    More details/findings.
    The stored proc takes 3 parameters, one of them is ignored.
    When the procedure is used with parameters, even when the parameters are set
    within the stored procedure, before the select to some hardcoded value , the
    performace difference is there.
    When the procedure is rewritten so that there are no parameters- local
    variables with hardcoded values are used instead, the stored proc runs as
    fast as the SQL batch.



    "Adriano Galle Dal Prá" <palm.com.br> wrote in message
    news:%23l6y$phx.gbl... [/ref]
    uses 
    > stored [/ref]
    more, 
    > not 
    > do 
    > Server [/ref]
    require 
    >
    >[/ref]


    Test Guest

Similar Threads

  1. Queries of query & Stored procedure
    By ?? in forum Coldfusion - Advanced Techniques
    Replies: 12
    Last Post: November 6th, 02:04 AM
  2. Replies: 3
    Last Post: December 3rd, 02:14 AM
  3. Replies: 2
    Last Post: August 6th, 07:08 PM
  4. Simple Stored Procedure query
    By Paul in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 14th, 08:25 PM
  5. Cannot execute dynamic query in stored procedure
    By Net in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 09:13 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