Mercurial Hosting > traffic-intelligence
comparison scripts/performance-db.sh @ 730:a850a4f92735 dev
added performance script comparing lcss using cdist with default
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Tue, 11 Aug 2015 10:52:04 -0400 |
parents | scripts/performance.sh@b0cb644950aa |
children |
comparison
equal
deleted
inserted
replaced
729:dad99b86a104 | 730:a850a4f92735 |
---|---|
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 |