Post reply

How To Calculate Database Transaction Execution time

  • June 30, 2009 at 6:26 am

    #133891

    Dear Fren's Here is the Scenario.

    I m working with Visual Studio 2005, when I perform a Database Transaction I want to show the Remaining Time to Show with the Progress Bar to complete the Database transaction .

    that will show the message that please wait and the Approximate remaining time to complete the transaction so that the user will get the idea that how much it will take to process.

    How it could be done ...please help

    Thank you so much

  • June 30, 2009 at 7:05 am

    #1017533

    not enough information to help you; you can see how long a command took with a trace or by setting SET STATISTICS TIME ON , but not an estimated time, as far as I know.

    a well written set based transaction, whether affecting one row or a million rows, would be so quick there's no need to try and track progress; I'm guessing your transactions are more RBAR in nature;

    Are you using typed datasets to handle your schema, so that the DataSet.UpdateDataTable is basically the only items in your transactions? there is a big advantage to that, because behind the scenes, the code is creating all the statements needed for the inserts/updates/deletes that occur to your .NET datatables; it's a sweet feature.

    what, specifically, is your transaction doing, that it is slow enough you want a progress bar?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 30, 2009 at 8:07 am

    #1017590

    There really is no way to accurately assess how long a query will take to run. You can have a single row update that should run in about 5ms that gets blocked by a 60000 row insert, and it's in a queue behind another 700000 row update, both of which are being delayed because of another read process on a different set of tables that is using up CPU causing time slicing with the other processes... You see? No way to know when or if it's being blocked and if it's being blocked to, on the fly, trace through the various blocking & queuing processes and determine what they're doing and how long they're running...

    But, there is a DMV, sys.dm_exec_requests, that shows an estimated percent complete for some types of queries. Read up on it in the Books Online. It might do what you need, depending on what you're doing.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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