Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Anup Shah
Starting Member
14 Posts |
 Posted - 2006-01-24 : 15:47:37
|
| hello friends,how can we findout the query execuetion time in mili seconds.for sample select * from tabelname;how much time it will take to retrive result.thanks. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-24 : 15:55:00
|
| [code]declare @start_time datetimeset @start_time = getdate()--... run your query here ...select [Elapsed Time] = datediff(ms,@start_time,getdate())[/code]CODO ERGO SUM |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-24 : 17:49:43
|
| Also... the execution time (in seconds) is shown in the lower right of the status bar in query analyzer.Then there's the execution plan analysis. Don't forget that. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-25 : 03:28:26
|
| And select * from tabelname;has the overhead time of transmission of all the Columns and Rows to the Client application (which may also have the overhead of displaying them).You might want to mitigate that time - e.g.CREATE TABLE #TempTable( ... Column list ...)INSERT INTO #TempTableselect * from tabelnameBut the question I probably should ask is:Are you trying to1) Time the elapsed time of an action2) Optimise a piece of your SQL code'coz if its (2) I wouldn't do it by timing it in this way!Kristen |
|
|
|
Anup Shah
Starting Member
14 Posts |
Posted - 2006-01-25 : 23:07:46
|
| no kristen I was looking for second option. and i get answer from michael. Thanks for giving me second option. I would like to know answer for 1st option from u. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-26 : 00:24:17
|
| Sorry, I don't understand: Michael gave you the answer to (1)Do you need help with (2) ?Kristen |
|
|
|
Anup Shah
Starting Member
14 Posts |
Posted - 2006-01-26 : 01:40:47
|
| hy Kristenwhat i understand from michael's answer is if execute query "select * from tablename" then michael approch of to use Datedifferance function will give me the time taken by compiler to execute it and return me the result.that is "elapsed time" for that ection.let me be specific.i am working on cellphone application. when user enter user name and password it come to server and check for validation. so i want to check how much time my database server will take to execute query and to return the result. check for user name is simple query, but later i want to check for some complex query also. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-26 : 14:43:18
|
| That's fine, as you describe it. But if you want to optimise a query you need to check the number of logical file operations, and which indexes are being used etc., so you can add indexes to improve the way the query plan is chosen, and thus make it faster.That needs an approach which shows more than just the elapsed time.Kristen |
|
|
|
|
|
|
findout query execution time