Re: [sqlite] Is there a best practice for breaking up a large update?
There are other writers, but they create new stuff, while this big update wants to consolidate info about existing stuff. There are also pure readers. Some of the docs on WAL logging had caveats that put me off. Not knowing exactly what I was going to be doing with the database, I could not be sure if the caveats applied to me or not. Particularly because I had some trouble understanding the caveats, since some of the terminology is a bit new to me. Not all of it -- I actually taught a database course at the undergrad level once, SQL-based, but it was quite a while ago. But most of my database experience was either following SQL recipes, And a long time ago I wrote a full database server from scratch (not relational -- heirarchical) in assembler. On Sat, Oct 15, 2016 at 6:14 PM, Darren Duncanwrote: > You didn't say if the other tasks need write access to the database or if > it is just read-only. If the others only need read-only, let them access a > copy of the database while you make your changes in another copy, then just > swap the databases when done. -- Darren Duncan > > > On 2016-10-15 1:21 PM, Kevin O'Gorman wrote: > >> I'm new to this, and working in Python's sqlite3. So be patient, and >> don't >> expect me to know too much. This is also a personal hobby, so there's >> nobody else for me to ask. >> >> I've got a database of a some tens of millions of positions in a board >> game. It may be over a billion before I'm done (working in an 11-TB >> partition at the moment.) I want to process a major slice of these >> records, >> in order to update other records. I might want to break the work up >> into chunks to allow other access to the database while this is going on. >> >> So I have some questions: >> (1) If I do all of my updates to a temporary table, does the database >> still >> get locked? >> >> (2) Is there another way to keep it available? It happens for this >> activity that consistency is not at risk. >> >> (3) If it turns out that I can avoid locking, it there still a performance >> reason to break the transaction into chunks, or would I be as well off >> doing it as a single transaction (assuming I have disk space for the >> journal). >> >> (4) If I break it up into chunks, I can think of several ways to do that >> and keep track of what's been done and what has not. Is there a best >> practice for this? >> >> (5) Is there a forum specifically for folks doing Python database >> programming? It occurs to me that much of what I'm asking about is not >> specific to SQLite. But my code, for now at least, is going to be in >> Python because it's the easiest way I can see, and my own efforts are the >> real bottleneck in making progress. >> >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- #define QUESTION ((bb) || (!bb)) /* Shakespeare */ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a best practice for breaking up a large update?
You didn't say if the other tasks need write access to the database or if it is just read-only. If the others only need read-only, let them access a copy of the database while you make your changes in another copy, then just swap the databases when done. -- Darren Duncan On 2016-10-15 1:21 PM, Kevin O'Gorman wrote: I'm new to this, and working in Python's sqlite3. So be patient, and don't expect me to know too much. This is also a personal hobby, so there's nobody else for me to ask. I've got a database of a some tens of millions of positions in a board game. It may be over a billion before I'm done (working in an 11-TB partition at the moment.) I want to process a major slice of these records, in order to update other records. I might want to break the work up into chunks to allow other access to the database while this is going on. So I have some questions: (1) If I do all of my updates to a temporary table, does the database still get locked? (2) Is there another way to keep it available? It happens for this activity that consistency is not at risk. (3) If it turns out that I can avoid locking, it there still a performance reason to break the transaction into chunks, or would I be as well off doing it as a single transaction (assuming I have disk space for the journal). (4) If I break it up into chunks, I can think of several ways to do that and keep track of what's been done and what has not. Is there a best practice for this? (5) Is there a forum specifically for folks doing Python database programming? It occurs to me that much of what I'm asking about is not specific to SQLite. But my code, for now at least, is going to be in Python because it's the easiest way I can see, and my own efforts are the real bottleneck in making progress. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a best practice for breaking up a large update?
On 10/15/2016 4:21 PM, Kevin O'Gorman wrote: So I have some questions: (1) If I do all of my updates to a temporary table, does the database still get locked? Locked against what? What else are you trying to do with the database while the update is in progress? Look at WAL journal mode: https://www.sqlite.org/wal.html . It allows a writing transaction to co-exist with multiple readers. See if this helps. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there a best practice for breaking up a large update?
I'm new to this, and working in Python's sqlite3. So be patient, and don't expect me to know too much. This is also a personal hobby, so there's nobody else for me to ask. I've got a database of a some tens of millions of positions in a board game. It may be over a billion before I'm done (working in an 11-TB partition at the moment.) I want to process a major slice of these records, in order to update other records. I might want to break the work up into chunks to allow other access to the database while this is going on. So I have some questions: (1) If I do all of my updates to a temporary table, does the database still get locked? (2) Is there another way to keep it available? It happens for this activity that consistency is not at risk. (3) If it turns out that I can avoid locking, it there still a performance reason to break the transaction into chunks, or would I be as well off doing it as a single transaction (assuming I have disk space for the journal). (4) If I break it up into chunks, I can think of several ways to do that and keep track of what's been done and what has not. Is there a best practice for this? (5) Is there a forum specifically for folks doing Python database programming? It occurs to me that much of what I'm asking about is not specific to SQLite. But my code, for now at least, is going to be in Python because it's the easiest way I can see, and my own efforts are the real bottleneck in making progress. -- #define QUESTION ((bb) || (!bb)) /* Shakespeare */ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users