Mercurial Hosting > traffic-intelligence
changeset 712:21aeadcfbabb
added script to test SQLite performance and impact of indices
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Thu, 23 Jul 2015 01:13:32 -0400 |
parents | 523eda2fafd4 |
children | b0cb644950aa |
files | python/storage.py scripts/performance.sh |
diffstat | 2 files changed, 85 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/python/storage.py Wed Jul 22 17:54:33 2015 -0400 +++ b/python/storage.py Thu Jul 23 01:13:32 2015 -0400 @@ -338,7 +338,7 @@ queryStatement = 'SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from '+tableName+' P, objects_features OF where P.trajectory_id = OF.trajectory_id' if objectNumbers is not None: queryStatement += ' and OF.object_id '+objectCriteria - queryStatement += ' group by OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' + queryStatement += ' GROUP BY OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' cursor.execute(queryStatement) logging.debug(queryStatement) elif trajectoryType in ['bbtop', 'bbbottom']: @@ -348,7 +348,7 @@ corner = 'bottom_right' queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName if objectNumbers is not None: - queryStatement += ' where object_id '+objectCriteria + queryStatement += ' WHERE object_id '+objectCriteria queryStatement += ' ORDER BY object_id, frame_number' cursor.execute(queryStatement) logging.debug(queryStatement)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/scripts/performance.sh Thu Jul 23 01:13:32 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