Re: [sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Oliver Peters]

Hello,

[...]

deleting

 PRAGMA foreign_keys = ON;

led to a miracle: everything completed after 30 minutes.

Because of the enormous difference (~ 24h without finish compared to 30 minutes)
I can imagine that there are ways to otimize the speed with the use of FKs - but
that seems to be a question only the programmers can answer.

Oliver

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Oliver Peters]

Dan Kennedy  writes:

[...]

> >
> > Most INSERTS are done into the table Verteilerdaten (>10,000,000).
> > I think the time depends heavily on the activated FOREIGN KEYs - is
> > my assumption correct and is this a behaviour I only can avoid by not
> > switching this PRAGMA on?
 

[...]

> 
> If you insert a row into a table that has a foreign key constraint,
> it has to search for the corresponding key in the parent table. So
> if the parent table is also large (too large for the cache), those
> searches might be slowing you down significantly. So if you can get
> away with doing the inserts with foreign keys turned off, it is
> worth trying.

[...]

Now I'm pretty sure that mass INSERTs and FOREIGN KEYs are not a couple
belonging together (at least in sqlite) - after almost 24 hours I stopped the
whole thing. Next try will be without FKs. I'll post the results - for those
that are interested.

Oliver

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Dan Kennedy]

On 01/11/2011 03:00 PM, Oliver Peters wrote:
> Hello,
>
> I'm on WinXP and using sqlite 3.7.4 with the CLI. I try to insert ~ 10,100,000
> records into a schema with different tables (http://pastebin.com/cbsPHNEj). 
> The
> db file has already 1.9 GB when I start the INSERTs via
>
> sqlite3 -bail extra.db3<  inserts.sql
>
> The statements in inserts.sql look like
>
> PRAGMA foreign_keys = ON;
> ...
> PRAGMA foreign_keys = OFF;
>
> Most INSERTS are done into the table Verteilerdaten (>10,000,000). I think the
> time depends heavily on the activated FOREIGN KEYs - is my assumption correct
> and is this a behaviour I only can avoid by not switching this PRAGMA on?

It could be correct. Once a database gets large enough, the speed of
INSERT statements tends to be limited by seeking around the file to
read data. Particularly if your transactions are also large.

If you insert a row into a table that has a foreign key constraint,
it has to search for the corresponding key in the parent table. So
if the parent table is also large (too large for the cache), those
searches might be slowing you down significantly. So if you can get
away with doing the inserts with foreign keys turned off, it is
worth trying.

Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Oliver Peters]

Hello,

Simon Slavin  writes:
> 
>[...]
> 
> It that takes 11 hours, that means you're taking about 4ms per INSERT.  I
don't know if this is unusually high. 
> You might like to try 'PRAGMA synchronous = OFF'
> 
> 
> 
> > sqlite3 -bail extra.db3 < inserts.sql
> 
> I'm not sure about the '-bail' switch for sqlite3.  Can you instead add '.bail
ON' to the top of your
> inserts.sql file ?
> 

I interrupted the process after 11 hours, integrated your suggestions and
started the process again - now its working since more than 5 hours.

Oliver

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Simon Slavin]

On 11 Jan 2011, at 8:00am, Oliver Peters wrote:

> I'm on WinXP and using sqlite 3.7.4 with the CLI. I try to insert ~ 10,100,000
> records into a schema with different tables

It that takes 11 hours, that means you're taking about 4ms per INSERT.  I don't 
know if this is unusually high.  You might like to try 'PRAGMA synchronous = 
OFF'



> sqlite3 -bail extra.db3 < inserts.sql

I'm not sure about the '-bail' switch for sqlite3.  Can you instead add '.bail 
ON' to the top of your inserts.sql file ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Oliver Peters]

Hello,

I'm on WinXP and using sqlite 3.7.4 with the CLI. I try to insert ~ 10,100,000
records into a schema with different tables (http://pastebin.com/cbsPHNEj). The
db file has already 1.9 GB when I start the INSERTs via

sqlite3 -bail extra.db3 < inserts.sql

The statements in inserts.sql look like

PRAGMA foreign_keys = ON;
-- table01
BEGIN TRANSACTION;
INSERT INTO 
INSERT INTO 
...
COMMIT;
-- table02
BEGIN TRANSACTION;
INSERT INTO 
INSERT INTO 
...
COMMIT;

-- last table
BEGIN TRANSACTION;
INSERT INTO 
INSERT INTO 
...
COMMIT;
PRAGMA foreign_keys = OFF;

Most INSERTS are done into the table Verteilerdaten (>10,000,000). I think the
time depends heavily on the activated FOREIGN KEYs - is my assumption correct
and is this a behaviour I only can avoid by not switching this PRAGMA on?

greetings
Oliver

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users