4 ms·

Are you suggesting have a template and then updating rows with random values where necessary?

nezirus 5y ago
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 as good as it gets, providing your IO is optimal.
- eismcc 5y ago
  This idea is mentioned as one of the future work at the bottom.
- tyingq 5y ago
  Do you mean crafting all the various database page btree structures and entries yourself? I'd be concerned about subtle bugs.
  - deleted 5y ago
    [deleted]
  - ndepoel 5y ago
    An SQLite database is just a file. You can build the empty database with schema and base values ahead of time, save it to a file (or an in-memory byte buffer) and then every time you want to create a new database, you just copy that file. No need to do any expensive initialization queries that way. If raw high-speed throughput is needed, skipping that step can make a significant difference.
    - deleted 5y ago
      [deleted]
    - tyingq 5y ago
      Yes, that approach makes sense. I thought what I was replying to was suggesting writing b-tree pages themselves, outside of sqlite, for the new data.
      - emmelaich 5y ago
        I guess if you wanted the fastest creation you could make a custom backend format for sqlite and use that. Especially if query speed was not important.
- geofft 5y ago
  Assuming SQLite has any internal counters or indexes, let alone B-trees or anything fancy, then this approach won't work. It will work for a raw record format (CSV, JSON, Protobuf, an mmapped array, etc.), but the author wants to actually interact with a real SQLite database. Generating a billion rows in some non-SQLite format still leaves problem to converting that format by loading it into SQLite, which isn't really a reduction of the original problem.
bob1029 5y ago
Yes. Start from known-good database and then update or insert with needed deltas. You could even have more than you need in the template file and truncate/drop what is not relevant for the current context. Depending on the situation, it might be faster to start from a database containing all of the possible things.
- gunapologist99 5y ago
  This makes sense. Just do a copy or copy-on-write to an existing database file; you could even have the actual bytes of an empty (or pre-DDL'ed) sqlite file in memory in your app, rather than needing to do a disk copy.
A4ukYkq6ILl 5y ago
> Are you suggesting have a template and then updating rows with random values where necessary? > Are you suggesting have a template and then updating rows with random values where necessary?
A4ukYkq6ILl 5y ago
3PwjGO6X9Gwc

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 as good as it gets, providing your IO is optimal.

eismcc 5y ago
This idea is mentioned as one of the future work at the bottom.
tyingq 5y ago
Do you mean crafting all the various database page btree structures and entries yourself? I'd be concerned about subtle bugs.
- deleted 5y ago
  [deleted]
- ndepoel 5y ago
  An SQLite database is just a file. You can build the empty database with schema and base values ahead of time, save it to a file (or an in-memory byte buffer) and then every time you want to create a new database, you just copy that file. No need to do any expensive initialization queries that way. If raw high-speed throughput is needed, skipping that step can make a significant difference.
  - deleted 5y ago
    [deleted]
  - tyingq 5y ago
    Yes, that approach makes sense. I thought what I was replying to was suggesting writing b-tree pages themselves, outside of sqlite, for the new data.
    - emmelaich 5y ago
      I guess if you wanted the fastest creation you could make a custom backend format for sqlite and use that. Especially if query speed was not important.
geofft 5y ago
Assuming SQLite has any internal counters or indexes, let alone B-trees or anything fancy, then this approach won't work. It will work for a raw record format (CSV, JSON, Protobuf, an mmapped array, etc.), but the author wants to actually interact with a real SQLite database. Generating a billion rows in some non-SQLite format still leaves problem to converting that format by loading it into SQLite, which isn't really a reduction of the original problem.

bob1029 5y ago

Yes. Start from known-good database and then update or insert with needed deltas. You could even have more than you need in the template file and truncate/drop what is not relevant for the current context. Depending on the situation, it might be faster to start from a database containing all of the possible things.

gunapologist99 5y ago
This makes sense. Just do a copy or copy-on-write to an existing database file; you could even have the actual bytes of an empty (or pre-DDL'ed) sqlite file in memory in your app, rather than needing to do a disk copy.

A4ukYkq6ILl 5y ago

> Are you suggesting have a template and then updating rows with random values where necessary? > Are you suggesting have a template and then updating rows with random values where necessary?

A4ukYkq6ILl 5y ago

3PwjGO6X9Gwc