from JellyDB.db import Database from JellyDB.query import Query from random import choice, randint, sample, seed db = Database() db.open('~/ECS165') # Student Id and 4 grades grades_table = db.create_table('Grades', 5, 0) query = Query(grades_table) records = {} seed(3562901) for i in range(0, 1000): key = 92106429 + i records[key] = [ key, randint(0, 20), randint(0, 20), randint(0, 20), randint(0, 20) ] query.insert(*records[key]) keys = sorted(list(records.keys())) print("Insert finished") for key in keys: record = query.select(key, 0, [1, 1, 1, 1, 1])[0] error = False for i, column in enumerate(record.columns): if column != records[key][i]:
def performance_m2(size_to_test): db = Database() db.open('~/ECS165') # Student Id and 4 grades grades_table = db.create_table('Grades', 5, 0) query = Query(grades_table) records = {} seed(3562901) for i in range(0, size_to_test): key = 92106429 + i records[key] = [ key, randint(0, 20), randint(0, 20), randint(0, 20), randint(0, 20) ] query.insert(*records[key]) keys = sorted(list(records.keys())) print("Insert finished") time_select_1_s = process_time() for key in keys: record = query.select(key, 0, [1, 1, 1, 1, 1])[0] error = False for i, column in enumerate(record.columns): if column != records[key][i]: error = True if error: print('select error on', key, ':', record, ', correct:', records[key]) # else: # print('select on', key, ':', record) time_select_1_f = process_time() print("Select finished") time_update_s = process_time() for _ in range(10): for key in keys: updated_columns = [None, None, None, None, None] for i in range(1, grades_table.num_columns): value = randint(0, 20) updated_columns[i] = value original = records[key].copy() records[key][i] = value query.update(key, *updated_columns) record = query.select(key, 0, [1, 1, 1, 1, 1])[0] error = False for j, column in enumerate(record.columns): if column != records[key][j]: error = True if error: print('update error on', original, 'and', updated_columns, ':', record, ', correct:', records[key]) #else: #print('update on', original, 'and', updated_columns, ':', record) updated_columns[i] = None time_update_f = process_time() print("Update finished") for i in range(0, 100): r = sorted(sample(range(0, len(keys)), 2)) column_sum = sum(map(lambda key: records[key][0], keys[r[0]:r[1] + 1])) result = query.sum(keys[r[0]], keys[r[1]], 0) if column_sum != result: print('sum error on [', keys[r[0]], ',', keys[r[1]], ']: ', result, ', correct: ', column_sum) # else: # print('sum on [', keys[r[0]], ',', keys[r[1]], ']: ', column_sum) print("Aggregate finished") db.close() # Student Id and 4 grades db = Database() db.open('~/ECS165') grades_table = db.get_table('Grades') query = Query(grades_table) # repopulate with random data records = {} seed(3562901) for i in range(0, size_to_test): key = 92106429 + i records[key] = [ key, randint(0, 20), randint(0, 20), randint(0, 20), randint(0, 20) ] keys = sorted(list(records.keys())) for _ in range(10): for key in keys: for j in range(1, grades_table.num_columns): value = randint(0, 20) records[key][j] = value keys = sorted(list(records.keys())) for key in keys: print(records[key]) print(records[key]) time_select_2_s = process_time() for key in keys: record = query.select(key, 0, [1, 1, 1, 1, 1])[0] error = False for i, column in enumerate(record.columns): if column != records[key][i]: error = True if error: print('select error on', key, ':', record, ', correct:', records[key]) time_select_2_f = process_time() print("Select finished") deleted_keys = sample(keys, 100) for key in deleted_keys: query.delete(key) records.pop(key, None) for i in range(0, 100): r = sorted(sample(range(0, len(keys)), 2)) column_sum = sum( map(lambda x: records[x][0] if x in records else 0, keys[r[0]:r[1] + 1])) result = query.sum(keys[r[0]], keys[r[1]], 0) if column_sum != result: print('sum error on [', keys[r[0]], ',', keys[r[1]], ']: ', result, ', correct: ', column_sum) print("Aggregate finished") db.close() return [ '8192', time_update_f - time_update_s, time_select_1_f - time_select_1_s, time_select_2_f - time_select_2_s ]
result = query.sum(i, 100, randrange(0, 5)) agg_time_1 = process_time() print("Aggregate 10k of 100 record batch took:\t", agg_time_1 - agg_time_0) # Measuring Delete Performance delete_time_0 = process_time() for i in range(0, 10000): query.delete(906659671 + i) delete_time_1 = process_time() print("Deleting 10k records took: \t\t\t", delete_time_1 - delete_time_0) if __name__ == "__main__": # Create favorite numbers table + original correctness tests db = Database() db.open("~/ECS165") fav_numbers = db.create_table('fav_numbers', 3, 0) correctness_testing(fav_numbers) # Close database db.close() # Reopen database and check data is still there db.open("~/ECS165") fav_numbers = db.get_table('fav_numbers') correctness_testing_after_close(fav_numbers) # performance_testing() print("\n\n")
def performance_testing(): print("\n====Performance testing:====\n") # Student Id and 4 grades # Grades table has 5 columns, key is first column db = Database() grades_table = db.create_table('Grades', 5, 0) assert grades_table._name == "Grades" assert grades_table._num_columns == 5 assert grades_table._key == 0 print("Create table good") return ################################################## query = Query(grades_table) keys = [] # Measuring Insert Performance insert_time_0 = process_time() for i in range(0, 10000): query.insert(906659671 + i, 93, 0, 0, 0) keys.append(906659671 + i) insert_time_1 = process_time() print("Inserting 10k records took: \t\t\t", insert_time_1 - insert_time_0) # Measuring update Performance update_cols = [ [randrange(0, 100), None, None, None, None], [None, randrange(0, 100), None, None, None], [None, None, randrange(0, 100), None, None], [None, None, None, randrange(0, 100), None], [None, None, None, None, randrange(0, 100)], ] update_time_0 = process_time() for i in range(0, 10000): query.update(choice(keys), *(choice(update_cols))) update_time_1 = process_time() print("Updating 10k records took: \t\t\t", update_time_1 - update_time_0) # Measuring Select Performance select_time_0 = process_time() for i in range(0, 10000): query.select(choice(keys), [1, 1, 1, 1, 1]) select_time_1 = process_time() print("Selecting 10k records took: \t\t\t", select_time_1 - select_time_0) # Measuring Aggregate Performance agg_time_0 = process_time() for i in range(0, 10000, 100): result = query.sum(i, 100, randrange(0, 5)) agg_time_1 = process_time() print("Aggregate 10k of 100 record batch took:\t", agg_time_1 - agg_time_0) # Measuring Delete Performance delete_time_0 = process_time() for i in range(0, 10000): query.delete(906659671 + i) delete_time_1 = process_time() print("Deleting 10k records took: \t\t\t", delete_time_1 - delete_time_0)
def performance_testing(range_to_test): db = Database() grades_table = db.create_table('Grades', 5, 0) query = Query(grades_table) records = {} tests_passed = 0 tests_failed = 0 insert_time_0 = process_time() for i in range(0, range_to_test): key = 92106429 + randint(0, 9000) while key in records: key = 92106429 + randint(0, 9000) records[key] = [ key, randint(0, 20), randint(0, 20), randint(0, 20), randint(0, 20) ] query.insert(*records[key]) insert_time_1 = process_time() time_insert = insert_time_1 - insert_time_0 print('inserted', range_to_test, 'records took: \t\t\t', time_insert) select_time_0 = process_time() for key in records: record = query.select(key, [1, 1, 1, 1, 1])[0] error = False for i, column in enumerate(record.columns): if column != records[key][i]: error = True if error: print('select error on', key, ':', record, ', correct:', records[key]) select_time_1 = process_time() time_select = select_time_1 - select_time_0 print('select', range_to_test, 'records took: \t\t\t', time_select) update_time_0 = process_time() for key in records: updated_columns = [None, None, None, None, None] for i in range(1, grades_table.num_columns): value = randint(0, 20) updated_columns[i] = value original = records[key].copy() records[key][i] = value query.update(key, *updated_columns) record = query.select(key, [1, 1, 1, 1, 1])[0] error = False for j, column in enumerate(record.columns): if column != records[key][j]: error = True if error: print('update error on', original, 'and', updated_columns, ':', record, ', correct:', records[key]) updated_columns[i] = None update_time_1 = process_time() time_update = update_time_1 - update_time_0 print('updated', range_to_test, 'records took: \t\t\t', time_update) keys = sorted(list(records.keys())) agg_time_0 = process_time() for c in range(0, grades_table.num_columns): for i in range(0, 20): r = sorted(sample(range(0, len(keys)), 2)) column_sum = sum( map(lambda key: records[key][c], keys[r[0]:r[1] + 1])) result = query.sum(keys[r[0]], keys[r[1]], c) if column_sum != result: print('sum error on [', keys[r[0]], ',', keys[r[1]], ']: ', result, ', correct: ', column_sum) agg_time_1 = process_time() time_sum = agg_time_1 - agg_time_0 print('Sum', range_to_test, 'records took: \t\t\t', time_sum) delete_time_0 = process_time() for key in records: try: query.delete(key) s = query.select(key, [1, 1, 1]) assert s == None, "Expected None from attempt to select deleted record" tests_passed += 1 except Exception as exc: print("\nfav_numbers delete + select FAILED") print(traceback.format_exc()) tests_failed += 1 delete_time_1 = process_time() time_delete = delete_time_1 - delete_time_0 print('Deletion', range_to_test, 'records took: \t\t\t', time_delete, 'true deletion completed:', tests_passed, 'deletion failed:', tests_failed) return time_insert, time_select, time_update, time_sum, time_delete