21–30 of 164 posts
Creator of RediSQL / zeeSQL ( https://zeesql.com/ ) Insertion performance on a single table are very very hard to optimize. A single process looping it is your best bet. I would just increase the batch size, which is the most influent factor. Then another point... When you do batches, you do BEGIN TRANSACTION; for i in range(1, 50): execute_stmt COMMIT; You do not create a long list of parameters. https://github.com/…
Earlier quoted context omitted.
Are you suggesting have a template and then updating rows with random values where necessary?
You just do raw byte copies from sample DB, no SQL or "inserts" or anything similar. Imagine test database consists of 3 parts (all raw bytes) ### PROLOGUE ### Sample row ### EPILOGUE You copy & write prologue, write 1B sample raws (can optimize this at will, large writes, etc) Copy & write epilogue and fsync the data. You probably need to modify some metadata, but that should be a few writes at most. That should be…
I wonder if defining a virtual table ( https://www.sqlite.org/vtab.html ) and just doing INSERT INTO t (...) SELECT ... from virtual_table would be any faster.
I wonder if you could insert into 10 different tables from 10 threads or processes then
insert into single_table (…) select (…) union.
No idea if insert select does the trick or not but you’re almost at the point of partitioning here. If the application called for it you could do some sort of poor mans partition in order to write all the data and then push some complexity into the read.
I also wondered what if any impact the ID primary key had on inserting at the level of frequency.
/armchair
If it's single, in memory table, is there really need to use database? Won't language provided data structures suffice?
Yes, if you want to run non-trivial queries on that data. Although, frankly, SQLite would not be my choice.
Whatever queries that might be a hashmap/tree/skiplist, etc. would be a lot better.
Creator of RediSQL / zeeSQL ( https://zeesql.com/ ) Insertion performance on a single table are very very hard to optimize. A single process looping it is your best bet. I would just increase the batch size, which is the most influent factor. Then another point... When you do batches, you do BEGIN TRANSACTION; for i in range(1, 50): execute_stmt COMMIT; You do not create a long list of parameters. https://github.com/…
Even though merging strings seems faster...
Earlier quoted context omitted.
Yes, if you want to run non-trivial queries on that data. Although, frankly, SQLite would not be my choice.
I can't think of a single case where in-memory database is a good option, aside playing w/ SQL. Whatever queries that might be a hashmap/tree/skiplist, etc. would be a lot better.
I thought one billion was 1000M (and not 100M)?
Earlier quoted context omitted.
Yes, if you want to run non-trivial queries on that data. Although, frankly, SQLite would not be my choice.
I can't think of a single case where in-memory database is a good option, aside playing w/ SQL. Whatever queries that might be a hashmap/tree/skiplist, etc. would be a lot better.