On 26 Oct 2014, at 9:27am, Ali Jawad <alijaw...@gmail.com> wrote: > right now this is all about the write process to > the database. 4 scripts run simultaneously, writing 500 entries each > through a while loop to 500 tables each every 10 minutes. > > The relevant part is here > > sqlite3 websites.db "PRAGMA busy_timeout=1500;insert into [$SITE] > (date,eu,us) values ($DATE,$DIFF,$DIFF2);"
First, a database with 500 tables in is probably badly organised and will lead to slow operations (and therefore locks !). Any time you find yourself using a 500-value data variable as a table name (in your case, $SITE) you're probably doing something wrong. It would be better to organise your table so that the $SITE name is a column in a table: CREATE TABLE samples (sitename TEXT,date TEXT,eu <whatever>,us <whatever>) insert into samples (sitename,date,eu,us) values ($SITE,$DATE,$DIFF,$DIFF2); It also means that you never have to worry about $SITE containing a character that is not legal in a table name. However, in the rest of this reply I will assume that you have good reasons for not wanting to reorganise your data in this way. Second, you are opening and closing the database 500 times, and opening and closing the database requires a huge amount of unique access, and is therefore keeping it busy, and therefore locked for a long time. So instead of running the SQLite shell tool 500 times, run it just once. In your script which runs the shell tool, instead of putting the data directly into the database, have it write the data to a text file. This text file should read PRAGMA busy_timeout=1500; BEGIN; insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); ... all your other INSERTs here ... END; Once you've written the whole text file you can tell the shell tool to execute it using the following command sqlite3 websites.db ".read commands.txt" All the INSERTs will happen while the file is open once, and the BEGIN/END means that they'll all happen in the same transaction, which will also make everything far faster. In SQLite it's transactions that take time, not individual commands. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
