How to know how long does a query takes 1

[pvillar]

Hi all,

I am trying to obtain the response time for a query. So far I've only come up with something like:

$ time -p dbaccess database query.sql

Is there any other way to do this? (I am looking for something like an option in dbaccess that shows the time it took for the query. IMHO the time command measures also the time it takes to dbaccess to open and read the file,etc...)

Thank you very much in advance !!!
Peter.

 

[hiten1972]

Hi

please try this in your query
for example

set explain on ;
select * from a;
set explain off

it's create sqexplain.out file
please check that file
Regards
Hiten

 

[DCTM]

Hi,

Using set explain on does not help solving your problem.

set explain on does not give the response time for a query. However, it gives a unit of time for the cost of the query. This unit of time cannot be translated into response time. set explain on is only good if it is used for comparison. For example, the same query is used in 2 different database. Or comparison between 2 queries on the sane database. "set explain on" also good for debugging. It will show if your query using index path or sequential search.

Sad to say, I have to agree with you to use the "time" in unix to get the response time.

 

[jeager]

You Could measure the time difference between the first case an the second just like this for example

SELECT CURRENT FROM SYSTABLES;
--your query
SELECT CURRENT FROM SYSTABLES;

in both scenarios, the cost is subjected to more than just time, the statistics could be different.

 

[vpshriyan]

jeager:

Your suggestion seems to be ok, but it needs some changes as follows:

select current as start_time from systables where tabid=1
into temp x with no log;
-- query to be benchmarked for response time
select current - start_time from systables,x where tabid=1;

Regards,
Shriyan