Click here to Skip to main content
14,924,395 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi ,

How can i get the accurate execution time for a query(i.e, DML operations or functions or procedures) before it is executed. For example consider that i have a parameterised procedure by name Proc_123 and it takes 5 mins to execute. My scenario is like whenever the Proc_123 is called, execution time should be displayed before it is executed.
Updated 16-May-12 0:04am

Does that even make sense? A query which would depend on lots of factors especially amount of data - how do you expect to find accurate execution time BEFORE it is executed?

I don't think you can. Go and talk to the person whoever has asked this from you.
revanth.crazy 16-May-12 9:23am
This scenario is used to display a countdown timer to the user until the process is completed ......i think there are lot of scenarios where countdown timer is used
Sandeep Mewara 16-May-12 11:32am
These scenario are not driven by defined countdown timer. You have to show a 'loading'/'processing' icon/bar as long as the process goes on.

Countdown timer are for fixed time event and as I mentioned, this particular event would not have fixed time. It will vary.
revanth.crazy 16-May-12 9:26am
Thanks for taking time to look into my issue
Wendelius 16-May-12 17:14pm
Even though the estimation is possible, I agree with you :)
Sandeep Mewara 17-May-12 1:33am
Estimation is. But OP wanted 'exact' which is not. Thanks.
Wendelius 17-May-12 4:32am
That's true :)
Before execution, the best guess that you can have is the estimated run time the optimization has calculated. So if you fetch the estimation from the plan you can use that.

However, it's not that easy if you have a stored procedure. The optimization is done on a statement level so you would have to sum all the estimations for the statements to be executed in the procedure. This can be done, but most likely you have to redesign you procedure.

Also remember that this is an estimate. The actual run time may be less or more so you cannot treat this as an exact value, so you actually cannot use a countdown timer.
Im in need for this because i need to calculate which part of the procedure or function is taking more time to execute. With this i can go directly to that part and fine tune the query. If this is not possible, let me know the best way to achieve my issue(to calculate which part of the procedure or function is taking more time to execute)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900