Post reply

SP execution time

  • September 12, 2003 at 4:46 am

    #59772

    I have rewritten a stored procedure to increase it's speed. I am trying to find a way to measure the execution time of the old versus the new procedure - ie the time from when it's called until it starts returning the results. Using query analyser I cannot seem to find this value. Suggestions?

    TIA, John

  • September 12, 2003 at 5:12 am

    #473883

    Hi.

    Using QA Use CTRL + Shift + T will show you the times.

    Something else I do is assign DateTime to a variable at the top of the proc and at the bottom, use DateDiff and subtract the first value with GetDate and see how many ms it takes. Date diff can tell you days and years but.....

    Cheers,

    Crispin

    Edited by - crappy on 09/12/2003 05:14:54 AM

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • September 12, 2003 at 5:21 am

    #473885

    John,

    What I usually do in these situations is use Profiler, filtering on the hostname that I'm testing from.

    Then I run the DBCC DROPCLEANBUFFERS, and then the old script/SP 3 times, then DBCC DROPCLEANBUFFERS, and then the new script/SP 3 times, and repeat this whole thing 2 or 3 times.

    I look at not just duration, but CPU, reads and writes.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • September 12, 2003 at 5:49 am

    #473891

    Crappy: Thanks for the datetime suggestion. I think there's a saying... sometimes, one can't see the forest....

    Mark: this makes sense, I especially like the 'averaging' aspect of it...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply