# Testing Scalability of 'Assets' and 'Issuances' Tables **URL:** \ **Category:** Counterparty\ **Created:** [June 2, 2023, 2:04pm UTC](https://forums.counterparty.io/t/testing-scalability-of-assets-and-issuances-tables/6621 "2023-06-02T14:04:06Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![JPJA](https://yyz2.discourse-cdn.com/flex004/user_avatar/forums.counterparty.io/jpja/32/1324_2.png) [@JPJA](https://forums.counterparty.io/u/JPJA)\ **Post date:** [June 2, 2023, 2:04pm UTC](https://forums.counterparty.io/t/testing-scalability-of-assets-and-issuances-tables/6621/1 "2023-06-02T14:04:07Z")
I’ve simulated inserting rows into the `assets` and `issuances` tables and measured the performance. The initial DB is a real, recent Counterparty DB. The added rows are random values of the same format as real values. **Assets** Milliseconds to look up a name: ![image](https://canada1.discourse-cdn.com/flex004/uploads/counterpartytalk/original/2X/7/7a9c73bbc07579be2460ac522eca55084fa4fb8a.png) _The performance is worse than O(n)_😱 **Issuances** Counting the number of rows for given names. ![image](https://canada1.discourse-cdn.com/flex004/uploads/counterpartytalk/original/2X/9/96ce155e33fcf21a1feab6478a61aaa5a0a9a271.png) _Even worse…_ * * * Python scripts used for testing. **Assets:** ```auto db_file = 'db_copy.db' #latest Counterparty DB import os dir_path = os.path.dirname(os.path.realpath( __file__ )) os.chdir(dir_path) import sqlite3 con = sqlite3.connect(db_file) cur = con.cursor() import random import datetime assets = ['FEELGOODMAN', 'JPJA', 'WOLVERINE', 'LTBCOIN'] for row in cur.execute('SELECT COUNT(*) FROM assets;'): row_count = row[0] i = 0 while (True): asset_id = str(random.randint(1e14, 9e14)) asset = 'A' + asset_id block = str(random.randint(1e6, 9e6)) cur.execute('INSERT INTO assets VALUES("'+asset_id+'", "'+asset+'", "'+block+'", NULL);') if i == 0 or i == 1e5 or i == 1e6: print('Table rows: ' + f'{row_count+i:,}' + ' (' + f'{i:,}' + ' rows added)') for asset in assets: t0 = datetime.datetime.now() for row in cur.execute('SELECT * FROM assets WHERE asset_name LIKE "'+asset+'";'): t1 = datetime.datetime.now() delta = t1- t0 print(row[1] + ' ' + str(int(delta.total_seconds() * 1000)) + 'ms') print() if i == 1e6: break i += 1 con.close() ``` **Issuances** ```auto db_file = 'db_copy.db' #latest Counterparty DB import os dir_path = os.path.dirname(os.path.realpath( __file__ )) os.chdir(dir_path) import sqlite3 con = sqlite3.connect(db_file) cur = con.cursor() import random import datetime assets = ['FEELGOODMAN', 'JPJA', 'WOLVERINE', 'LTBCOIN'] for row in cur.execute('SELECT COUNT(*) FROM issuances;'): row_count = row[0] i = 0 while (True): #Random dummy values tx_index = str(1e7+i) tx_hash = str(random.randint(1e64, 9e64)) block = str(random.randint(1e6, 9e6)) asset_id = str(random.randint(1e14, 9e14)) asset = 'A' + asset_id source = str(random.randint(1e34, 9e34)) descr = str(random.randint(1e15, 9e15)) cur.execute('INSERT INTO issuances VALUES("'+tx_index+'", "'+tx_hash+'", "0", "'+block+'", "'+asset+'", "500", "0", "'+source+'", "'+source+'", "0", "0", "0", "0.0", "'+descr+'", "1000000", "0", "valid", NULL, "0");') if i == 0 or i == 1e5 or i == 1e6: print('Table rows: ' + f'{row_count+i:,}' + ' (' + f'{i:,}' + ' rows added)') for asset in assets: t0 = datetime.datetime.now() for row in cur.execute('SELECT COUNT(*) FROM issuances WHERE asset LIKE "'+asset+'";'): t1 = datetime.datetime.now() delta = t1- t0 print(asset + ' ' + str(row[0]) + ' ' + str(int(delta.total_seconds() * 1000)) + 'ms') print() if i == 1e6: break i += 1 con.close() ``` ---
**Author:** ![JPJA](https://yyz2.discourse-cdn.com/flex004/user_avatar/forums.counterparty.io/jpja/32/1324_2.png) [@JPJA](https://forums.counterparty.io/u/JPJA)\ **Post date:** [June 2, 2023, 3:14pm UTC](https://forums.counterparty.io/t/testing-scalability-of-assets-and-issuances-tables/6621/3 "2023-06-02T15:14:39Z")
Hmmm, discovered an embarrassing mistake BUT THIS IS GOOD NEWS. I used the `LIKE` operator intstead of `=`. The findings after this change are better. The time to look up an asset in the `assets` table is negligible – even after 1M records added `SELECT * FROM assets WHERE asset_name = 'X'` ![image](https://canada1.discourse-cdn.com/flex004/uploads/counterpartytalk/original/2X/4/40119ca2203eae58f3297a804562d59705618df8.png) On the `issuances` table` the increase is dramatic. Increasing the rows from \<200k to 1.2M (6x) makes the query ~13x slower (from 15ms to 190ms). Worse than O(n) performance! `SELECT COUNT(*) FROM issuances WHERE asset = 'X'` ![image](https://canada1.discourse-cdn.com/flex004/uploads/counterpartytalk/original/2X/f/f0824001a3f8b8de96d6ed287a3e37df85a74c5f.png) Of course, there may be more that I’ve overlooked. Other, more relevant, queries for example? ---
**Author:** ![jdogresorg](https://yyz2.discourse-cdn.com/flex004/user_avatar/forums.counterparty.io/jdogresorg/32/1349_2.png) [@jdogresorg](https://forums.counterparty.io/u/jdogresorg)\ **Post date:** [June 2, 2023, 4:05pm UTC](https://forums.counterparty.io/t/testing-scalability-of-assets-and-issuances-tables/6621/4 "2023-06-02T16:05:23Z")
Thank you for taking the time to write these tools and do this quick analysis. Performance can be further improved by : - Indexing every unique `address`, `asset`, and `tx_hash` into separate index tables - Updating all CP tables to reference `address/asset/x_hash` by index id (integer) - Creating indexes on all CP tables for `address/asset/tx_hash` id fields - Migrating from sqlite to Mysql/MariaDB - Migrating tables to MyISAM (faster reads) This is what I have done on counterparty2mysql/XChain, and get much better performance out of the database than the native sqlite cp database IMO the database growth and performance is a valid concern that needs monitoring and tweaking going forward.