Fwd: Query execution time
Hello dear MontDB users,
I have a question please, I just need to get the query execution time without showing the results of the query (for performance comparaison) . Something which corresponds to the "traceonly" in Oracle. that will be greate if I can see the query plan but for the moment the most important for me is to get the query execution time.
for example: I want to know the query exection time of this query (select * from source where a>10) knowing that I have more than one hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time without showing the results of the query (for performance comparaison) . Something which corresponds to the "traceonly" in Oracle. that will be greate if I can see the query plan but for the moment the most important for me is to get the query execution time.
for example: I want to know the query exection time of this query (select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome. Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thank you for your answer. But in general I would like to know how to get the query exexution time without printing the results. It's for performance comparaison. I want to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings, Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" Martin.Kersten@cwi.nl a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time without showing the results of the query (for performance comparaison) . Something which corresponds to the "traceonly" in Oracle. that will be greate if I can see the query plan but for the moment the most important for me is to get the query execution time.
for example: I want to know the query exection time of this query (select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome. Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer. But in general I would like to know how to get the query exexution time without printing the results. It's for performance comparaison. I want to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings, Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" <Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time without showing the results of the query (for performance comparaison) . Something which corresponds to the "traceonly" in Oracle. that will be greate if I can see the query plan but for the moment the most important for me is to get the query execution time.
for example: I want to know the query exection time of this query (select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome. Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" Martin.Kersten@cwi.nl a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote: Thank you for your answer. But in general I would like to know how to get the query exexution time without printing the results. It's for performance comparaison. I want to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings, Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" <Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time without showing the results of the query (for performance comparaison) . Something which corresponds to the "traceonly" in Oracle. that will be greate if I can see the query plan but for the moment the most important for me is to get the query execution time.
for example: I want to know the query exection time of this query (select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome. Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
The first time you run a query all your data may reside on disk, i.e. a cold database. The second time you run the same query you benefit from caching effects, i.e. a hot database
On 10/13/13 12:31 PM, baraa Mohamad wrote:
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" <Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl> a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer. But in general I would like to know how to get the query exexution time without printing the results. It's for performance comparaison. I want to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings, Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" <Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
> Hello dear MontDB users, > > I have a question please, I just need to get the query execution time > without showing the results of the query (for performance > comparaison) . > Something which corresponds to the "traceonly" in Oracle. that will be > greate if I can see the query plan but for the moment the most > important > for me is to get the query execution time. > > for example: I want to know the query exection time of this query > (select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome. Better use SELECT COUNT(*)....
> hundred million records which correspond to this predicate. > > Thank in advace for your appreciated help. > > Baraa
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
perhaps this might help
http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
On 10/13/13 12:31 PM, baraa Mohamad wrote:
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" <Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl> a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer. But in general I would like to know how to get the query exexution time without printing the results. It's for performance comparaison. I want to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings, Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" <Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl mailto:Martin.Kersten@cwi.nl> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
> Hello dear MontDB users, > > I have a question please, I just need to get the query execution > time > without showing the results of the query (for performance > comparaison) . > Something which corresponds to the "traceonly" in Oracle. that > will be > greate if I can see the query plan but for the moment the most > important > for me is to get the query execution time. > > for example: I want to know the query exection time of this query > (select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome. Better use SELECT COUNT(*)....
> hundred million records which correspond to this predicate. > > Thank in advace for your appreciated help. > > Baraa
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thanks for the link and the explication. In fact I'm running the query normally first (without the time option) and I get about 600ms at the end of the execution. Then when I use time mclient -d db query.sql/dev/null that gives me real 1m3.895suser 0m15.640ssys 0m2.160s So it's not really the issu of cold and hot data because for the first normal execution I got a time much more smaller than the second one using Time. I really appriciate your help because I need to get the exact real execution time. One more question please what is the meaning of real, user and sys or where I can find the difinition of these parameters. Thanks again Date: Sun, 13 Oct 2013 14:36:16 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
perhaps this might help
http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
On 10/13/13 12:31 PM, baraa Mohamad wrote:
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" Martin.Kersten@cwi.nl a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer.
But in general I would like to know how to get the query exexution time
without printing the results. It's for performance comparaison. I want
to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings,
Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" <Martin.Kersten@cwi.nl
mailto:Martin.Kersten@cwi.nl> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time
without showing the results of the query (for performance
comparaison) .
Something which corresponds to the "traceonly" in Oracle. that will be
greate if I can see the query plan but for the moment the most
important
for me is to get the query execution time.
for example: I want to know the query exection time of this query
(select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome.
Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
_______________________________________________
users-list mailing list
users-list@monetdb.org mailto:users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org mailto:users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thanks for the link and the explication. In fact I'm running the query normally first (without the time option) and I get about 600ms at the end of the execution. Then when I use time mclient -d db query.sql/dev/null that gives me real 1m3.895suser 0m15.640ssys 0m2.160s So it's not really the issu of cold and hot data because for the first normal execution I got a time much more smaller than the second one using Time. I really appriciate your help because I need to get the exact real execution time. One more question please what is the meaning of real, user and sys or where I can find the difinition of these parameters. Thanks again Date: Sun, 13 Oct 2013 14:36:16 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
perhaps this might help
http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
On 10/13/13 12:31 PM, baraa Mohamad wrote:
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" Martin.Kersten@cwi.nl a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer.
But in general I would like to know how to get the query exexution time
without printing the results. It's for performance comparaison. I want
to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings,
Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" <Martin.Kersten@cwi.nl
mailto:Martin.Kersten@cwi.nl> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time
without showing the results of the query (for performance
comparaison) .
Something which corresponds to the "traceonly" in Oracle. that will be
greate if I can see the query plan but for the moment the most
important
for me is to get the query execution time.
for example: I want to know the query exection time of this query
(select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome.
Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
_______________________________________________
users-list mailing list
users-list@monetdb.org mailto:users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org mailto:users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Dear all,
please be aware that "time is not time".
It largely depends on what you want to measure and what you can measure.
Having said that, to understand why different way of measuring time result in different results, it's mandatory to understand what these different ways do actually measure (and why).
1) Shell built-in command "time" and unix tool "/bin/time" / "/usr/bin/time" --- beware that they are not the same! --- mainly measure and report the wall-clock time spent by the given command/process. See their respective man pages for details.
2) The time reported by mclient when using the "-i"/"--interactive" option is the time span between sending the query to the server and receiving the first part / block of the result.
Thus, (1) includes everything from loading the mclient binary and starting the mclient process, parsing the query in mclient, sending to the server, having the server execute the query and serialize the result, sending the result back to the client, to the client receiving, parsing and rendering the result, and sending the result to /dev/null ("for free"), to a file (I/O), or to a terminal (scrolling).
(2) merely includes the time the server spends on receiving and executing the query and creating the result. The abovementioned costs on the client side to receive, parse, render, etc. the result are excluded.
Given this, and assuming you have s single query in Q.sql, please consider running time -p mclient -i Q.sql | tail -n1 and compare the results opbtained.
Best, Stefan
ps: For more general information on running experiments and measuring time, please also consider studying these tutorial slides: https://repository.cwi.nl/noauth/search/searchrepository.php?isneditor=all&a...
----- Original Message -----
Thanks for the link and the explication. In fact I'm running the query normally first (without the time option) and I get about 600ms at the end of the execution.
Then when I use time mclient -d db query.sql/dev/null
that gives me real 1m3.895s user 0m15.640s sys 0m2.160s
So it's not really the issu of cold and hot data because for the first normal execution I got a time much more smaller than the second one using Time.
I really appriciate your help because I need to get the exact real execution time.
One more question please what is the meaning of real, user and sys or where I can find the difinition of these parameters.
Thanks again
Date: Sun, 13 Oct 2013 14:36:16 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
perhaps this might help
http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
On 10/13/13 12:31 PM, baraa Mohamad wrote:
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" < Martin.Kersten@cwi.nl > a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer.
But in general I would like to know how to get the query exexution time
without printing the results. It's for performance comparaison. I want
to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings,
Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" < Martin.Kersten@cwi.nl
< mailto:Martin.Kersten@cwi.nl >> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time
without showing the results of the query (for performance
comparaison) .
Something which corresponds to the "traceonly" in Oracle. that will be
greate if I can see the query plan but for the moment the most
important
for me is to get the query execution time.
for example: I want to know the query exection time of this query
(select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome.
Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
users-list mailing list
users-list@monetdb.org < mailto:users-list@monetdb.org >
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list
users-list@monetdb.org < mailto:users-list@monetdb.org >
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
GREAT EXPLICATION! Thanks for this helpful and clear explication. I think it's very benificial to add it somewhere on the documentation of MonetDB (if it's not already the case); I spent three days googling and trying to understand how is that works. I have one more question please, what if I have more that one query in my Q.sql? can I get the time (2: reported by mclient -i option) for each of the queries or I can only get the total time?
Best regards, Baraa
Date: Sun, 13 Oct 2013 16:03:26 +0200 From: Stefan.Manegold@cwi.nl To: users-list@monetdb.org Subject: Re: Query execution time
Dear all,
please be aware that "time is not time".
It largely depends on what you want to measure and what you can measure.
Having said that, to understand why different way of measuring time result in different results, it's mandatory to understand what these different ways do actually measure (and why).
Shell built-in command "time" and unix tool "/bin/time" / "/usr/bin/time" --- beware that they are not the same! --- mainly measure and report the wall-clock time spent by the given command/process. See their respective man pages for details.
The time reported by mclient when using the "-i"/"--interactive" option is the time span between sending the query to the server and receiving the first part / block of the result.
Thus, (1) includes everything from loading the mclient binary and starting the mclient process, parsing the query in mclient, sending to the server, having the server execute the query and serialize the result, sending the result back to the client, to the client receiving, parsing and rendering the result, and sending the result to /dev/null ("for free"), to a file (I/O), or to a terminal (scrolling).
(2) merely includes the time the server spends on receiving and executing the query and creating the result. The abovementioned costs on the client side to receive, parse, render, etc. the result are excluded.
Given this, and assuming you have s single query in Q.sql, please consider running time -p mclient -i Q.sql | tail -n1 and compare the results opbtained.
Best, Stefan
ps: For more general information on running experiments and measuring time, please also consider studying these tutorial slides: https://repository.cwi.nl/noauth/search/searchrepository.php?isneditor=all&a...
----- Original Message -----
Thanks for the link and the explication. In fact I'm running the query normally first (without the time option) and I get about 600ms at the end of the execution.
Then when I use time mclient -d db query.sql/dev/null
that gives me real 1m3.895s user 0m15.640s sys 0m2.160s
So it's not really the issu of cold and hot data because for the first normal execution I got a time much more smaller than the second one using Time.
I really appriciate your help because I need to get the exact real execution time.
One more question please what is the meaning of real, user and sys or where I can find the difinition of these parameters.
Thanks again
Date: Sun, 13 Oct 2013 14:36:16 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
perhaps this might help
http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
On 10/13/13 12:31 PM, baraa Mohamad wrote:
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" < Martin.Kersten@cwi.nl > a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer.
But in general I would like to know how to get the query exexution time
without printing the results. It's for performance comparaison. I want
to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings,
Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" < Martin.Kersten@cwi.nl
< mailto:Martin.Kersten@cwi.nl >> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time
without showing the results of the query (for performance
comparaison) .
Something which corresponds to the "traceonly" in Oracle. that will be
greate if I can see the query plan but for the moment the most
important
for me is to get the query execution time.
for example: I want to know the query exection time of this query
(select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome.
Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
users-list mailing list
users-list@monetdb.org < mailto:users-list@monetdb.org >
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list
users-list@monetdb.org < mailto:users-list@monetdb.org >
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
GREAT EXPLICATION! Thanks for this helpful and clear explication. I think it's very benificial to add it somewhere on the documentation of MonetDB (if it's not already the case); I spent three days googling and trying to understand how is that works.
The explanation of "time" is on its man page. We'll consider adding the explanation of mclient's "-i" option to its man page.
I have one more question please, what if I have more that one query in my Q.sql? can I get the time (2: reported by mclient -i option) for each of the queries or I can only get the total time?
unless you have more than one query per line, you'll get individual times.
Stefan
Best regards,
Baraa
Date: Sun, 13 Oct 2013 16:03:26 +0200 From: Stefan.Manegold@cwi.nl To: users-list@monetdb.org Subject: Re: Query execution time
Dear all,
please be aware that "time is not time".
It largely depends on what you want to measure and what you can measure.
Having said that, to understand why different way of measuring time result in different results, it's mandatory to understand what these different ways do actually measure (and why).
Shell built-in command "time" and unix tool "/bin/time" / "/usr/bin/time" --- beware that they are not the same! --- mainly measure and report the wall-clock time spent by the given command/process. See their respective man pages for details.
The time reported by mclient when using the "-i"/"--interactive" option is the time span between sending the query to the server and receiving the first part / block of the result.
Thus, (1) includes everything from loading the mclient binary and starting the mclient process, parsing the query in mclient, sending to the server, having the server execute the query and serialize the result, sending the result back to the client, to the client receiving, parsing and rendering the result, and sending the result to /dev/null ("for free"), to a file (I/O), or to a terminal (scrolling).
(2) merely includes the time the server spends on receiving and executing the query and creating the result. The abovementioned costs on the client side to receive, parse, render, etc. the result are excluded.
Given this, and assuming you have s single query in Q.sql, please consider running time -p mclient -i Q.sql | tail -n1 and compare the results opbtained.
Best, Stefan
ps: For more general information on running experiments and measuring time, please also consider studying these tutorial slides: https://repository.cwi.nl/noauth/search/searchrepository.php?isneditor=all&a...
----- Original Message -----
Thanks for the link and the explication. In fact I'm running the query normally first (without the time option) and I get about 600ms at the end of the execution.
Then when I use time mclient -d db query.sql/dev/null
that gives me real 1m3.895s user 0m15.640s sys 0m2.160s
So it's not really the issu of cold and hot data because for the first normal execution I got a time much more smaller than the second one using Time.
I really appriciate your help because I need to get the exact real execution time.
One more question please what is the meaning of real, user and sys or where I can find the difinition of these parameters.
Thanks again
Date: Sun, 13 Oct 2013 14:36:16 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
perhaps this might help
http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
On 10/13/13 12:31 PM, baraa Mohamad wrote:
That's great! Thank you very much for your appreciated help.
I just could't really understand the output of this command.
This is my output: Real: 1m23 User: 0m24s Sys: 0m2.3s
I'm a little bit confused because when I execute the same query directly without the time option i get 733,28ms.
So i couldn't know what is the real execution time.
Le 13 oct. 2013 à 11:29, "Martin Kersten" < Martin.Kersten@cwi.nl > a écrit :
How about (time mclient -d db <inputfile >/dev/null)
On 10/13/13 11:24 AM, baraa Mohamad wrote:
Thank you for your answer.
But in general I would like to know how to get the query exexution time
without printing the results. It's for performance comparaison. I want
to evaluate MonetDB for my research and i'm working with large tables.
Please any idea??
Greetings,
Baraa
Le 13 oct. 2013 à 11:02, "Martin Kersten" < Martin.Kersten@cwi.nl
< mailto:Martin.Kersten@cwi.nl >> a écrit :
Hi
On 10/13/13 10:55 AM, baraa Mohamad wrote:
Hello dear MontDB users,
I have a question please, I just need to get the query execution time
without showing the results of the query (for performance
comparaison) .
Something which corresponds to the "traceonly" in Oracle. that will be
greate if I can see the query plan but for the moment the most
important
for me is to get the query execution time.
for example: I want to know the query exection time of this query
(select * from source where a>10) knowing that I have more than one
This is not the ind of query you fire when you expect a large outcome.
Better use SELECT COUNT(*)....
hundred million records which correspond to this predicate.
Thank in advace for your appreciated help.
Baraa
users-list mailing list
users-list@monetdb.org < mailto:users-list@monetdb.org >
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list
users-list@monetdb.org < mailto:users-list@monetdb.org >
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On 10/13/13 4:41 PM, Stefan Manegold wrote:
GREAT EXPLICATION! Thanks for this helpful and clear explication. I think it's very benificial to add it somewhere on the documentation of MonetDB (if it's not already the case); I spent three days googling and trying to understand how is that works.
See: http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming Clear enough?
The explanation of "time" is on its man page. We'll consider adding the explanation of mclient's "-i" option to its man page.
I have one more question please, what if I have more that one query in my Q.sql? can I get the time (2: reported by mclient -i option) for each of the queries or I can only get the total time?
unless you have more than one query per line, you'll get individual times.
Stefan
Great! I find MonetDB really interesting and I get impressive performance results. Maybe the only thing that make it difficult for new users is the absence of pratical examples, the getting start toturial (http://www.monetdb.org/Documentation/UserGuide/Tutorial) is great because it has practical and simple examples, but "in my humble opinion" it's not the case for the rest of the documentation :) Thank you again (Stefan and Martin) for your appreciated help. Best regards,Baraa P.S:I'm working on some research (maybe using MonetDB to show expermintal results of my contribution) and I'll try to publish a paper in the few coming months. If you are interested i'll send it to you as soon as I finish it.
Date: Sun, 13 Oct 2013 16:50:08 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
On 10/13/13 4:41 PM, Stefan Manegold wrote:
GREAT EXPLICATION! Thanks for this helpful and clear explication. I think it's very benificial to add it somewhere on the documentation of MonetDB (if it's not already the case); I spent three days googling and trying to understand how is that works.
See: http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming Clear enough?
The explanation of "time" is on its man page. We'll consider adding the explanation of mclient's "-i" option to its man page.
I have one more question please, what if I have more that one query in my Q.sql? can I get the time (2: reported by mclient -i option) for each of the queries or I can only get the total time?
unless you have more than one query per line, you'll get individual times.
Stefan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
I tried to execute it with a file (Q.sql) containing more than one query, it seems that it gives me the total execution time with the number of tuple for the last query in the file. Is there something else to do in order to get the execution time of each of the queries. mclient -i -u monetdb -d voc query.sql | tail -n1 Best regards,Baraa
Date: Sun, 13 Oct 2013 16:50:08 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
On 10/13/13 4:41 PM, Stefan Manegold wrote:
GREAT EXPLICATION! Thanks for this helpful and clear explication. I think it's very benificial to add it somewhere on the documentation of MonetDB (if it's not already the case); I spent three days googling and trying to understand how is that works.
See: http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming Clear enough?
The explanation of "time" is on its man page. We'll consider adding the explanation of mclient's "-i" option to its man page.
I have one more question please, what if I have more that one query in my Q.sql? can I get the time (2: reported by mclient -i option) for each of the queries or I can only get the total time?
unless you have more than one query per line, you'll get individual times.
Stefan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
http://lmgtfy.com/?q=man+mclient
$ echo 'select 1;' > /tmp/Q.sql $ echo 'select 2;' >> /tmp/Q.sql $ cat /tmp/Q.sql select 1; select 2; $ mclient -i /tmp/Q.sql | tail -n1 1 tuple (0.078ms) $ mclient -i /tmp/Q.sql | grep -E '^[0-9]+ tuples? (.*)$' 1 tuple (0.742ms) 1 tuple (0.682ms)
Best, Stefan
----- Original Message -----
I tried to execute it with a file (Q.sql) containing more than one query, it seems that it gives me the total execution time with the number of tuple for the last query in the file. Is there something else to do in order to get the execution time of each of the queries.
mclient -i -u monetdb -d voc query.sql | tail -n1
Best regards, Baraa
Date: Sun, 13 Oct 2013 16:50:08 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
On 10/13/13 4:41 PM, Stefan Manegold wrote:
GREAT EXPLICATION! Thanks for this helpful and clear explication. I think it's very benificial to add it somewhere on the documentation of MonetDB (if it's not already the case); I spent three days googling and trying to understand how is that works.
See: http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming Clear enough?
The explanation of "time" is on its man page. We'll consider adding the explanation of mclient's "-i" option to its man page.
I have one more question please, what if I have more that one query in my Q.sql? can I get the time (2: reported by mclient -i option) for each of the queries or I can only get the total time?
unless you have more than one query per line, you'll get individual times.
Stefan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thank you for your answer even for my "stupid" question, in fact I'm not really used to use linux. I had had to google it before asking. Thank you again :) Best regards,Baraa
Date: Sun, 13 Oct 2013 22:29:22 +0200 From: Stefan.Manegold@cwi.nl To: users-list@monetdb.org Subject: Re: Query execution time
http://lmgtfy.com/?q=man+mclient
$ echo 'select 1;' > /tmp/Q.sql $ echo 'select 2;' >> /tmp/Q.sql $ cat /tmp/Q.sql select 1; select 2; $ mclient -i /tmp/Q.sql | tail -n1 1 tuple (0.078ms) $ mclient -i /tmp/Q.sql | grep -E '^[0-9]+ tuples? (.*)$' 1 tuple (0.742ms) 1 tuple (0.682ms)
Best, Stefan
----- Original Message -----
I tried to execute it with a file (Q.sql) containing more than one query, it seems that it gives me the total execution time with the number of tuple for the last query in the file. Is there something else to do in order to get the execution time of each of the queries.
mclient -i -u monetdb -d voc query.sql | tail -n1
Best regards, Baraa
Date: Sun, 13 Oct 2013 16:50:08 +0200 From: martin@monetdb.org To: users-list@monetdb.org Subject: Re: Query execution time
On 10/13/13 4:41 PM, Stefan Manegold wrote:
GREAT EXPLICATION! Thanks for this helpful and clear explication. I think it's very benificial to add it somewhere on the documentation of MonetDB (if it's not already the case); I spent three days googling and trying to understand how is that works.
See: http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming Clear enough?
The explanation of "time" is on its man page. We'll consider adding the explanation of mclient's "-i" option to its man page.
I have one more question please, what if I have more that one query in my Q.sql? can I get the time (2: reported by mclient -i option) for each of the queries or I can only get the total time?
unless you have more than one query per line, you'll get individual times.
Stefan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
baraa Mohamad -
Martin Kersten -
Martin Kersten -
Stefan Manegold