Mercurial Hosting > traffic-intelligence

annotate scripts/performance-db.py @ 1222:69b531c7a061

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added methods to reset trajectories and change object coordinates (including features)
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Tue, 20 Jun 2023 15:42:19 -0400
parents cc5cb04b04b0
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
998
933670761a57 updated code to python 3 (tests pass and scripts run, but non-executed parts of code are probably still not correct)
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents: 730
diff changeset
1 #! /usr/bin/env python3
714
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
2
1028
cc5cb04b04b0 major update using the trafficintelligence package name and install through pip
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents: 998
diff changeset
3 import sys, shutil, os, sqlite3, timeit
714
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
4
1028
cc5cb04b04b0 major update using the trafficintelligence package name and install through pip
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents: 998
diff changeset
5 from trafficintelligence import storage
714
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
6
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
7 if len(sys.argv) >= 2:
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
8 dbFilename = sys.argv[1]
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
9 else:
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
10 dbFilename="./samples/laurier.sqlite"
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
11
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
12 print("Performance report using {}".format(dbFilename))
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
13 shutil.copy(dbFilename, dbFilename+'.test')
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
14 dbFilename=dbFilename+'.test'
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
15 connection = sqlite3.connect(dbFilename)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
16
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
17 nFeatures=storage.getNumberRowsTable(connection, "positions", "trajectory_id")
998
933670761a57 updated code to python 3 (tests pass and scripts run, but non-executed parts of code are probably still not correct)
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents: 730
diff changeset
18 print(dbFilename, nFeatures)
714
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
19
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
20 ####
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
21 # test query tmp trajectory instant table
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
22 ####
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
23 def selectTrajectoryIdInstant(connection, lastInstant):
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
24 cursor = connection.cursor()
998
933670761a57 updated code to python 3 (tests pass and scripts run, but non-executed parts of code are probably still not correct)
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents: 730
diff changeset
25 for i in range(lastInstant):
714
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
26 cursor.execute("select trajectory_id from trajectory_instants where last_instant = {}".format(lastInstant))
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
27 cursor.fetchall()
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
28
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
29 def test2Setup(connection):
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
30 cursor = connection.cursor()
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
31 cursor.execute("CREATE TEMP TABLE IF NOT EXISTS trajectory_instants AS select trajectory_id, min(frame_number) as first_instant, max(frame_number) as last_instant, max(frame_number)-min(frame_number)+1 as length from positions group by trajectory_id")
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
32 return storage.getMinMax(connection, "trajectory_instants", "last_instant", "max")
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
33
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
34 # lastInstant = test2Setup(connection)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
35 # print timeit.timeit("selectTrajectoryIdInstant(connection, lastInstant)", setup="from __main__ import selectTrajectoryIdInstant, connection, lastInstant", number = 100)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
36 # storage.createIndex(connection, "trajectory_instants", "last_instant")
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
37 # print timeit.timeit("selectTrajectoryIdInstant(connection, lastInstant)", setup="from __main__ import selectTrajectoryIdInstant, connection, lastInstant", number = 100)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
38 # storage.createIndex(connection, "trajectory_instants", "trajectory_id", True)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
39 # print timeit.timeit("selectTrajectoryIdInstant(connection, lastInstant)", setup="from __main__ import selectTrajectoryIdInstant, connection, lastInstant", number = 100)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
40
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
41
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
42 ####
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
43 # test query positions/velocities in feature grouping
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
44 ####
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
45 def selectTrajectories(connection, nFeatures):
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
46 cursor = connection.cursor()
998
933670761a57 updated code to python 3 (tests pass and scripts run, but non-executed parts of code are probably still not correct)
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents: 730
diff changeset
47 for i in range(nFeatures):
714
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
48 cursor.execute("select * from positions where trajectory_id = {} order by frame_number".format(i))
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
49 cursor.fetchall()
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
50
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
51 def dropIndices(connection):
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
52 cursor = connection.cursor()
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
53 cursor.execute("drop index if exists positions_trajectory_id_index")
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
54 #sqlite3 $dbFilename "drop index if exists frame_number_index"
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
55 #sqlite3 $dbFilename "drop index if exists tid_frame_index"
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
56 #sqlite3 $dbFilename "drop index if exists of_trajectory_id_index"
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
57 connection.commit()
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
58
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
59 def test1Positions():
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
60 print('''####
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
61 test query positions
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
62 ####''')
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
63 print("no index")
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
64 dropIndices(connection)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
65 print timeit.timeit("selectTrajectories(connection, nFeatures)", setup="from __main__ import selectTrajectories, connection, nFeatures", number = 100)
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
66 ####
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
67 print("with index on trajectory_id")
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
68 storage.createIndex(connection, "positions", "trajectory_id")#sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)"
998
933670761a57 updated code to python 3 (tests pass and scripts run, but non-executed parts of code are probably still not correct)
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents: 730
diff changeset
69 print(timeit.timeit("selectTrajectories(connection, nFeatures)", setup="from __main__ import selectTrajectories, connection, nFeatures", number = 100))
714
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
70
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
71 #### Cleanup
d6c69d3d09e5 developing python performance tests for SQLite
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
72 os.remove(dbFilename)