[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] SQLite Transaction Rate and speed...
From:       "VF" <goodarm () gmail ! com>
Date:       2009-03-12 5:57:50
Message-ID: DB6A027856234C60BE73F51BEEB5E98B () xps
[Download RAW message or body]

Great suggestion!

I wonder however how should I implement it though... I am upserting tens of
millions of rows in chunks of several hundreds thousands (naturally I can't
do all this in memory). SQLite tutorial says if each update/insert is not
resulting in transaction, it can make up to 50k updates a second. 

I tried calling:

my $dbh = DBI->connect_cached(          # connect to your database, create
if needed
			    "dbi:SQLite:dbname=$dbpath", # DSN: dbi, driver,
database file
			    "",                          # no user
			    "",                          # no password
			    { RaiseError => 1, AutoCommit => 0 },         #
complain if something goes wrong
	) or die $DBI::errstr;
	
	# set pragmas
	$dbh->do('pragma synchronous=off;');
	$dbh->do('PRAGMA default_cache_size = 10000;');
	$dbh->do('pragma page_size=819;2');
	$dbh->do('pragma temp_store=memory;');

before the actual upserts, however it didn't seem to help. 

Now, if in my bulk_upser() function I do this:
	$dbh->do("BEGIN");
	foreach <arr> {
		$dbh-do("insert or ignore")
	}
	$dbh->do("COMMIT");

...would that work? How do I get rid of these atomic transactions?

In general though - I am a little surprised. This is a seemingly simple task
- hundreds of thousands of upserts in a non-shared DB - requiring such
"gymnastics" and taking hours to complete - seem to be neither "SQL" nor
"lite". Or is it a problem of Perl DBI interface?

Thanks a lot in advance, Bobby

> Is it possible for you to do the following?  The INSERT should fail
> silently if you violate a uniqueness constraint; we also set the initial
> counter to 0 as it will be immediately bumped to 1 by the UPDATE.  You
> should be able to wrap the whole thing in a transaction.
> 
> ***
> 
> INSERT OR IGNORE INTO MAPPINGS_$idx
>  (key, mapping, rank, counter, timeCreated, timeModified)
>    values (?, ?, 1, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
> 
> UPDATE MAPPINGS_$idx SET counter = counter + 1, 
>   timeModified = CURRENT_TIMESTAMP WHERE key = ? AND mapping = ?;
> 

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]