--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/scripts/performance-db.sh	Tue Aug 11 10:52:04 2015 -0400
@@ -0,0 +1,83 @@
+#!/bin/bash
+if [ $# -ge 1 ];
+then
+    dbFilename=$1
+else
+    dbFilename="./samples/laurier.sqlite"
+fi
+echo "Performance report using "$dbFilename
+cp $dbFilename $dbFilename.test
+dbFilename=$dbFilename.test
+nFeatures=`sqlite3 $dbFilename "select count(distinct trajectory_id) from positions"`
+
+####
+# test query positions/velocities in feature grouping
+####
+echo "####"
+echo "test query positions"
+echo "####"
+
+selectTrajectories() {
+    for (( i=1; i<=$nFeatures; i++ ))
+    do
+	sqlite3 $dbFilename "select * from positions where trajectory_id = ${i} order by frame_number" > /dev/null
+    done
+}
+
+dropIndices() {
+    sqlite3 $dbFilename "drop index if exists trajectory_id_index"
+    sqlite3 $dbFilename "drop index if exists frame_number_index"
+    sqlite3 $dbFilename "drop index if exists tid_frame_index"
+    sqlite3 $dbFilename "drop index if exists of_trajectory_id_index"
+}
+
+echo "no index"
+dropIndices
+time selectTrajectories
+####
+echo "with index on trajectory_id"
+sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)"
+time selectTrajectories
+####
+echo "with index on trajectory_id and frame_number"
+sqlite3 $dbFilename "create index frame_number_index on positions(frame_number)"
+time selectTrajectories
+####
+echo "with index on the 2 columns trajectory_id"
+dropIndices
+sqlite3 $dbFilename "create index tid_frame_index on positions(trajectory_id,frame_number)"
+time selectTrajectories
+####
+echo "query all feature positions, order by id and frame_number (index/no index)"
+time (sqlite3 $dbFilename "select * from positions order by trajectory_id, frame_number" > /dev/null)
+dropIndices
+time (sqlite3 $dbFilename "select * from positions order by trajectory_id, frame_number" > /dev/null)
+# no effect: probably because already ordered
+
+####
+# test query positions/velocities in feature grouping
+####
+echo "####"
+echo "test query object positions"
+echo "####"
+
+selectObjectTrajectories() {
+    sqlite3 $dbFilename "SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id group by OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number" > /dev/null
+}
+echo "no index"
+dropIndices
+time selectObjectTrajectories
+####
+echo "index on trajectory_id"
+sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)"
+time selectObjectTrajectories
+####
+echo "index on trajectory_id in positions and objects_features"
+sqlite3 $dbFilename "create index of_trajectory_id_index on objects_features(trajectory_id)"
+time selectObjectTrajectories
+
+
+###
+echo "Cleaning up!"
+#dropIndices
+rm $dbFilename