none
Stored procedure - slow when executed using ExecuteReader RRS feed

  • Question

  • Hi,
    I'm facing a weird problem and need some assistance to nail it down. I have a stored proc which accepts a table valued parameter and do some joins. When executed in with in the SQL management studio it returns results with in average 3 seconds. But when I request using a command.ExecuteReader it takes significantly long. (like 40 secs)
    What can I do to trace down the error ?
    Thanks in advance.
    V

    Wednesday, March 2, 2011 7:04 PM

Answers

  • Hello vije,

     

    Welcome to the MSDN Forum and thanks for posting here.

    According to your description, I think this question is a very good one! A discussion about this topic between Ivan and Jared in the MSDN Forum can provide much useful infomation to solve it.

    Please read this thread.

    And more, Ivan has done tons of tests about this topic, and found a way improving the performance of Stored Proc. Please read his blog.

    What he did is to make a local copy of the parameters and to use them in the procedure’s body.

    See the code as below:

    Create procedure LoadData(@Param1_ int @param2_ int)

    declare

                    @Param1 int,

                    @param2 int,

     

    Set @Param1 = @Param1_

    Set @param2 = @Param2_

     

     

    Select Column1, Column2, ….

    From Table1

                    Join …

    Where Column1 = @Param1 and …

    The stored procedure was executed for less than 1ms!!!

    I hope this can help you.

     

    Have a nice day,

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 3, 2011 9:38 AM
    Moderator

All replies

  • Hello vije,

     

    Welcome to the MSDN Forum and thanks for posting here.

    According to your description, I think this question is a very good one! A discussion about this topic between Ivan and Jared in the MSDN Forum can provide much useful infomation to solve it.

    Please read this thread.

    And more, Ivan has done tons of tests about this topic, and found a way improving the performance of Stored Proc. Please read his blog.

    What he did is to make a local copy of the parameters and to use them in the procedure’s body.

    See the code as below:

    Create procedure LoadData(@Param1_ int @param2_ int)

    declare

                    @Param1 int,

                    @param2 int,

     

    Set @Param1 = @Param1_

    Set @param2 = @Param2_

     

     

    Select Column1, Column2, ….

    From Table1

                    Join …

    Where Column1 = @Param1 and …

    The stored procedure was executed for less than 1ms!!!

    I hope this can help you.

     

    Have a nice day,

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 3, 2011 9:38 AM
    Moderator
  • It certainly worked for me!

    Thanks

    Wednesday, September 9, 2015 4:23 PM