Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Adelaide, South Australia
    Posts
    2

    Unanswered: Slow performing stored procedure

    Hello People,

    I had a strange occurence that I have never noticed before in my many years of database programming and wondered whether anyone else has had this problem:

    I have a delete command that deletes records from table a inner joined to table b.

    when I run this command as native SQL it takes about 10 seconds.

    I moved this command to a stored procedure and all was well until a few days later when the stored procedure decided to slow down so much, I had to manually kill the process.

    To cut a long story short, I re-compiled the stored procedure and all is well.

    Any ideas on why or how this is happening and can we all trust that our stored procedures really are performing to the best of their ability?

    BTW, we are using Sybase 12.0 using Informatica to call the stored procedure.

    Any insights will be greatfully received.

    Cheers
    Jon

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Bad execution plan on the cache ?
    bad execution plan regarding the input parameters ?

    Difficult without comparing 2 showplan. Did zou execute a sp_showplan before dropping the SP ?

  3. #3
    Join Date
    Dec 2003
    Location
    Adelaide, South Australia
    Posts
    2
    Originally posted by fadace
    Bad execution plan on the cache ?
    bad execution plan regarding the input parameters ?

    Difficult without comparing 2 showplan. Did zou execute a sp_showplan before dropping the SP ?
    The only execution plan I had was form the actual delete statement that the stored procedure was running as opposed to the compiled stored procedure.

    We had a thought on Friday that it may have something to do with the statistics on the table when the stored procedure was first created. There may have been a very small amount of data but had subsequently been loaded with a significantly larger amount.

    Unfortunately (or fortunately in this case), it hasn't happened since, but if it does, I'll certainly check the stored procedure execution plan and post it in.

    Cheers
    Jon

Posting Permissions

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