/test/speed/understand_dynamic_index.py

  1import ajc
  2import pod
  3import time
  4import random
  5
  6class PersonNoTyped(pod.Object):
  7    POD_DYNAMIC_INDEX = True
  8
  9class PersonNoTypedWithIndex(pod.Object):
 10    POD_DYNAMIC_INDEX = True
 11    
 12class PersonTyped(pod.Object):
 13    age    = pod.typed.Int(index = False)
 14    name   = pod.typed.String(index = False)
 15    weight = pod.typed.Float(index = False)
 16    phrase = pod.typed.String(index = False)
 17    
 18class PersonTypedWithIndex(pod.Object):
 19    age    = pod.typed.Int(index = True)
 20    name   = pod.typed.String(index = True)
 21    weight = pod.typed.Float(index = True)
 22    phrase = pod.typed.String(index = True)
 23    
 24
 25
 26db = pod.Db(file = 'mypod100000.sqlite3', remove = False, clear = False, very_chatty = False)
 27
 28
 29ajc.util.time_start(msg = 'Check fid speed')
 30print len([row for row in PersonTyped.execute('SELECT age FROM cls_table WHERE age = 25')])
 31ajc.util.time_stop()
 32
 33ajc.util.time_start(msg = 'Check fid speed')
 34print len([row for row in PersonNoTyped.execute('SELECT str FROM cls_table_kvdict WHERE fid = 94340')])
 35ajc.util.time_stop()
 36
 37ajc.util.time_start(msg = 'Check fid speed')
 38print len([row for row in PersonNoTyped.execute('SELECT str FROM cls_table_kvdict WHERE fid = 9440 and key = "name"')])
 39ajc.util.time_stop()
 40
 41ajc.util.time_start(msg = 'Check fid speed')
 42print len([row for row in PersonTyped.execute('SELECT age FROM cls_table WHERE age = 25')])
 43ajc.util.time_stop()
 44
 45exit()
 46
 47ajc.util.time_start(msg = 'PersonNoTyped org Query')
 48print len([peep for peep in PersonNoTyped.age < 25])
 49ajc.util.time_stop()
 50
 51ajc.util.time_start(msg = 'PersonNoTypedWithIndex org Query')
 52print len([peep for peep in PersonNoTypedWithIndex.age < 25])
 53ajc.util.time_stop()
 54
 55ajc.util.time_start(msg = 'PersonTyped org query')
 56print len([peep for peep in PersonTyped.age < 25])
 57ajc.util.time_stop()
 58
 59ajc.util.time_start(msg = 'PersonTypedWithIndex org query')
 60print len([peep for peep in PersonTypedWithIndex.age < 25])
 61ajc.util.time_stop()
 62
 63ajc.util.time_start(msg = 'Fixed PersonNoTypedWithIndex query')
 64print len([row for row in PersonNoTypedWithIndex.execute('SELECT fid FROM cls_table_kvdict WHERE key="age" AND num > -1e100 AND num < 25')])
 65ajc.util.time_stop()
 66
 67ajc.util.time_start(msg = 'Try equals PersonNoTypedWithIndex query')
 68print len([row for row in PersonNoTypedWithIndex.execute('SELECT fid FROM cls_table_kvdict WHERE key="age" AND num = 1')])
 69ajc.util.time_stop()
 70
 71
 72
 73
 74
 75ajc.util.time_start(msg = 'PersonTyped str query')
 76print len([row for row in PersonTyped.execute('SELECT id FROM cls_table WHERE name="Stanley25"')])
 77ajc.util.time_stop()
 78
 79ajc.util.time_start(msg = 'PersonTypedWithIndex str query')
 80print len([row for row in PersonTypedWithIndex.execute('SELECT id FROM cls_table WHERE name="Stanley25"')])
 81ajc.util.time_stop()
 82
 83ajc.util.time_start(msg = 'PersonTypedWithIndex LIKE str query')
 84print len([row for row in PersonTypedWithIndex.execute('SELECT id FROM cls_table WHERE name LIKE "%ley25"')])
 85ajc.util.time_stop()
 86
 87ajc.util.time_start(msg = 'PersonTypedWithIndex substr query')
 88print len([row for row in PersonTypedWithIndex.execute('SELECT id FROM cls_table WHERE substr(name,-4) == "ey25"')])
 89ajc.util.time_stop()
 90
 91ajc.util.time_start(msg = 'PersonTypedWithIndex double substr query')
 92print len([row for row in PersonTypedWithIndex.execute('SELECT id FROM cls_table WHERE substr(name,1,1) == "S" AND substr(name,-4) == "ey25"')])
 93ajc.util.time_stop()
 94
 95
 96
 97
 98
 99
100
101ajc.util.time_start(msg = 'PersonNoTyped str query')
102print len([row for row in PersonNoTyped.execute('SELECT fid FROM cls_table_kvdict WHERE key="name" AND str="Stanley25"')])
103ajc.util.time_stop()
104
105ajc.util.time_start(msg = 'PersonNoTypedWithIndex str query')
106print len([row for row in PersonNoTypedWithIndex.execute('SELECT fid FROM cls_table_kvdict WHERE key="name" AND str="Stanley25"')])
107ajc.util.time_stop()
108
109ajc.util.time_start(msg = 'PersonNoTypedWithIndex LIKE str query')
110print len([row for row in PersonNoTypedWithIndex.execute('SELECT fid FROM cls_table_kvdict WHERE key="name" AND str LIKE "%ley25"')])
111ajc.util.time_stop()
112
113ajc.util.time_start(msg = 'PersonNoTypedWithIndex substr query')
114print len([row for row in PersonNoTypedWithIndex.execute('SELECT fid FROM cls_table_kvdict WHERE key="name" AND substr(str,-4) == "ey25"')])
115ajc.util.time_stop()
116
117ajc.util.time_start(msg = 'PersonNoTypedWithIndex double substr query')
118print len([row for row in PersonNoTypedWithIndex.execute('SELECT fid FROM cls_table_kvdict WHERE key="name" AND substr(str,1,1) == "S" AND substr(str,-4) == "ey25"')])
119ajc.util.time_stop()
120
121
122
123
124
125if False:
126    for peep in PersonTypedWithIndex:
127        peep.age = random.randrange(1,100000)
128    
129    for peep in PersonNoTypedWithIndex:
130        peep.age = random.randrange(1,100000)
131        
132    db.commit()
133
134if False:
135    for peep in PersonNoTypedWithIndex:
136        peep.weight = None
137    db.commit()