Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Sat, Aug 6, 2016 at 2:49 PM, Kevin O'Gorman wrote: > On Sat, Aug 6, 2016 at 2:09 AM, Dan Kennedy wrote: > >> On 08/06/2016 09:52 AM, Kevin O'Gorman wrote: >> >>> On Fri, Aug 5, 2016 at 2:03 PM, Dan Kennedy >>> wrote: >>>

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Sat, Aug 6, 2016 at 2:09 AM, Dan Kennedy wrote: > On 08/06/2016 09:52 AM, Kevin O'Gorman wrote: > >> On Fri, Aug 5, 2016 at 2:03 PM, Dan Kennedy >> wrote: >> >> On 08/06/2016 03:28 AM, Kevin O'Gorman wrote: >>> >>> On Fri, Aug 5, 2016 at 1:08 PM,

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On 08/06/2016 09:52 AM, Kevin O'Gorman wrote: On Fri, Aug 5, 2016 at 2:03 PM, Dan Kennedy wrote: On 08/06/2016 03:28 AM, Kevin O'Gorman wrote: On Fri, Aug 5, 2016 at 1:08 PM, David Raymond wrote: .. Apart from the default location of

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Fri, Aug 5, 2016 at 2:03 PM, Dan Kennedy wrote: > On 08/06/2016 03:28 AM, Kevin O'Gorman wrote: > >> On Fri, Aug 5, 2016 at 1:08 PM, David Raymond >> wrote: >> >> .. >> >> Apart from the default location of the files, it reads like your

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Fri, Aug 5, 2016 at 3:03 PM, Darren Duncan wrote: > On 2016-08-04 7:27 AM, Jim Callahan wrote: > >> Steps >> Agree with Darren Duncan and Dr. Hipp you may want to have at least 3 >> separate steps >> (each step should be a separate transaction): >> >> 1. Simple load

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On 2016-08-04 7:27 AM, Jim Callahan wrote: Steps Agree with Darren Duncan and Dr. Hipp you may want to have at least 3 separate steps (each step should be a separate transaction): 1. Simple load 2. Create additional column 3. Create index Have you pre-defined the table you are loading data

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

list Subject: Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this. On Fri, Aug 5, 2016 at 12:30 PM, Igor Korot <ikoro...@gmail.com> wrote: Hi, Kevin, On Fri, Aug 5, 2016 at 3:18 PM, Kevin O'Gorman <kevinogorm...@gmail.com> wrote: Okay, I followed s

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

led poorly. > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Kevin O'Gorman > Sent: Friday, August 05, 2016 3:41 PM > To: SQLite mailing list > Subject: Re: [sqlite] newbie has waited days for a DB build to co

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

-boun...@mailinglists.sqlite.org] On Behalf Of Kevin O'Gorman Sent: Friday, August 05, 2016 3:41 PM To: SQLite mailing list Subject: Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this. On Fri, Aug 5, 2016 at 12:30 PM, Igor Korot <ikoro...@gmail.com> wrote:

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Fri, Aug 5, 2016 at 12:30 PM, Igor Korot wrote: > Hi, Kevin, > > On Fri, Aug 5, 2016 at 3:18 PM, Kevin O'Gorman > wrote: > > Okay, I followed some of the advice y'all gave and got some results. > > > > 1. The original problem was compromised by

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

Hi, Kevin, On Fri, Aug 5, 2016 at 3:18 PM, Kevin O'Gorman wrote: > Okay, I followed some of the advice y'all gave and got some results. > > 1. The original problem was compromised by malformed input. However, it > appears that did not cause the wedging of the process.

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

Okay, I followed some of the advice y'all gave and got some results. 1. The original problem was compromised by malformed input. However, it appears that did not cause the wedging of the process. See (3) below. 2. I separated the steps, and started small. Time increased slightly sub-linearly

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

The metric for feasability is coding ease, not runtime. I'm the bottleneck, not the machine, at least at this point. As for adding rows, it will be about like this time: a billion or so at a time. But there's no need to save the old data. Each round can be separate except for a persistent

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On 2016/08/04 5:56 PM, Kevin O'Gorman wrote: On Thu, Aug 4, 2016 at 8:29 AM, Dominique Devienne wrote: It's even less dense than that. Each character has only 3 possible values, and thus it's pretty easy to compress down to 2 bits each, for a 16 byte blob. It's just

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Thu, Aug 4, 2016 at 8:29 AM, Dominique Devienne wrote: > On Thu, Aug 4, 2016 at 5:05 PM, Kevin O'Gorman > wrote: > > > 3. Positions are 64 bytes always, so your size guesses are right. They > are > > in no particular order. I like the

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On 2016/08/04 5:05 PM, Kevin O'Gorman wrote: Lots of answers, so thanks all around. Some more info: 1. All partitions have at least 3 GB free, and it's not changing. /tmp is 3 TiB and empty. 2. I have a RAID partition, for size, but no RAID controller. As a hobby project, I don't have

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Thu, Aug 4, 2016 at 5:29 PM, Dominique Devienne wrote: > [...] you also force SQLite's SQL parser to parse a huge amount of text. > [...] > OK, maybe not the SQL parser, depends what you write out and the .import mode (I guess, didn't look into the details). But for sure

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On Thu, Aug 4, 2016 at 5:05 PM, Kevin O'Gorman wrote: > 3. Positions are 64 bytes always, so your size guesses are right. They are > in no particular order. I like the suggestion of a separate position > table, because they're going to appear in multiple qmove records,

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

Lots of answers, so thanks all around. Some more info: 1. All partitions have at least 3 GB free, and it's not changing. /tmp is 3 TiB and empty. 2. I have a RAID partition, for size, but no RAID controller. As a hobby project, I don't have spare parts, and I fear the results of a failure of a

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

Temp Files Have you checked how much storage is available to the temporary file locations? The temporary file locations are different depending on the OS, build, VFS and PRAGMA settings. See the last section "5.0 Temporary File Storage Locations" of: https://www.sqlite.org/tempfiles.html The

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On 8/4/16, Wade, William wrote: > > I believe that with SQLite, if you don't specify WITHOUT ROWID your "real" > record order is based on rowid, Correct > > In principle, indices can be created by writing the needed information > (index key, record position) in the original

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

lite-users Subject: [sqlite] newbie has waited days for a DB build to complete. what's up with this. I'm working on a hobby project, but the data has gotten a bit out of hand. I thought I'd put it in a real database rather than flat ASCII files. I've got a problem set of about 1 billion game positions

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

Hello Kevin, I'd write a utility to do it instead of using the command line tool then add logging to the program in order to note progress. I like the idea of chopping it into smaller parts too. "ON CONFLICT ROLLBACK" You're doing one large transaction and if it rolls back it'll have

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

On 4 Aug 2016, at 4:00am, Kevin O'Gorman wrote: > I've got a problem set of about 1 billion game positions and 187GB to work > on (no, I won't have to solve them all) that took about 4 hours for a > generator program just to write. I wrote code to turn them into

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

One way to get a clue is to try doing this in stages. First start over and import a much smaller amount of data, say just a 1GB fraction say, see if that completes, and if it does, how long it takes and other factors like disk and memory etc. If 1GB doesn't work, start smaller yet, until you

[sqlite] newbie has waited days for a DB build to complete. what's up with this.

I'm working on a hobby project, but the data has gotten a bit out of hand. I thought I'd put it in a real database rather than flat ASCII files. I've got a problem set of about 1 billion game positions and 187GB to work on (no, I won't have to solve them all) that took about 4 hours for a