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" 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

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" 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" 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" 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" 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&id=14302 ----- 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) |

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).

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&id=14302

----- 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

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

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

http://lmgtfy.com/?q=man+mclient http://lmgtfy.com/?q=man+tail $ 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) http://lmgtfy.com/?q=man+grep 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) |