Post reply

Updating a timestamp mid-procedure.

  • July 9, 2012 at 7:47 am

    #259168

    So I am trying to record how long my SProc takes to run.

    More precisely, how long each step of my proc runs for.

    I'll say now to be clear, I do not have access to the SQL Profiler utility; and tbh it's not the solution I want this context.

    Instead, at the beginning of my SProc, I INSERT a line into a custom log table, where a default value of GETDATE() is generated.

    At each critical step of the SProc, I then update the same row in the log table with the value GETDATE().

    e.g. 'UPDATE [Logtable] set [endStamp] = getdate() where [ID] = @ID'

    However, I have noticed that this does not appear to be accurately recording the time that the insert occurred.

    So, am I right in thinking that the GETDATE() value in the UPDATE statement, is being obtained as the time of compilation, and not of execution ?

    If that is so, what advice could anyone give as to obtaining the time at the execution of the UPDATE event ?

    My initial thoughts are to insert a dummy row into another dummy table having a field of default value GETDATE().. and use that value in my UPDATE statement.

    ... just seems a long way around !?!?

  • July 9, 2012 at 8:01 am

    #1510348

    GETDATE() returns a DATETIME value which rounds to .000 .003 or .007 of a second.

    I would look at rowversioning using datetime2 and timestamps

    http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspx

  • July 9, 2012 at 8:03 am

    #1510351

    Sim-473257 (7/9/2012)

    However, I have noticed that this does not appear to be accurately recording the time that the insert occurred.

    So, am I right in thinking that the GETDATE() value in the UPDATE statement, is being obtained as the time of compilation, and not of execution

    The GetDate value is being obtained at the point that the update executes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2012 at 3:18 pm

    #1510616

    CELKO (7/9/2012)

    SQL is a set-oriented language; everything is done on the whole set all at once and only one time per statement. You missed some fundamental concepts here and have you mindset back in files and procedural processing.

    And this has to do with recording durations of statements how?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2012 at 5:03 pm

    #1510642

    CELKO (7/9/2012)

    Please start using CURRENT_TIMESTAMP. SQL is a set-oriented language; everything is done on the whole set all at once and only one time per statement. You missed some fundamental concepts here and have you mindset back in files and procedural processing.

    That brings up a good question, Joe (and I realize the neither you nor the readers might understand the connection)... Has the new edition of "Trees and Hierarchies in SQL" hit the streets yet?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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