Mercurial Hosting > traffic-intelligence

annotate scripts/performance-db.sh @ 1219:8a626226793e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
update where optimization uses either nomad-parameter file depending on optimizing 1 or 2 steps
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Mon, 19 Jun 2023 17:09:56 -0400
parents a850a4f92735
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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