7 ms·

Out of curiosity, just how many rows were you trying to insert, for this to be a problem? My memory is a bit fuzzy, but on SQLite even standard INSERT statemen

Out of curiosity, just how many rows were you trying to insert, for this to be a problem? My memory is a bit fuzzy, but on SQLite even standard INSERT statements can scale to hundreds of thousands per second, if you do them in one transaction. Just curious about the scenario here.

prox 6y ago
A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app. Not sure how general use this is.
- danielbarla 6y ago
  Indeed, without this, performance would seem quite lacklustre. I believe it's very common in the SQLite community.
- hnlmorg 6y ago
  This is exactly how I accomplished high performance in sqlite too. I'm surprised more people don't use transactions in sqlite given transactions are a staple of using any enterprise RDBMS.
  - Aeolun 6y ago
    Eh? They exist, that doesn’t mean anyone actually uses them.
    - hnlmorg 6y ago
      My point is that using transactions should be drilled into people who work on databases and where consistency is a requirement because transactions turn multiple complex SQL requests into one atomic operation: http://db4beginners.com/blog/relationaldb-transaction/ http://db4beginners.com/blog/relationaldb-transaction/
      - Aeolun 6y ago
        Oh, I agree. It’s just that my recent experiences have included mostly people foreign to both transactions and foreign keys.
    - mikeyjk 6y ago
      I've never worked at a place that doesn't use them. What if the 3rd insert in a series fails and consequently writes the wrong thing down on the 4th with an update? That's just my experience so I guess it may be meaningless but I'm surprised to hear it may not be often used.
      - konha 6y ago
        > I've never worked at a place that doesn't use them. Consider yourself lucky then. I know of a place that doesn’t use transactions in a homegrown ERP solution, of all things.
        FpUser 6y ago
        I've seen all kinds of wonders during my life so this one is no surprise. If however someone is doing stupid things it is their problem. They're free to complain to themselves.
        mumblemumble 6y ago
        Tangentially - it may be their problem, but I hesitate to say it's their fault. I'm continually dismayed at how spotty and superficial education about how to use an RDBMS can be. Even in formal education on the subject.
        FpUser 6y ago
        It does not take PhD and rocket science for one to figure that sometimes operations must be bunched and executed with success/failure as a single unit. That would come as a business requirements. For curious person it would not take much to do some search on a subject and discover and read about transactions.
      - slaymaker1907 6y ago
        You hardly ever need transactions if you track validity explicitly in your schema. Suppose T1 has a 1-many relationship with T2. Declare in your assumptions that any rows in T2 with no corresponding valid row in T1 are not valid. Additionally, have an is_valid field on T1 so selecting all valid data from T2 is done with "select * from T2 inner join T1 on T2.t1id = T1.id where T1.is_valid". To insert data, insert a row into T1 first but initially have is_valid be false. Then insert all necessary data into T2. Finally, do an update and change the original row in T1 to have is_valid be true. For deletions to T1, just do an update and set is_valid to false. Thanks to the validity logic, this has the effect of also invalidating all T2 rows. Updates are trickier, but you can allow them to work without transactions by having two ids for for every table. The first id is the one we worked with before which is used for joins. The second id is used by applications to look for explicit records. Therefore, just never do any updates aside from the one setting is_valid to true (which is really storage logic and not application logic). Instead, just insert a new row into T1 whenever you want to update something in T1. The final update now just needs to flip the is_valid bit for the old row and the new row and will also need to verify that the old row is valid as well as any other rows the current update relies on (basically need to turn it into complex CAS). All of this is pretty messy, but it does let you have CRUD without any transaction support from your DB. Also, even if your DB has transactions, this scheme has the advantage of being lock-free so your application cannot deadlock. If you have many updates/deletes, you can do garbage collection either by allowing the GC to use a transaction or by changing adding in a check for insertions to T1 that verify the number of associated rows in T2 before setting is_valid. Unfortunately, while updates and inserts with GC can still be lock-free, they are not wait-free since an insert or update can fail. If you never do updates or GC though, this is actually wait-free and guarantees that every create, read, and delete operation will succeed in the absence of hardware/network failures. Still, this overhead probably isn't worth it unless you already need to track the history explicitly for auditing or something. At the company where we used this, we didn't have an is_valid row, we had valid_from and valid_to which were timestamps.
    - FpUser 6y ago
      Not using transaction is just very bad practice. If people are using wrong approach to solve the task they should not complain about results.
  - AtlasBarfed 6y ago
    Once you travel code boundaries (classes, functions, whatever) transaction management gets a bit hairy. The question "prove this program reliably closes the transaction I started" starts to become equivalent to "prove this program halts" Obviously they are useful tools and heavily used, but it's not like they are a zero-overhead feature.
    - deleted 6y ago
      [deleted]
    - smallnamespace 6y ago
      You don’t need to solve the halting program, you just need a way to construct programs that halt (or close the connection), which is way easier. Many languages have some sort of `finally` or `with` construct tailored for this use case. Remember, we’re code writers, not arbitrary discriminators.
    - Spivak 6y ago
      I mean I have to deal with this crap at $dayjob but I genuinely can't believe of the terrible code I see that borrows a resource (connection, transaction, file handle) and then only the happy path gives it back. I desperately wish that languages would make this a compile error if all code paths don't lead to the resource being freed. The only thing that should ever stop you from returning a resource is a malicious scheduler.
      - lixtra 6y ago
        > I desperately wish that languages would make this a compile error if all code paths don't lead to the resource being freed. You might want to take a look at rust.
        AtlasBarfed 6y ago
        I can see the obvious parallels between memory management lifetimes and transaction management, does rust have explicit features for extending lifetimes to resources besides memory?
  - starik36 6y ago
    I actually avoided transactions in MSSQL when I could because it escalates locks real quick. Which is a death knell for a busy system.
- derekp7 6y ago
  Another trick if you have multiple process (users) accessing the DB and you don't want to lock the DB for a long time, is to insert into a temp table (possibly with a transaction, although the time savings is not as dramatic with temp tables). Then copy the temp table to the main one (insert into ... from ...). The advantage is lets say you are reading in a bunch of items from something else, that will take a chunk of time more than just the DB time. So by going to a temp table you aren't locking the target table for anyone else while gathering the data. Then combine this with flushing the temp table every X rows or X seconds, and you have a number of efficient updates to the table without long lock times. Also have WAL mode on to get multi-user access going.
  - uh_uh 6y ago
    This surprises me as I thought SQLite locks are db-level, not table-level. Is this not the case?
    - derekp7 6y ago
      If you have wal-mode enabled then the automatic locks are table level. So one process can be updating one table and another one can work on a different one. Also, you only need to enable wal mode on the DB once (pragma journal_mode=wal), it "sticks" for each connection. In my application that uses SQLite (Snebu backup), as data comes in (as a TAR format stream) I have one process extracting the data and metadata, then serializing the metadata to another process that owns the DB connection. This process dumps the metadata to a temp table, then every 10 seconds "flushes" the metadata to the various tables that it needs to go to. This way I can easily have multiple backups going simultaneously, as each process spends a small amount of time (relatively) flushing the data to the permanent tables, and a greater part of the time compressing and writing backup data to the disk vault directory. I've been working with this for the past 8 years or so, and have picked up a few tricks on keeping as much as possible batched up in transactions, but also keeping the transaction times short relative to other operations. So far seems to work out fairly well. Note, that in addition to journal_mode=wal, you need to have a busy handler defined that infinitely retries transactions with a 250 ms delay between each retry. Edit: On further review of the docs, I'm not sure if wal mode enables table-level locking, it may be that when writing to a temp table, that temp tables are part of a separate schema (or are otherwise separate from the main DB) -- which makes sense, as temp tables are only visible to the process that owns them. So a temp table can be locked in a transaction, while the rest of the DB is writable.
- quietbritishjim 6y ago
  > A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app. That is exactly what the parent comment already said: > if you do them in one transaction The commands you stated are exactly how to do (multiple) things in a transaction.
fctorial 6y ago
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?

prox 6y ago

A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app. Not sure how general use this is.

danielbarla 6y ago
Indeed, without this, performance would seem quite lacklustre. I believe it's very common in the SQLite community.
hnlmorg 6y ago
This is exactly how I accomplished high performance in sqlite too. I'm surprised more people don't use transactions in sqlite given transactions are a staple of using any enterprise RDBMS.
- Aeolun 6y ago
  Eh? They exist, that doesn’t mean anyone actually uses them.
  - hnlmorg 6y ago
    My point is that using transactions should be drilled into people who work on databases and where consistency is a requirement because transactions turn multiple complex SQL requests into one atomic operation: http://db4beginners.com/blog/relationaldb-transaction/ http://db4beginners.com/blog/relationaldb-transaction/
    - Aeolun 6y ago
      Oh, I agree. It’s just that my recent experiences have included mostly people foreign to both transactions and foreign keys.
  - mikeyjk 6y ago
    I've never worked at a place that doesn't use them. What if the 3rd insert in a series fails and consequently writes the wrong thing down on the 4th with an update? That's just my experience so I guess it may be meaningless but I'm surprised to hear it may not be often used.
    - konha 6y ago
      > I've never worked at a place that doesn't use them. Consider yourself lucky then. I know of a place that doesn’t use transactions in a homegrown ERP solution, of all things.
      - FpUser 6y ago
        I've seen all kinds of wonders during my life so this one is no surprise. If however someone is doing stupid things it is their problem. They're free to complain to themselves.
        mumblemumble 6y ago
        Tangentially - it may be their problem, but I hesitate to say it's their fault. I'm continually dismayed at how spotty and superficial education about how to use an RDBMS can be. Even in formal education on the subject.
        FpUser 6y ago
        It does not take PhD and rocket science for one to figure that sometimes operations must be bunched and executed with success/failure as a single unit. That would come as a business requirements. For curious person it would not take much to do some search on a subject and discover and read about transactions.
    - slaymaker1907 6y ago
      You hardly ever need transactions if you track validity explicitly in your schema. Suppose T1 has a 1-many relationship with T2. Declare in your assumptions that any rows in T2 with no corresponding valid row in T1 are not valid. Additionally, have an is_valid field on T1 so selecting all valid data from T2 is done with "select * from T2 inner join T1 on T2.t1id = T1.id where T1.is_valid". To insert data, insert a row into T1 first but initially have is_valid be false. Then insert all necessary data into T2. Finally, do an update and change the original row in T1 to have is_valid be true. For deletions to T1, just do an update and set is_valid to false. Thanks to the validity logic, this has the effect of also invalidating all T2 rows. Updates are trickier, but you can allow them to work without transactions by having two ids for for every table. The first id is the one we worked with before which is used for joins. The second id is used by applications to look for explicit records. Therefore, just never do any updates aside from the one setting is_valid to true (which is really storage logic and not application logic). Instead, just insert a new row into T1 whenever you want to update something in T1. The final update now just needs to flip the is_valid bit for the old row and the new row and will also need to verify that the old row is valid as well as any other rows the current update relies on (basically need to turn it into complex CAS). All of this is pretty messy, but it does let you have CRUD without any transaction support from your DB. Also, even if your DB has transactions, this scheme has the advantage of being lock-free so your application cannot deadlock. If you have many updates/deletes, you can do garbage collection either by allowing the GC to use a transaction or by changing adding in a check for insertions to T1 that verify the number of associated rows in T2 before setting is_valid. Unfortunately, while updates and inserts with GC can still be lock-free, they are not wait-free since an insert or update can fail. If you never do updates or GC though, this is actually wait-free and guarantees that every create, read, and delete operation will succeed in the absence of hardware/network failures. Still, this overhead probably isn't worth it unless you already need to track the history explicitly for auditing or something. At the company where we used this, we didn't have an is_valid row, we had valid_from and valid_to which were timestamps.
  - FpUser 6y ago
    Not using transaction is just very bad practice. If people are using wrong approach to solve the task they should not complain about results.
- AtlasBarfed 6y ago
  Once you travel code boundaries (classes, functions, whatever) transaction management gets a bit hairy. The question "prove this program reliably closes the transaction I started" starts to become equivalent to "prove this program halts" Obviously they are useful tools and heavily used, but it's not like they are a zero-overhead feature.
  - deleted 6y ago
    [deleted]
  - smallnamespace 6y ago
    You don’t need to solve the halting program, you just need a way to construct programs that halt (or close the connection), which is way easier. Many languages have some sort of `finally` or `with` construct tailored for this use case. Remember, we’re code writers, not arbitrary discriminators.
  - Spivak 6y ago
    I mean I have to deal with this crap at $dayjob but I genuinely can't believe of the terrible code I see that borrows a resource (connection, transaction, file handle) and then only the happy path gives it back. I desperately wish that languages would make this a compile error if all code paths don't lead to the resource being freed. The only thing that should ever stop you from returning a resource is a malicious scheduler.
    - lixtra 6y ago
      > I desperately wish that languages would make this a compile error if all code paths don't lead to the resource being freed. You might want to take a look at rust.
      - AtlasBarfed 6y ago
        I can see the obvious parallels between memory management lifetimes and transaction management, does rust have explicit features for extending lifetimes to resources besides memory?
- starik36 6y ago
  I actually avoided transactions in MSSQL when I could because it escalates locks real quick. Which is a death knell for a busy system.
derekp7 6y ago
Another trick if you have multiple process (users) accessing the DB and you don't want to lock the DB for a long time, is to insert into a temp table (possibly with a transaction, although the time savings is not as dramatic with temp tables). Then copy the temp table to the main one (insert into ... from ...). The advantage is lets say you are reading in a bunch of items from something else, that will take a chunk of time more than just the DB time. So by going to a temp table you aren't locking the target table for anyone else while gathering the data. Then combine this with flushing the temp table every X rows or X seconds, and you have a number of efficient updates to the table without long lock times. Also have WAL mode on to get multi-user access going.
- uh_uh 6y ago
  This surprises me as I thought SQLite locks are db-level, not table-level. Is this not the case?
  - derekp7 6y ago
    If you have wal-mode enabled then the automatic locks are table level. So one process can be updating one table and another one can work on a different one. Also, you only need to enable wal mode on the DB once (pragma journal_mode=wal), it "sticks" for each connection. In my application that uses SQLite (Snebu backup), as data comes in (as a TAR format stream) I have one process extracting the data and metadata, then serializing the metadata to another process that owns the DB connection. This process dumps the metadata to a temp table, then every 10 seconds "flushes" the metadata to the various tables that it needs to go to. This way I can easily have multiple backups going simultaneously, as each process spends a small amount of time (relatively) flushing the data to the permanent tables, and a greater part of the time compressing and writing backup data to the disk vault directory. I've been working with this for the past 8 years or so, and have picked up a few tricks on keeping as much as possible batched up in transactions, but also keeping the transaction times short relative to other operations. So far seems to work out fairly well. Note, that in addition to journal_mode=wal, you need to have a busy handler defined that infinitely retries transactions with a 250 ms delay between each retry. Edit: On further review of the docs, I'm not sure if wal mode enables table-level locking, it may be that when writing to a temp table, that temp tables are part of a separate schema (or are otherwise separate from the main DB) -- which makes sense, as temp tables are only visible to the process that owns them. So a temp table can be locked in a transaction, while the rest of the DB is writable.
quietbritishjim 6y ago
> A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app. That is exactly what the parent comment already said: > if you do them in one transaction The commands you stated are exactly how to do (multiple) things in a transaction.

fctorial 6y ago

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?