Re: [sqlite] Is there a best practice for breaking up a large update?

[Kevin O'Gorman]

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 Duncan 
wrote:

> 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?

[Darren Duncan]

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?

[Igor Tandetnik]

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