Mercurial Hosting > traffic-intelligence
comparison scripts/performance.py @ 714:d6c69d3d09e5
developing python performance tests for SQLite
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Fri, 24 Jul 2015 17:58:59 -0400 |
parents | |
children |
comparison
equal
deleted
inserted
replaced
713:b0cb644950aa | 714:d6c69d3d09e5 |
---|---|
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) |