Mercurial Hosting > traffic-intelligence
annotate scripts/performance-db.py @ 1234:dd969637381e
work on tracker interface
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Thu, 14 Sep 2023 16:18:36 -0400 |
parents | cc5cb04b04b0 |
children |
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) |