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