Find Querey Execution Time - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find Querey Execution Time

Author

Topic

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-22 : 00:57:25

Hi!
How can i find the querey execution time in sql 2000.
If u have any article or books online please suggest me.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 02:14:55

run your query in Query Analyzer and check the execution time at the bottom right of QA

KH
[spoiler]Time is always against us[/spoiler]

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-22 : 02:50:48

hi!

I execute the following cursor:

declare c cursor
for
select distinct count(id) as cnt,id from ab1 group by id
open c
declare @c int
declare @id int
fetch next from c into @c,@id
while @@fetch_status=0
begin
update ab1 set name=@c where id=@id
fetch next from c into @c,@id
end
close c
deallocate c

I can check it but it will display 0:00:00

please tell me what can i do?

Thanks for your reply!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-22 : 02:56:48

Add following command before running the code:

SET STATISTICS TIME ON

-- your code here

SET STATISTICS TIME OFF

After running the query, check the messages tab.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 02:59:59

that means your query runs within one second. What is the purpose of this ? Performance tuning ? You can also use profiler or show execution plan to see the details.

A side question, why are you using cursor ?

KH
[spoiler]Time is always against us[/spoiler]

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-23 : 00:33:51

HI!
Thanks for all your replies!

In my cursor ,I used set statistics time on .but it displayed the output like this

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(4 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 23 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(2 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

what is elapsed time? does it high or low while executing a sql statement?

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-23 : 00:50:51

Hi!

I have a table named ab1

id and name are int datatype

id name
---------
1 null

1 null

1 null

1 null

2 null

2 null

3 null

i want the output like

id name
---------
1 4
2 2
3 1

for this purpose i used cursor and i have an alternate solution to
update the table with count

update ab1 set name=(select count(*) from ab1 where id=a.id) from ab1 a

my question
-----------

can i use cursor or update statement? which is better?
so only i asked execution time

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-23 : 01:36:03

In this case you can use update and no need for cursor. Also you can have them in select statement. Why do you want to update them in another table?

Select id, count(coalesce(name,'')) as counting from ab1
group by id

Madhivanan

Failing to plan is Planning to fail

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-23 : 03:42:42

Hi!

sorry i want the output like

id name
--------
1 4
1 4
1 4
1 4
2 2
2 2
3 1

i want to update the same table not another table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-23 : 04:21:48

[code]update t1
set name=t2.name
from ab1 t1 inner join
(
select id,count(*) as name from ab1
group by id
) t2
on t1.id=t2.id[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources