/test/decisions/sep_type_column.py

 1import sqlite3
 2import time
 3
 4import ajc
 5
 6db_one = sqlite3.connect('sep.one.sqlite3')
 7cu_one = db_one.cursor()
 8cu_one.execute("CREATE TABLE IF NOT EXISTS my_table (fid INTEGER, key TEXT, value TEXT, PRIMARY KEY (fid, key))")
 9db_two = sqlite3.connect('sep.two.sqlite3')
10cu_two = db_two.cursor()
11cu_two.execute("CREATE TABLE IF NOT EXISTS my_table (fid INTEGER, key TEXT, type TEXT, value TEXT, PRIMARY KEY (fid, key))")
12
13N = 100000
14
15def i1():
16    ajc.util.time_start(msg = '1 column insert')
17    for i in range(N): 
18        cu_one.execute('INSERT OR REPLACE INTO my_table (fid, key, value) VALUES (?,?,?)', (i, 'a_key', 'i' + str(i),))     
19        cu_one.execute('INSERT OR REPLACE INTO my_table (fid, key, value) VALUES (?,?,?)', (i, 'b_key', 'i' + str(4),))     
20    db_one.commit()
21    ajc.util.time_stop()
22
23def i2():
24    ajc.util.time_start(msg = '2 column insert')
25    for i in range(N): 
26        cu_two.execute('INSERT OR REPLACE INTO my_table (fid, key, type, value) VALUES (?,?,?,?)', (i, 'a_key', 'i', i,))     
27        cu_two.execute('INSERT OR REPLACE INTO my_table (fid, key, type, value) VALUES (?,?,?,?)', (i, 'b_key', 'i', 4,))     
28    db_two.commit()
29    ajc.util.time_stop()
30
31def e1():
32    ajc.util.time_start(msg = '1 column query equals')
33    for row in cu_one.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND value = 'i4'"):
34        fid = row[0]
35    db_one.commit()
36    ajc.util.time_stop()
37
38def e2():
39    ajc.util.time_start(msg = '2 column query equals')
40    for row in cu_two.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND type = 'i' AND value = '4'"):
41        fid = row[0]
42    db_two.commit()
43    ajc.util.time_stop()
44
45def l1():
46    ajc.util.time_start(msg = '1 column query less than')
47    for row in cu_one.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND substr(value,1,1) = 'i' AND 1*substr(value,2) < 75000"):
48        fid = row[0]
49        #print "A", fid

50    db_one.commit()
51    ajc.util.time_stop()
52
53def l2():
54    ajc.util.time_start(msg = '2 column query less than')
55    for row in cu_two.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND type = 'i' AND 1*value < 75000"):
56        fid = row[0]
57        #print "B", fid

58    db_two.commit()
59    ajc.util.time_stop()
60
61i1()
62i2()
63i2()
64i2()
65i1()
66i1()
67i2()
68i1()
69
70e2()
71e1()
72e2()
73e2()
74e1()
75e1()
76e2()
77e1()
78
79l2()
80l1()
81l2()
82l2()
83l1()
84l1()
85l2()
86l1()
87
88
89