Which is the execution time of aql?


#1

i use the shell command to see how long an afl statement will cost .
the statement is below:

"SELECT G.ObjID, G.u, G.g, G.r, G.i, G.z FROM (select ObjID, u, g, r, i, z ,parentID from Galaxy where parentID>0 ) AS G JOIN (select parentID from Star where parentID>0 AS S ON G.parentID = S.parentID "
and it takes a long time to execute.which says:
[color=#FF0000]“
real 1066m58.109s
user 0m18.837s
sys 0m2.774s
”[/color]

so ,which is the real execution time?

tks


#2

You’re using the “time” Linux tool?

If you’re the only user running queries on the box, them the important/relevant time is the “real” time, which is the wall clock time associated with the iquery execution time. It’s the amount of time that passed between when the process ID was created for the iquery program and when it exited. In other words, it’s the time that includes both the iquery processing time, AND the server side processing time. When we time query performance in-house, the real execution time of the overall query is what we look at first. When we want to go deeper, we try to break queries up into smaller sections by looking at how long / how much compute / memory / IO resource, was consumed by each operator in a query plan.

The other two numbers … user and sys … refer to the user space CPU time and the system space CPU time for the iquery process. They’re not really relevant to time the query as they don’t include server-side processing.

Have a look at “man time”.


#3

[quote=“plumber”]You’re using the “time” Linux tool?

If you’re the only user running queries on the box, them the important/relevant time is the “real” time, which is the wall clock time associated with the iquery execution time. It’s the amount of time that passed between when the process ID was created for the iquery program and when it exited. In other words, it’s the time that includes both the iquery processing time, AND the server side processing time. When we time query performance in-house, the real execution time of the overall query is what we look at first. When we want to go deeper, we try to break queries up into smaller sections by looking at how long / how much compute / memory / IO resource, was consumed by each operator in a query plan.

The other two numbers … user and sys … refer to the user space CPU time and the system space CPU time for the iquery process. They’re not really relevant to time the query as they don’t include server-side processing.

Have a look at “man time”.[/quote]

tks.


#4

I recommend the following:

  • switch to AFL or the recommended scidb-R, scidb-Py like packages
  • use AFL between whenever filtering on dimensions
  • use cross_join for better join performance than AQL join
  • use consume() to benchmark query performance.

Also – please take a look at this thread. It touches a few topics:
viewtopic.php?f=11&t=1196&hilit=consume&start=10


#5

[quote=“apoliakov”]I recommend the following:

  • switch to AFL or the recommended scidb-R, scidb-Py like packages
  • use AFL between whenever filtering on dimensions
  • use cross_join for better join performance than AQL join
  • use consume() to benchmark query performance.

Also – please take a look at this thread. It touches a few topics:
viewtopic.php?f=11&t=1196&hilit=consume&start=10[/quote]

tks.i will try. :smiley:


#6

[quote=“plumber”]You’re using the “time” Linux tool?

If you’re the only user running queries on the box, them the important/relevant time is the “real” time, which is the wall clock time associated with the iquery execution time. It’s the amount of time that passed between when the process ID was created for the iquery program and when it exited. In other words, it’s the time that includes both the iquery processing time, AND the server side processing time. When we time query performance in-house, the real execution time of the overall query is what we look at first. When we want to go deeper, we try to break queries up into smaller sections by looking at how long / how much compute / memory / IO resource, was consumed by each operator in a query plan.

The other two numbers … user and sys … refer to the user space CPU time and the system space CPU time for the iquery process. They’re not really relevant to time the query as they don’t include server-side processing.

Have a look at “man time”.[/quote]

I am wondering which tool you use to check the CPU / memory / IO resources a SciDB query takes. The tool may be like EXPLAIN ANALYZE for postgresql or some linux tool?


#7

[quote=“rencailhc”][quote=“plumber”]You’re using the “time” Linux tool?

If you’re the only user running queries on the box, them the important/relevant time is the “real” time, which is the wall clock time associated with the iquery execution time. It’s the amount of time that passed between when the process ID was created for the iquery program and when it exited. In other words, it’s the time that includes both the iquery processing time, AND the server side processing time. When we time query performance in-house, the real execution time of the overall query is what we look at first. When we want to go deeper, we try to break queries up into smaller sections by looking at how long / how much compute / memory / IO resource, was consumed by each operator in a query plan.

The other two numbers … user and sys … refer to the user space CPU time and the system space CPU time for the iquery process. They’re not really relevant to time the query as they don’t include server-side processing.

Have a look at “man time”.[/quote]

I am wondering which tool you use to check the CPU / memory / IO resources a SciDB query takes. The tool may be like EXPLAIN ANALYZE for postgresql or some linux tool?[/quote]

i use the linux time interface,i think the real time as the execution time.


#8

Paul is on vacation this week.
I often monitor CPU and memory usage using the ‘top’ unix command.
To check how many seconds a SciDB query e.g. list() runs, without printing out the query result onto the screen, I sometimes do:

/usr/bin/time -f “%e” iquery -naq “list()”


#9

[quote=“donghui”]Paul is on vacation this week.
I often monitor CPU and memory usage using the ‘top’ unix command.
To check how many seconds a SciDB query e.g. list() runs, without printing out the query result onto the screen, I sometimes do:

/usr/bin/time -f “%e” iquery -naq “list()”[/quote]

i use that too.


#10

Great to know. Thanks all.