Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    36

    Unanswered: Drop Stored procedure

    Hi,

    I've a table and a Stored procedure which selects the data on the table.
    When I drop the table and re-create the table, the performance of stored procedure become decreased. SP is taking more time.

    When I drop and re-create the Stored procedure also it's executing fast.

    Why the dropping and re-creating the stored procedure improves the performance though the Stored procedure pointing out the table name only?
    Is it necessary to drop and recreate the stored procedure also whenever the table is dropped and re-created?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    When you drop the table on which the stored procedure (SP) depends, then DB2 notices that. The next time the SP is called, DB2 must verify that all dependencies can be resolved. Due to the DROP TABLE, this is not the case, of course. So DB2 has to recompile the stored procedure on the fly. This takes some time.

    Do you see the performance degradation also when calling the SP repeatedly - without dropping anything in between?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    Thanks for the reply.
    I ran 6 times and then dropped SP and re-created.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let me phrase my question differently:
    - you drop + recreate the table
    - you call the procedure
    - you call the procedure again
    - you call the procedure yet again

    What's the performance of the procedure in the second and third call?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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