On Wed, Nov 9, 2011 at 5:21 PM, yqpl <[email protected]> wrote:
>
> Hi,
>
> my task is to parse a lot of files and then insert them to sqlite database.
> it could be thousands of files. i use c#.
>
> im starting a transaction
> then make a lot of inserts and commit.
> ive got about 30 inserts per second
I typically get 100,000 rows per second on a modern workstation, from
inside a transaction. Are you *sure* you are using a transaction?
> but after a while it is dropping to
> about 1-2 inserts per second. it takse about ~500 inserts to drop to this
> 1-2 insert per sec.
>
> ive got indexes on this database - but this dosnt make difference as i
> checked on copy without indexes.
>
> 1) why it is getting tired ans slows down? how to fix it?
>
> o tried to this in a loop with 100 inserts to keep speed reasonable. then
> closing database and reopening.
> after close and reopen next commit gets SQLITE_BUSY/* The database file is
> locked */
>
> 2) wtf?
>
> please help me im stucked
>
> code is like:
>
> //done by background worker
> void import(object sender, DoWorkEventArgs e)
> {
> DataTable tab;
> tab = PST_POSTGRES.Postgres.Query(textBox6.Text, textBox1.Text,
> textBox3.Text, textBox4.Text, textBox5.Text, textBox2.Text);
>
>
> SQLiteWrapper.SQLite db = new SQLiteWrapper.SQLite();
> db.OpenDatabase(sqlite_db);
> db.BeginTransaction();
>
>
> foreach (DataRow r in tab.Rows)
> {
>
> if (bw.CancellationPending == true)
> {
> e.Cancel = true;
> break;
> }
> //import here
>
> foreach (object o in imported)
> {
> doinserts(o)
> }
>
> //here is an reopen condition - when gets slower
> if ((((long)(imported * 1000)) / stoper.ElapsedMilliseconds)
> < next)
> {
> db.CommitTransaction();
> db.CloseDatabase();
> db = new SQLiteWrapper.SQLite();
> db.OpenDatabase(sqlite_db);
> db.begintransaction();
> }
> next = (((long)(imported * 1000)) /
> stoper.ElapsedMilliseconds);
> }
>
> db.CommitTransaction();
> db.CloseDatabase();
> stoper.Stop();
> }
> --
> View this message in context:
> http://old.nabble.com/inserts%2C-performance%2C-file-lock...-tp32814772p32814772.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
