[pgrouting-dev] Develop the automatic testing tool

[Stephen Woodbridge]

On 6/29/2012 10:38 PM, Jinfu Leng wrote:
> Hey all,
>
> I almost finished my code for the new algorithm. Next I want to test the
> performance (regarding computing time) of these algorithms. I am
> planning to randomly generate some source-target pairs, and use them for
> testing. But I do not know where to start.
>
> My questions are:
> How to execute the query from the code? (I always test the algorithms
> from pgadmin)
> How to save the results? (I want to save the total distance at least,
> and then I can use these results to make sure all the algorithms are right)
> How to measure the computing time of each algorithm?

There are a lot of ways to build automated tests. I will outline some of 
them below. One big thing to watch out for is to be aware of WHAT you 
are measuring.

1. you can use linux command line in a script like:

time psql -U user -h localhost databse -f input.sql

in the input.sql you can start off with '\timing on' to get it to echo 
query times for each command. There are other command line options read 
'man psql' for details that might be useful.

Be aware that you are measuring psql startup time, database connect 
time, and the query time of the commands, the transfer of the results 
and printing the results. If the query time is small relative to all the 
other stuff. this might be problematic.

2. you can use a perl script with DBI and Time::HighRes modules to 
measure the time a query takes to run. If you are knowledgeable in perl 
this also makes it easy to read data and query input from files and log 
the results to a file.

3. If you like plpgsql or SQL, you could create a table in your database 
with the data, queries, and tests that you want to run. maybe with a 
structure like:

create table testdata (
   testid integer not null primary key,
   query text,
   expected_results text,
   ...
);

And a table for results:

create table testresults {
   id integer not null primary key,
   testid integer,
   run_date time stamp with timezone,
   duration integer,
   pass integer,
   comments text
);

Then you could write a stored procedure(s) like:
   select * from runtests();
   select * from runtests(testid);

this would read the test(s) from testdata
run each test requested
store the results in testresults
and return the results one for each test run.

You can do timing via SQL and plpgsql using timestamps and extracting 
microseconds from the interval between the start and end timestamps that 
can bracket a query.

4. you could add timing collection in your C/C++ code and return it via 
the NOTICE facility, like what we do for debugging.

And I'm sure there are other ways to do this.

-Steve