Query execution time before execution

Question

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.

Posted 16-May-12 0:00am

revanth.crazy

Updated 16-May-12 0:04am

v3

Add a Solution

3 solutions

Solution 1

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.

Posted 16-May-12 1:36am

Sandeep Mewara

Comments

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 :)

Solution 2

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.

Posted 16-May-12 11:13am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

revanth.crazy · Accepted Answer · 2012-05-21T04:15:00

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)