Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re: DBI::SQLite slowness (DBD::SQLite bulk insert woes)

by Anonymous Monk
on Sep 20, 2013 at 02:32 UTC ( #1054934=note: print w/ replies, xml ) Need Help??


in reply to DBI::SQLite slowness

See DBD::SQLite bulk insert woes and use http://metacpan.org/module/DBD::SQLite#Transactions, so

## begin transaction ## foreach loop ## end transaction

see also Re^5: Index a file with pack for fast access/Sauntering Coder: Fastest bulk import into sqlite which says

.echo ON .read create_table_without_pk.sql PRAGMA cache_size = 400000; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; PRAGMA count_changes = OFF; PRAGMA temp_store = MEMORY; PRAGMA auto_vacuum = NONE; .separator "\t" .import a_tab_seprated_table.txt mytable BEGIN; .read add_indexes.sql COMMIT; .exit sqlite3 mydb.db < commands.txt
#~

http://www.sqlite.org/pragma.html#pragma_cache_size
http://www.sqlite.org/pragma.html#pragma_synchronous
http://www.sqlite.org/pragma.html#pragma_journal_mode
http://www.sqlite.org/pragma.html#pragma_locking_mode
http://www.sqlite.org/pragma.html#pragma_count_changes
http://www.sqlite.org/pragma.html#pragma_temp_store
http://www.sqlite.org/pragma.html#pragma_auto_vacuum
Command Line Shell For SQLite


Comment on Re: DBI::SQLite slowness (DBD::SQLite bulk insert woes)
Select or Download Code
Re^2: DBI::SQLite slowness (DBD::SQLite bulk insert woes)
by Anonymous Monk on Sep 20, 2013 at 02:45 UTC
      I can attest to efficiency improvements due to at least one of the notes in SQLite Optimization FAQ. Used it at a customer site once, too long ago to remember the numbers but I recall the difference was dramatic. Sample of the commands needed:
      C:\Steve>sqlite test.db C:\Steve>call sqlite-STEVEM2 test.db C:\Steve>"C:\App\SQLite\sqlite3.exe" test.db SQLite version 3.3.5 Enter ".help" for instructions sqlite> pragma default_cache_size; 2000 sqlite> pragma default_cache_size=200000; sqlite> pragma default_cache_size; 200000 sqlite> .exit C:\Steve>sqlite test.db C:\Steve>call sqlite-STEVEM2 test.db C:\Steve>"C:\App\SQLite\sqlite3.exe" test.db SQLite version 3.3.5 Enter ".help" for instructions sqlite> pragma default_cache_size; 200000 sqlite> .quit C:\Steve>

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1054934]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (7)
As of 2014-12-20 05:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (95 votes), past polls