Mercurial > hg > nsaunier > traffic-intelligence

comparison scripts/performance-db.py @ 730:a850a4f92735 dev

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.py@d6c69d3d09e5
children 933670761a57
comparison
equal deleted inserted replaced
729:dad99b86a104 730:a850a4f92735
1 #! /usr/bin/env python
2
3 import sys, shutil, os, sqlite3, timeit#, argparse
4
5 import storage
6
7 if len(sys.argv) >= 2:
8 dbFilename = sys.argv[1]
9 else:
10 dbFilename="./samples/laurier.sqlite"
11
12 print("Performance report using {}".format(dbFilename))
13 shutil.copy(dbFilename, dbFilename+'.test')
14 dbFilename=dbFilename+'.test'
15 connection = sqlite3.connect(dbFilename)
16
17 nFeatures=storage.getNumberRowsTable(connection, "positions", "trajectory_id")
18 print dbFilename, nFeatures
19
20 ####
21 # test query tmp trajectory instant table
22 ####
23 def selectTrajectoryIdInstant(connection, lastInstant):
24 cursor = connection.cursor()
25 for i in xrange(lastInstant):
26 cursor.execute("select trajectory_id from trajectory_instants where last_instant = {}".format(lastInstant))
27 cursor.fetchall()
28
29 def test2Setup(connection):
30 cursor = connection.cursor()
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")
32 return storage.getMinMax(connection, "trajectory_instants", "last_instant", "max")
33
34 # lastInstant = test2Setup(connection)
35 # print timeit.timeit("selectTrajectoryIdInstant(connection, lastInstant)", setup="from __main__ import selectTrajectoryIdInstant, connection, lastInstant", number = 100)
36 # storage.createIndex(connection, "trajectory_instants", "last_instant")
37 # print timeit.timeit("selectTrajectoryIdInstant(connection, lastInstant)", setup="from __main__ import selectTrajectoryIdInstant, connection, lastInstant", number = 100)
38 # storage.createIndex(connection, "trajectory_instants", "trajectory_id", True)
39 # print timeit.timeit("selectTrajectoryIdInstant(connection, lastInstant)", setup="from __main__ import selectTrajectoryIdInstant, connection, lastInstant", number = 100)
40
41
42 ####
43 # test query positions/velocities in feature grouping
44 ####
45 def selectTrajectories(connection, nFeatures):
46 cursor = connection.cursor()
47 for i in xrange(nFeatures):
48 cursor.execute("select * from positions where trajectory_id = {} order by frame_number".format(i))
49 cursor.fetchall()
50
51 def dropIndices(connection):
52 cursor = connection.cursor()
53 cursor.execute("drop index if exists positions_trajectory_id_index")
54 #sqlite3 $dbFilename "drop index if exists frame_number_index"
55 #sqlite3 $dbFilename "drop index if exists tid_frame_index"
56 #sqlite3 $dbFilename "drop index if exists of_trajectory_id_index"
57 connection.commit()
58
59 def test1Positions():
60 print('''####
61 test query positions
62 ####''')
63 print("no index")
64 dropIndices(connection)
65 print timeit.timeit("selectTrajectories(connection, nFeatures)", setup="from __main__ import selectTrajectories, connection, nFeatures", number = 100)
66 ####
67 print("with index on trajectory_id")
68 storage.createIndex(connection, "positions", "trajectory_id")#sqlite3 $dbFilename "create index trajectory_id_index on positions(trajectory_id)"
69 print timeit.timeit("selectTrajectories(connection, nFeatures)", setup="from __main__ import selectTrajectories, connection, nFeatures", number = 100)
70
71 #### Cleanup
72 os.remove(dbFilename)