Slow SQLite data insertion Solution

SQLite inserts data slowly because it is already in the form of a file on the disk. Each time you access the data, you need to open the file. If you perform a large amount of operations on the data, it is very slow.

 

The solution is to commit in the form of a transaction: because after the transaction starts, a large number of statements for operations are stored in the memory, and all the statements are written to the database only when the transaction is committed. At this time, the database file is opened only once.

 

I used 4366 s to insert 507.781 pieces of data in the form of transactions. On average, each piece of data is inserted for 116 Ms. In the form of transactions, only 391 MS is used for insertion.

 

Transaction method:

 

Begin; <br/> insert into "table" values ('A', 'B', 'C '); <br/> insert into "table" values ('A', 'B', 'C'); <br/> insert into "table" values ('A ', 'B', 'C'); <br/> commit;

 

The single quotation mark escape in SQLite is not a backslash '/' but a single quotation mark, that is, a single quotation mark ('') is added before the single quotation mark ('').

 

The following is a script for calculating Apache logs. Apache logs are divided into directories by date and files by hour under the directory by date. log data sources:

124.115.0.27--[25/02/2011: 23: 00: 01] "Get/images/css.css HTTP/1.1" 200 3487 "http://www.XXXX.com/index.php? Categoryid = 175 "" sosospider + (+ http://help.soso.com/webspider.htm )"

221.130.177.108--[25/02/2011: 22: 59: 53] "Get/index. php? Newsid = 2010122810463740 HTTP/1.1 "200 5079 "-""-"

The script is as follows:

<? Php <br/>/** <br/> * convert Apache access logging to SQLite database <br/> * Only IP addresses are retained, session ID <br/> * Note: The execution program and log data are stored in the same directory, and SQLite data is stored in the ipdb directory, to avoid data sources not found <br/> */<br/> $ rootpath = _ DIR __; <br/> // convert the data directory <br/> $ dirpath = $ rootpath. '/'; <br/> // SQLite database directory <br/> $ ipdbpath = $ rootpath. '/ipdb'; <br/> $ db = NULL; <br/> $ filearr = dirlist ($ dirpath); <br/> foreach ($ filearr as $ file) {<br/> $ dbpath = str_replace ($ d Irpath, $ ipdbpath, $ file); <br/> $ dbpath = substr ($ dbpath, 0,-7). '. db'; <br/> If (! File_exists ($ dbpath) {<br/> If (false ===( $ fp = fopen ($ dbpath, 'w '))) {<br/> exit ("Open File {$ dbpath} fail! /N "); <br/>}< br/> fclose ($ FP); <br/> If (! Is_null ($ dB) {$ db = NULL ;}< br/> $ db = new PDO ('sqlite :'. $ dbpath); <br/> // $ table = substr ($ file,-6, 2); <br/> $ table = 'log '; <br/> // $ SQL = "Drop table if exists '{$ table }'; <br/> $ SQL = "CREATE TABLE '{$ table}' (<br/> 'IP' text, <br/> 'start _ time' text, <br/> 'ucode' text <br/> ); <br/> Create index 'start _ time' <br/> on '{$ table}' ('start _ time' ASC); <br/> "; <br/> $ db-> exec ($ SQL); <br/>} else {<br/> If (is_null ($ dB) {$ db = new PDO ('sqlite :'. $ dbpath) ;}< br/>}< br/> // insert a folder record <br/> If (false! = ($ Fp = fopen ($ file, 'R') {<br/> $ I = 0; <br/> $ db-> begintransaction (); <br/> while (! Feof ($ FP) {< br/> $ buffer = fgets ($ FP, 1024); <br/> $ IP = trim (substr ($ buffer, 0, strpos ($ buffer, '-'); <br/> $ start_time = substr ($ buffer, strpos ($ buffer, '[') + 1, 19 ); <br/> If (false ===( strpos ($ buffer, '/') & empty ($ IP) continue; <br/> $ ucode = substr ($ buffer, strpos ($ buffer, '/') + 2, 32 ); <br/> $ SQL = "insert into 'log' ('IP', 'start _ time', 'ucode') values ('{$ IP }', '{$ start_time}', '{$ u Code} ') "; <br/> $ db-> exec ($ SQL); <br/> // If (0 = ($ I ++ % 100 )) echo '*'; <br/>}< br/> $ db-> commit (); <br/> fclose ($ FP ); <br/>}< br/> function dirlist ($ PATH) {<br/> $ dir = Dir ($ PATH ); <br/> static $ filearr = array (); <br/> while ($ file = $ Dir-> Read ())! = False) {<br/> If (is_dir ("{$ PATH}/{$ file}") & $ file! = '.' & $ File! = '.. ') {<Br/> dirlist ("{$ PATH}/{$ file}"); <br/>}else {<br/> if ($ file! = '.' & $ File! = '.. ') {<Br/> $ filearr [] = $ path. '/'. $ file; <br/>}< br/> $ Dir-> close (); <br/> return $ filearr; <br/>}< br/>?> <Br/>