Mercurial Hosting > traffic-intelligence
comparison scripts/performance.sh @ 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 | |
| children | b0cb644950aa |
comparison
equal
deleted
inserted
replaced
| 711:523eda2fafd4 | 712:21aeadcfbabb |
|---|---|
| 1 #!/bin/bash | |
| 2 if [ $# -ge 1 ]; | |
| 3 then | |
| 4 dbFilename=$1 | |
| 5 else | |
| 6 dbFilename="./samples/laurier.sqlite" | |
| 7 fi | |
| 8 echo "Performance report using "$dbFilename | |
| 9 cp $dbFilename $dbFilename.test | |
| 10 dbFilename=$dbFilename.test | |
| 11 nFeatures=`sqlite3 $dbFilename "select count(distinct trajectory_id) from positions"` | |
| 12 | |
| 13 #### | |
| 14 # test query positions/velocities in feature grouping | |
| 15 #### | |
| 16 echo "####" | |
| 17 echo "test query positions" | |
| 18 echo "####" | |
| 19 | |
| 20 selectTrajectories() { | |
| 21 for (( i=1; i<=$nFeatures; i++ )) | |
| 22 do | |
| 23 sqlite3 $dbFilename "select * from positions where trajectory_id = ${i} order by frame_number" > /dev/null | |
| 24 done | |
| 25 } | |
| 26 | |
| 27 dropIndices() { | |
| 28 sqlite3 $dbFilename "drop index if exists trajectory_id_index" | |
| 29 sqlite3 $dbFilename "drop index if exists frame_number_index" | |
| 30 sqlite3 $dbFilename "drop index if exists tid_frame_index" | |
| 31 sqlite3 $dbFilename "drop index if exists of_trajectory_id_index" | |
| 32 } | |
| 33 | |
| 34 echo "no index" | |
| 35 dropIndices | |
| 36 time selectTrajectories | |
| 37 #### | |
| 38 echo "with index on trajectory_id" | |
| 39 sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)" | |
| 40 time selectTrajectories | |
| 41 #### | |
| 42 echo "with index on trajectory_id and frame_number" | |
| 43 sqlite3 $dbFilename "create index frame_number_index on positions(frame_number)" | |
| 44 time selectTrajectories | |
| 45 #### | |
| 46 echo "with index on the 2 columns trajectory_id" | |
| 47 dropIndices | |
| 48 sqlite3 $dbFilename "create index tid_frame_index on positions(trajectory_id,frame_number)" | |
| 49 time selectTrajectories | |
| 50 #### | |
| 51 echo "query all feature positions, order by id and frame_number (index/no index)" | |
| 52 time (sqlite3 $dbFilename "select * from positions order by trajectory_id, frame_number" > /dev/null) | |
| 53 dropIndices | |
| 54 time (sqlite3 $dbFilename "select * from positions order by trajectory_id, frame_number" > /dev/null) | |
| 55 # no effect: probably because already ordered | |
| 56 | |
| 57 #### | |
| 58 # test query positions/velocities in feature grouping | |
| 59 #### | |
| 60 echo "####" | |
| 61 echo "test query object positions" | |
| 62 echo "####" | |
| 63 | |
| 64 selectObjectTrajectories() { | |
| 65 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 | |
| 66 } | |
| 67 echo "no index" | |
| 68 dropIndices | |
| 69 time selectObjectTrajectories | |
| 70 #### | |
| 71 echo "index on trajectory_id" | |
| 72 sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)" | |
| 73 time selectObjectTrajectories | |
| 74 #### | |
| 75 echo "index on trajectory_id in positions and objects_features" | |
| 76 sqlite3 $dbFilename "create index of_trajectory_id_index on objects_features(trajectory_id)" | |
| 77 time selectObjectTrajectories | |
| 78 | |
| 79 | |
| 80 ### | |
| 81 echo "Cleaning up!" | |
| 82 dropIndices | |
| 83 rm $dbFilename |