Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Extreme strange error when call Store Procedure

    Hi all,

    I have a problem when I call store procedure (SP).
    I create a SP which content is:
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER proc [dbo].[Get_Users_Friends]
    @Country varchar(50),
    @UserName varchar(50)
    as
    begin
                select *
                from sys_users_friends
                where Country = @Country 
                and FriendID = 
                (select UserID
                from dbo.users_detail
                where UserName = @userName
                and Country = @Country )            
    end
    I write an application (written by Java, using JDBC) to get information.
    Scenario 1:
    My query is: EXEC Get_Users_Friends 'us', 'kingnand' -> total execution time about 4 seconds
    Scenario 2:
    I change my query into:
    Code:
                select *
                from sys_users_friends
                where Country = @Country 
                and FriendID = 
                (select UserID
                from dbo.users_detail
                where UserName = @userName
                and Country = @Country )
    I get information directly, not via SP
    In this case, total execution time reduces about 10 times if compared with scenario 1

    Scenario 3:
    I call SP directly via client of MSSQL 2008 (not via Java application), with case total time is the same with scenario 2

    Could you please tell me why scenario 1 is slower than scenario 2? I can'n believe that result and can't explain them. Please help me. Thank you very much.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Possibly due to cached query plans?
    When you execute the statement, the optimizer devised a new query plan based on it's current knowledge of the table statistics. When you execute the stored procedure, the optimizer is using a query plan which was optimized for the table statistics at the time the procedure was compiled.
    Has there been a change in the number of records or the cardinality of your data? Can you try refreshing your statistics, or recompiling your store procedure?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Try to add 2 local variables to your stored procedure, @u and @c, and set passed values in @User and @Country to them. Then, use those variables in your query.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sounds plausible, but can you explain how that works?
    Adam Machanic wrote an excellent high-level article on keeping multiple cached query plans which might be relevant: Adam Machanic : Controlling Stored Procedure Caching with ... Dyanmic SQL?!?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, so I did take the time to follow along with his article (mostly I liked the "trout" part). I could have stopped at "with recompile" version, but decided to force myself to go to the end. The dyn-sql version produced the same results as "with recompile", but that's because the same think happened, - recompile, except explicit. Then I did what I had suggested to the OP earlier, - created local variables and assigned to them the values from passed parameters. The plans for both short and long ranges were the same. This means that for the short range it was less efficient, but for the long range it was perfect (considering that Machanic's conclusion was that it was). To explain this we need to look into the same area as Machanic suggests, - statistics. More specifically, - selectivity and query operators. Although selectivity is very high, the RANGE operator (between) told the optimizer, that when a wide range is selected, an ordered scan would be the most preferable. So, after introducing my version, I saw consistent plan regardless of parameter values I pass, and no recompile, and no dynamic SQL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    But to the OP's issue, he's not dealing with a range.
    How does referencing secondary variables result in a a better execution time than referencing the passed parameters?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's called "parameter sniffing", when the first execution of the proc is used as the base to compile the plan. Unless the underlying data changes drammatically (20% or 500 rows, which ever is larger), the plan will stay in procedure cache and will be used regardless of the parameters passed. That means that for some it's favorable, and for others it's not.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    But how is reassigning the parameters to variables going to result in a different execution plan?
    I mean, if this works, I'll use this little trick frequently. But I want to understand it first.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This guy explains it better than me:
    Parameter Sniffing
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Excellent, and bookmarked.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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