Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002

    Unhappy Unanswered: Execution speed mystery

    I've written a T-SQL routine that computes medians in a real estate application. When I execute the routine from Query Analyzer, it computes about 500 medians/sec for my circumstances. I want to include the routine as one step in a nightly scheduled job, but the code exceeds the size limitation for a single step in SQL Server's job scheduler. And re-formulating the routine to fit isn't feasible.

    So, I turned the routine into a stored procedure which I intended to call from the appropriate job step. To my amazement, the 500 medians/sec slowed precipitously to about 35 medians/sec!! Terrible! Then when I executed the stored procedure from Query Analyzer, it ran at 500/sec?!

    Does anyone know why the identical code run as a step from within a scheduled job would perform so much more poorly than when run from Query Analyzer?

  2. #2
    Join Date
    Oct 2002

    Re: Execution speed mystery

    Assuming precautions to be certain you were not just looking at cached vs. non-cached performance were taken, there may still be reasonable explanations (forced recompile, transient locks being waited on, etc.,), particularly if this is on a busy production server / DB. You may wish to schedule several runs within a minute or two and monitor the performance results, and / or investigate further, for example with profiler.

Posting Permissions

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