Mercurial > hg > nsaunier > traffic-intelligence

comparison scripts/performance-db.py @ 1012:01db14e947e4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
resolved
author Wendlasida
date Fri, 01 Jun 2018 10:47:49 -0400
parents 933670761a57
children cc5cb04b04b0
comparison
equal deleted inserted replaced
1011:4f0312bee393 1012:01db14e947e4
1 #! /usr/bin/env python 1 #! /usr/bin/env python3
2 2
3 import sys, shutil, os, sqlite3, timeit#, argparse 3 import sys, shutil, os, sqlite3, timeit#, argparse
4 4
5 import storage 5 import storage
6 6
13 shutil.copy(dbFilename, dbFilename+'.test') 13 shutil.copy(dbFilename, dbFilename+'.test')
14 dbFilename=dbFilename+'.test' 14 dbFilename=dbFilename+'.test'
15 connection = sqlite3.connect(dbFilename) 15 connection = sqlite3.connect(dbFilename)
16 16
17 nFeatures=storage.getNumberRowsTable(connection, "positions", "trajectory_id") 17 nFeatures=storage.getNumberRowsTable(connection, "positions", "trajectory_id")
18 print dbFilename, nFeatures 18 print(dbFilename, nFeatures)
19 19
20 #### 20 ####
21 # test query tmp trajectory instant table 21 # test query tmp trajectory instant table
22 #### 22 ####
23 def selectTrajectoryIdInstant(connection, lastInstant): 23 def selectTrajectoryIdInstant(connection, lastInstant):
24 cursor = connection.cursor() 24 cursor = connection.cursor()
25 for i in xrange(lastInstant): 25 for i in range(lastInstant):
26 cursor.execute("select trajectory_id from trajectory_instants where last_instant = {}".format(lastInstant)) 26 cursor.execute("select trajectory_id from trajectory_instants where last_instant = {}".format(lastInstant))
27 cursor.fetchall() 27 cursor.fetchall()
28 28
29 def test2Setup(connection): 29 def test2Setup(connection):
30 cursor = connection.cursor() 30 cursor = connection.cursor()
42 #### 42 ####
43 # test query positions/velocities in feature grouping 43 # test query positions/velocities in feature grouping
44 #### 44 ####
45 def selectTrajectories(connection, nFeatures): 45 def selectTrajectories(connection, nFeatures):
46 cursor = connection.cursor() 46 cursor = connection.cursor()
47 for i in xrange(nFeatures): 47 for i in range(nFeatures):
48 cursor.execute("select * from positions where trajectory_id = {} order by frame_number".format(i)) 48 cursor.execute("select * from positions where trajectory_id = {} order by frame_number".format(i))
49 cursor.fetchall() 49 cursor.fetchall()
50 50
51 def dropIndices(connection): 51 def dropIndices(connection):
52 cursor = connection.cursor() 52 cursor = connection.cursor()
64 dropIndices(connection) 64 dropIndices(connection)
65 print timeit.timeit("selectTrajectories(connection, nFeatures)", setup="from __main__ import selectTrajectories, connection, nFeatures", number = 100) 65 print timeit.timeit("selectTrajectories(connection, nFeatures)", setup="from __main__ import selectTrajectories, connection, nFeatures", number = 100)
66 #### 66 ####
67 print("with index on trajectory_id") 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)" 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) 69 print(timeit.timeit("selectTrajectories(connection, nFeatures)", setup="from __main__ import selectTrajectories, connection, nFeatures", number = 100))
70 70
71 #### Cleanup 71 #### Cleanup
72 os.remove(dbFilename) 72 os.remove(dbFilename)