How to show the execution time before query result will display

Question

How to show the execution time before query result will display

Answer 1

If you're calling the query from code, you can use a stopwatch to time the query. Start it before execution and stop it immediately after.

Answer 2

Do you mean get your program to display a "Time remaining until query completes" counter, or a progress bar, like when you delete a lot of files in the Windows Explorer?

That is not generally possible. Many queries cannot be estimated "in advance" without doing a significant amount of work, so that the estimated completion time wouldn't be available until the query was almost finished anyway.

A simple linear search through a table would be a simple case where this was possible, but adding other constraints or using indexes would cause headaches.

(Even the example from Windows of deleting a large directory is fraught with problems - they have to scan the whole directory to count the files before they start deleting them, just so they can show you the progress bar; which is why I tend to clobber large directories from the command line to save time).

Answer 3

How to show the execution time before query result will display

You can use "sys.dm_exec_requests" but it will support only a handful of operations listed below. Obviously it can't support normal DML/select queries. sys.dm_exec_requests (Transact-SQL)

ALTER INDEX REORGANIZE

AUTO_SHRINK option with ALTER DATABASE

BACKUP DATABASE

CREATE INDEX

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKTABLE

DBCC INDEXDEFRAG

DBCC SHRINKDATABASE

DBCC SHRINKFILE

KILL (Transact-SQL)

RESTORE DATABASE,

UPDATE STATISTICS.

Answer 4

Run the query asynchronously. With ADO, it's something like this. For ADO.NET, refer to this.

Then, display the timer until you get a Complete event.