4 ms·

Why would I do a hundred thousand insertions in a single transaction in a crud app?

cztomsik 6y ago
because bulk kinda implies that you want all-or-nothing :)
dwohnitmok 6y ago
I think parent means that for some careful selection of N (where N > 1) insertions per bulk transaction you can scale up to hundreds of thousands of insertions per second, rather than putting hundreds of thousands of insertions in a single transaction.
- Tuna-Fish 6y ago
  No, the opposite. In SQLite, starting and ending transactions that write things to the db is a relatively expensive operation, and running queries outside transaction is effectively the same as running each of them in an independent transaction. If you need to do a lot of inserts (or updates, etc), the slowest possible way to do them is to do them outside of a transaction. The fastest way to do them is to wrap them all into a single transaction.
  - dwohnitmok 6y ago
    Oh fascinating, you actually put hundreds of thousands of statements in a single SQLite transaction in an online CRUD app (as opposed to offline processing)? I've never done more than a couple hundred and even then usually they're "logically batched," both because I'm worried about forcing unnecessary read to write transaction promotions for concurrent reads and thereby increasing busy errors, but also because that affects durability to have a transaction open that long (it's not great to let your HTTP response hang for a second before responding as you keep your transaction open). For serialized writers in any system I'm sure keeping a transaction open as long as possible is the ideal case for throughput, but there's other problems with that in a CRUD app no?
  - deleted 6y ago
    [deleted]
  - quietbritishjim 6y ago
    > If you need to do a lot of inserts (or updates, etc), the slowest possible way to do them is to do them outside of a transaction. The fastest way to do them is to wrap them all into a single transaction. This doesn't seem to contradict the comment you're replying to. They're suggesting wrapping operations into transactions in batches e.g. (just making some numbers up) if you have 100,000 inserts maybe you'd do 100 transactions of 1000 inserts each. I wouldn't call that "the opposite" of your one mega-transaction suggestion. In fact I'd expect it to still have most or all of the speed benefit of using one single transaction, or potentially even be slightly faster.
kenniskrag 6y ago
db seeding with dummy data, migration, analytics db sync etc.
- fctorial 6y ago
  These are all one time/one a year operations.
  - imtringued 6y ago
    You still don't want to wait 5 hours to let your inefficient query complete.
danielbarla 6y ago
Well, that was my original question to the the parent poster, too. You can achieve 100k+ /sec, but they went looking for better performance, apparently. But to answer your question at face value, one common use for SQLite is as a file format for complex desktop apps, or perhaps a game save format for certain types of games (mostly the non-realtime types). One great advantage to this is that if you use a DB migration library, you ensure backwards compatibility with previous versions of your saved files. However, it's easy to imagine a fair bit of data getting inserted into such a new file each time it is created. It might only be 5000 rows, but I'd prefer to not have to wait 5 seconds just to persist it, if a single change can make it 0.05 seconds.

because bulk kinda implies that you want all-or-nothing :)

I think parent means that for some careful selection of N (where N > 1) insertions per bulk transaction you can scale up to hundreds of thousands of insertions per second, rather than putting hundreds of thousands of insertions in a single transaction.

Tuna-Fish 6y ago
No, the opposite. In SQLite, starting and ending transactions that write things to the db is a relatively expensive operation, and running queries outside transaction is effectively the same as running each of them in an independent transaction. If you need to do a lot of inserts (or updates, etc), the slowest possible way to do them is to do them outside of a transaction. The fastest way to do them is to wrap them all into a single transaction.
- dwohnitmok 6y ago
  Oh fascinating, you actually put hundreds of thousands of statements in a single SQLite transaction in an online CRUD app (as opposed to offline processing)? I've never done more than a couple hundred and even then usually they're "logically batched," both because I'm worried about forcing unnecessary read to write transaction promotions for concurrent reads and thereby increasing busy errors, but also because that affects durability to have a transaction open that long (it's not great to let your HTTP response hang for a second before responding as you keep your transaction open). For serialized writers in any system I'm sure keeping a transaction open as long as possible is the ideal case for throughput, but there's other problems with that in a CRUD app no?
- deleted 6y ago
  [deleted]
- quietbritishjim 6y ago
  > If you need to do a lot of inserts (or updates, etc), the slowest possible way to do them is to do them outside of a transaction. The fastest way to do them is to wrap them all into a single transaction. This doesn't seem to contradict the comment you're replying to. They're suggesting wrapping operations into transactions in batches e.g. (just making some numbers up) if you have 100,000 inserts maybe you'd do 100 transactions of 1000 inserts each. I wouldn't call that "the opposite" of your one mega-transaction suggestion. In fact I'd expect it to still have most or all of the speed benefit of using one single transaction, or potentially even be slightly faster.

kenniskrag 6y ago

db seeding with dummy data, migration, analytics db sync etc.

fctorial 6y ago
These are all one time/one a year operations.
- imtringued 6y ago
  You still don't want to wait 5 hours to let your inefficient query complete.

danielbarla 6y ago

Well, that was my original question to the the parent poster, too. You can achieve 100k+ /sec, but they went looking for better performance, apparently. But to answer your question at face value, one common use for SQLite is as a file format for complex desktop apps, or perhaps a game save format for certain types of games (mostly the non-realtime types). One great advantage to this is that if you use a DB migration library, you ensure backwards compatibility with previous versions of your saved files. However, it's easy to imagine a fair bit of data getting inserted into such a new file each time it is created. It might only be 5000 rows, but I'd prefer to not have to wait 5 seconds just to persist it, if a single change can make it 0.05 seconds.