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 ]
[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(): 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 correctness_testing_after_close(fav_numbers): print("\n====Correctness testing after close:====\n") tests_passed = 0 tests_failed = 0 try: assert fav_numbers._name == "fav_numbers" assert fav_numbers._num_columns == 3 + Config.METADATA_COLUMN_COUNT assert fav_numbers._key == 0 print("fav_numbers table attributes after closed passed") tests_passed += 1 except Exception as exc: print("fav_numbers table attributes after closed FAILED") print(traceback.format_exc()) tests_failed += 1 query = Query(fav_numbers) try: # Try selecting 2 keys from above. # Make sure each returns only 1 record and that record is correct. # Note 25 was deleted in previous block for testkey in [5, 99]: # Get all columns s = get_columns(query.select(testkey, 0, [1, 1, 1])) # Make sure result is right length # At this point select should return a record (not a list of records) # So this is checking how many columns are in that record assert len(s) == 3 # Make sure primary key matches assert s[0] == testkey print("\nfav_numbers primary key select after closed passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers primary key select after closed FAILED") print(traceback.format_exc()) tests_failed += 1 try: query.delete(99) print("\nfav_numbers delete after closed passed") tests_passed += 1 except: print("\nfav_numbers delete after closed FAILED") print(traceback.format_exc()) tests_failed += 1 try: query.delete(5) print("\nfav_numbers delete updated record after closed passed") tests_passed += 1 except: print("\nfav_numbers delete updated record after closed FAILED") print(traceback.format_exc()) tests_failed += 1 if tests_failed == 0: print("\nAll {} tests passed!!! :)".format(str(tests_passed))) else: print("\nTests passed:", tests_passed) print("Tests failed:", tests_failed)
def correctness_testing(fav_numbers): print("\n====Correctness testing:====\n") tests_passed = 0 tests_failed = 0 try: assert fav_numbers._name == "fav_numbers" assert fav_numbers._num_columns == 3 + Config.METADATA_COLUMN_COUNT assert fav_numbers._key == 0 print("fav_numbers create table passed") tests_passed += 1 except Exception as exc: print("fav_numbers create table FAILED") print(traceback.format_exc()) tests_failed += 1 query = Query(fav_numbers) nums1 = [5, 6, 7] nums2 = [25, 26, 27] nums3 = [99, 98, 97] query.insert(*nums1) query.insert(*nums2) query.insert(*nums3) try: # Try selecting 3 keys from above. # Make sure each returns only 1 record and that record is correct. for testkey in [5, 25, 99]: # Get all columns s = get_columns(query.select(testkey, 0, [1, 1, 1])) # Make sure result is right length # At this point select should return a record (not a list of records) # So this is checking how many columns are in that record assert len(s) == 3 # Make sure primary key matches assert s[0] == testkey print("\nfav_numbers primary key select passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers primary key select FAILED") print(traceback.format_exc()) tests_failed += 1 query.update(5, *(5, 12, 20)) try: # Try selecting record just updated s = get_columns(query.select(5, 0, [1, 1, 1])) assert s == [5, 12, 20] print("\nfav_numbers update + primary key select passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers update + primary key select FAILED") print(traceback.format_exc()) tests_failed += 1 try: query.table.create_index(1) query.table.create_index(2) # Try selecting record with 26 in column 1 (non-primary key) s = get_columns(query.select(26, 1, [1, 1, 1])) assert s == [25, 26, 27] # Insert a new record with 26 in column 1 query.insert(100, 26, 32) # Try selecting 26 in column 1 again, should get 2 records now s = query.select(26, 1, [1, 1, 1]) assert len(s) == 2, "Expected 2 records, instead found {}".format(len(s)) for record in s: assert record.columns[1] == 26, "Expected 26 in column 1. Record found was {}".format(record) print("\nfav_numbers non-primary key select passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers non-primary key select FAILED") print(traceback.format_exc()) tests_failed += 1 try: # Drop indexes and assert they become None query.table.drop_index(1) query.table.drop_index(2) assert query.table._indices.data[4] == None assert query.table._indices.data[5] == None # Create indexes again to not break later tests query.table.create_index(1) query.table.create_index(2) print("\nfav_numbers drop_index passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers drop_index FAILED") print(traceback.format_exc()) tests_failed += 1 try: s = get_columns(query.select(5, 0, [1, 0, 0])) assert s == [5, None, None] s = get_columns(query.select(5, 0, [0, 1, 0])) assert s == [None, 12, None] s = get_columns(query.select(5, 0, [0, 0, 1])) assert s == [None, None, 20] print("\nfav_numbers select certain columns only passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers select certain columns only FAILED") print(traceback.format_exc()) tests_failed += 1 try: sum = query.sum(5, 99, 0) assert sum == 129, "Sum all entries in column 0, expected 129, found {}".format(str(sum)) sum = query.sum(5, 99, 1) assert sum == 136, "Sum all entries in column 1, expected 136, found {}".format(str(sum)) sum = query.sum(5, 6, 1) assert sum == 12, "Sum first entry in column 1, expected 12, found {}".format(str(sum)) sum = query.sum(25, 99, 2) assert sum == 124, "Sum last two entries in column 2, expected 124, found {}".format(str(sum)) print("\nfav_numbers sum passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers sum FAILED") print(traceback.format_exc()) tests_failed += 1 try: query.delete(25) s = query.select(25, 0, [1, 1, 1]) assert s == None, "Expected None from attempt to select deleted record" print("\nfav_numbers delete + select passed") tests_passed += 1 except Exception as exc: print("\nfav_numbers delete + select FAILED") print(traceback.format_exc()) tests_failed += 1 if tests_failed == 0: print("\nAll {} tests passed!!! :)".format(str(tests_passed))) else: print("\nTests passed:", tests_passed) print("Tests failed:", tests_failed)
print(records[key]) print(records[key]) 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]) 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()
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