How can we find the timing of an particular sql query

kutty.r · New User Joined: 22 Jun 2006 Posts: 24

hi,

how can we find the timing of an particular sql query. i want to know how long an particular query executed ( how to find ?).

raghunathns · Posted: Fri Sep 08, 2006 10:39 pm

if you execute in qmf you will get the query cost before execution

kutty.r · New User Joined: 22 Jun 2006 Posts: 24

i need after executing an query how much time it takes to execute.

for ex: if i execute a query which fetches 1 row it takes xxxxx time to fetch the row . but if i execute a query which fetches 10,000 rows it surely takes more time to fetch the rows . so how to see the exact time taken by each query?

guptae · Posted: Fri Sep 08, 2006 10:50 pm

Hi,

Just give the below query before & after ur query in spufi & take out the time difference.

Select current_timestamp
from sysibm.sysdummy;

raghunathns · Posted: Sat Sep 09, 2006 12:06 am

i thought you are looking for query cost.

for your question. ekta answer is ok i believe.

srinivas_y · New User Joined: 18 Oct 2005 Posts: 1

I dont think the answer is correct.

If you could use explain statement then it would drive you in finding out correct answer.

Let me know if you need further info.

Regards,
S...

raghunathns · Posted: Wed Sep 13, 2006 9:27 pm

explain will give you
how it is accessing the data. which index is used that kind of info.

not the
time taken to extract one row
time taken to extract 1000 rows.

kgumraj · Posted: Wed Sep 13, 2006 10:10 pm

It may be helpful but it uses DB2 so defenetly the time is not true

SELECT CURRENT TIMESTAMP INTO :A-VAR FROM SYSIBM.SYSDUMMY1;

<YOUR QUERY>

SELECT CURRENT TIMESTAMP INTO :B-VAR FROM SYSIBM.SYSDUMMY1;

SUBTRACT A-VAR FROM B-VAR GIVING C-VAR

DISPLAY 'THE TIME OF EXECUTION IS' C-VAR