Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: With Recompile in stored procedure

    Hi,

    currently i am working on performance tuning on some stored procedure and found that most of the stored procedure include with recompile on top of it.

    i try to remove it and now it improve a lot on speed tuning. However, for those stored procedure which is using dynamic sql, is it a must to include recompile in our stored procedure?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When you create a procedure WITH RECOMPILE, you are specifying that the plan can change greatly from one execution of the procedure to the next. This CAN cause drastic changes in the fundamental way that the server processes the procedure...

    If your procedure takes parameters that can cause drastically different amounts of data, it needs different query plans. The time needed to prepare a plan can be trivial compared to executing a bad plan... If the procedure doesn't need to be recompiled because it uses the same basic plan every time it executes, then the recompile is wasted and ought to be avoided.

    Without knowing exactly what your procedure does, and how much difference there can be from the simplest to the most complex plan, there really isn't a good way to guess at how much you need the RECOMPILE option. Based on your observation that the procedure runs much faster now, I'm inclined to guess that the RECOMPILE option isn't needed, but I don't have any way to prove that.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Procedures that use dynamic SQL need to be critically reviewed and a determinations needs to be made if they can be changed to a set-based solution with deterministic execution plan. Most of them (at least the ones that I've seen) would be recompiled anyway.
    "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 2004
    Posts
    57
    for example
    i have 2 table, table A and table B which have different data structure.
    in my procedure i need to update A from table B for those column have same name. And this procedure will be use in every where, so we need to use dynamic sql on it.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by GongXi
    for example
    i have 2 table, table A and table B which have different data structure.
    in my procedure i need to update A from table B for those column have same name. And this procedure will be use in every where, so we need to use dynamic sql on it.
    This doesn't sound like a good approach to me. You are using stored procedures to execute essentially arbitrary SQL passed in from the client ?!?! If that is the case, what is the point of the stored procedure, why not just directly execute the SQL statement(s)?

    -PatP

  6. #6
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by Pat Phelan
    This doesn't sound like a good approach to me. You are using stored procedures to execute essentially arbitrary SQL passed in from the client ?!?! If that is the case, what is the point of the stored procedure, why not just directly execute the SQL statement(s)?

    -PatP
    I am not pass in from client, this sp will be execute in other sp.
    As table A and table B are different for different process, so we are using dynamic sql to implement it.

    what you mean by "why not just directly execute the SQL statement(s)?"
    do you have any better idea?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    He meant directly from the front-end code, instead of passing the execution task to the stored procedure.
    "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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without understanding what your procedures do, and how they interact I can't be sure of anything, but my first guess would be that they are doing a lot of extra work. You really need to sit down and understand the whole of the procedures, what they do by themselves and what they do collectively. Once you understand that, you should be able to examine them to determine what makes sense and what does not make sense.

    -PatP

  9. #9
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by Pat Phelan
    Without understanding what your procedures do, and how they interact I can't be sure of anything, but my first guess would be that they are doing a lot of extra work. You really need to sit down and understand the whole of the procedures, what they do by themselves and what they do collectively. Once you understand that, you should be able to examine them to determine what makes sense and what does not make sense.

    -PatP
    Yes, very sorry about my misleading.
    Actually, after check on the coding, i found that we use dynamic sql because of we have something like

    where column_name not in @list,

    now i already solve it by use table variables.

    however, we still need to use dynamic sql in certain area.
    1) update table A column from table B which have same column name.
    2) update table A column which have null value to empty or zero.
    i know that i can use default value in this case, however, due to some limitation on our system, we can not implement default value.

    for above case, we are first loop from information_schema.columns to get the
    column_name and update it accordingly by using dynamic sql.

    my question here is whether we must include WITH RECOMPILE in my stored procedure once we use dynamic sql?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't see why either of those cases needs dynamic SQL. Couldn't you just use something like:
    Code:
    UPDATE tableA  -- Case #1
       SET tableA.Column1 = tableB.Column1
       FROM tableB
       WHERE  tableB.PK = tableA.PK
    
    UPDATE tableC  --  Case #2a
       SET column2 = ''
       WHERE  column2 IS NULL
    
    UPDATE tableD  --  Case #2b
       SET column3 = 0
       WHERE  column3 IS NULL
    There are a few cases where you might truly need dynamic SQL, but I don't think any of the cases that you've raised so far actually need it.

    -PatP

  11. #11
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by Pat Phelan
    I can't see why either of those cases needs dynamic SQL. Couldn't you just use something like:
    Code:
    UPDATE tableA  -- Case #1
       SET tableA.Column1 = tableB.Column1
       FROM tableB
       WHERE  tableB.PK = tableA.PK
    
    UPDATE tableC  --  Case #2a
       SET column2 = ''
       WHERE  column2 IS NULL
    
    UPDATE tableD  --  Case #2b
       SET column3 = 0
       WHERE  column3 IS NULL
    There are a few cases where you might truly need dynamic SQL, but I don't think any of the cases that you've raised so far actually need it.
    -PatP
    because the column name we get from information_schema and is assign to a variable.

    we cant execute statement like

    update @table1
    set @col1 = @col1
    from @table2
    where @table2.PK = table1.PK

    right?

Posting Permissions

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