Paul Sanderson wrote
> Unlike a rollback journal a WAL file can have multiple copies of the same
> page.
> 
> So from yor main loop, expanding the following code may help us
> understand.
> 
> "insert all downloaded rows"
> 
> If your inserted records is 5million separate insertions then each
> insertion could result in a table leaf page being written to the WAL
> file. Each insertion could potentially also result in one (or more)
> table interior pages being written to the WAL. Subsequent insertions
> could result in multiple copies of the same page.

Maybe you got me onto the right track...

I'll start with the record size estimation:

CREATE TABLE [discounttype]
(
        [createdon] DATETIME    NULL,
        [description] NTEXT     NULL,
        [discounttypeid] UNIQUEIDENTIFIER       NOT NULL CONSTRAINT 
PK_discounttype
PRIMARY KEY ROWGUIDCOL DEFAULT (newid()),
        [isamounttype] BIT      NOT NULL DEFAULT(0),
        [modifiedon] DATETIME   NULL,
        [name] NVARCHAR(200)    NULL COLLATE NOCASE
);

The record will be rather small. It consists of
2xDATETIME - usu. 38 By
UNIQUEIDENTIFIER - 32 By
+ 2 rather short strings

Altogether I wouldn't be surprised if  the page size (1024) is about 7x
larger than the average record length. This (together with your explanation)
would explain extreme WAL size.

---------------------------

Thanks to your post I discovered multiple-row inserts so that I now
understand what you asked.

I found  this nice article
<https://www.simple-talk.com/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/>
  
that (although for SQL server) proves that multi-inserts can greatly speed
up bulk inserts. I am quite confident that most of arguments brought by that
article will apply also to our case. (sqlite used from c#)

Will have to test it thoroughly and (if successful) modify our application
code.





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80052.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to