Mercurial Hosting > traffic-intelligence
annotate scripts/performance-db.sh @ 1002:6c5ce3ec497e
improved handling of path for feature based tracking
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Fri, 01 Jun 2018 17:19:24 -0400 |
parents | a850a4f92735 |
children |
rev | line source |
---|---|
712
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
1 #!/bin/bash |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
2 if [ $# -ge 1 ]; |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
3 then |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
4 dbFilename=$1 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
5 else |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
6 dbFilename="./samples/laurier.sqlite" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
7 fi |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
8 echo "Performance report using "$dbFilename |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
9 cp $dbFilename $dbFilename.test |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
10 dbFilename=$dbFilename.test |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
11 nFeatures=`sqlite3 $dbFilename "select count(distinct trajectory_id) from positions"` |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
12 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
13 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
14 # test query positions/velocities in feature grouping |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
15 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
16 echo "####" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
17 echo "test query positions" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
18 echo "####" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
19 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
20 selectTrajectories() { |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
21 for (( i=1; i<=$nFeatures; i++ )) |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
22 do |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
23 sqlite3 $dbFilename "select * from positions where trajectory_id = ${i} order by frame_number" > /dev/null |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
24 done |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
25 } |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
26 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
27 dropIndices() { |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
28 sqlite3 $dbFilename "drop index if exists trajectory_id_index" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
29 sqlite3 $dbFilename "drop index if exists frame_number_index" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
30 sqlite3 $dbFilename "drop index if exists tid_frame_index" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
31 sqlite3 $dbFilename "drop index if exists of_trajectory_id_index" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
32 } |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
33 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
34 echo "no index" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
35 dropIndices |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
36 time selectTrajectories |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
37 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
38 echo "with index on trajectory_id" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
39 sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
40 time selectTrajectories |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
41 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
42 echo "with index on trajectory_id and frame_number" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
43 sqlite3 $dbFilename "create index frame_number_index on positions(frame_number)" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
44 time selectTrajectories |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
45 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
46 echo "with index on the 2 columns trajectory_id" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
47 dropIndices |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
48 sqlite3 $dbFilename "create index tid_frame_index on positions(trajectory_id,frame_number)" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
49 time selectTrajectories |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
50 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
51 echo "query all feature positions, order by id and frame_number (index/no index)" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
52 time (sqlite3 $dbFilename "select * from positions order by trajectory_id, frame_number" > /dev/null) |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
53 dropIndices |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
54 time (sqlite3 $dbFilename "select * from positions order by trajectory_id, frame_number" > /dev/null) |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
55 # no effect: probably because already ordered |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
56 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
57 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
58 # test query positions/velocities in feature grouping |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
59 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
60 echo "####" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
61 echo "test query object positions" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
62 echo "####" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
63 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
64 selectObjectTrajectories() { |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
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 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
66 } |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
67 echo "no index" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
68 dropIndices |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
69 time selectObjectTrajectories |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
70 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
71 echo "index on trajectory_id" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
72 sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
73 time selectObjectTrajectories |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
74 #### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
75 echo "index on trajectory_id in positions and objects_features" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
76 sqlite3 $dbFilename "create index of_trajectory_id_index on objects_features(trajectory_id)" |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
77 time selectObjectTrajectories |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
78 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
79 |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
80 ### |
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
81 echo "Cleaning up!" |
713
b0cb644950aa
added results on one of Norway db
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
712
diff
changeset
|
82 #dropIndices |
712
21aeadcfbabb
added script to test SQLite performance and impact of indices
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff
changeset
|
83 rm $dbFilename |