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
