Golang SQLite3 Performance Test

SQLite is a small database, very concise, that is, supporting files also support memory, more suitable for small independent projects, in the absence of the network to do some complex relational data storage and operation.

To test the performance of 10M (10 million) records, Test machine 4CPU, 8G memory, operating system: Ubuntu 16

Test results:

10M Write time: 92 seconds

Average retrieval time: 0.0172 milliseconds

Maximum memory consumption 1.7G

Use of driver

Github.com/mattn/go-sqlite3

Here's the code for the test

//SQLite in memory, be careful not to write only: memory: So every connection will requestDB, err: = SQL. Open ("Sqlite3", "File::memory:?mode=memory&cache=shared&loc=auto")    ifErr! =Nil {fmt. Println ("SQLite:", Err)} Defer db. Close () fmt. Println ("SQLite Start")    //CREATE table//delete from Bc;,sqlite field type less, bool type can use Integer, string with textSQLSTMT: = ' CREATE TABLE BC (B_code text notNULLPrimary key, C_code text notNULL, Code_type Integer, is_new integer); ' _, Err=db. Exec (sqlstmt)ifErr! =Nil {fmt. Println ("CREATE TABLE Error->%q:%s\n", err, sqlstmt)return    }    //CREATE INDEX, index and no index performance difference is huge, is not a magnitude, interested can be removed try_, Err = db. Exec ("CREATE INDEX Inx_c_code on BC (C_code);")    ifErr! =Nil {fmt. Println ("CREATE INDEX Error->%q:%s\n", err, sqlstmt)return    }    //Write 10M recordStart: =Time . Now (). Unix () TX, err:=db. Begin ()ifErr! =Nil {fmt. Println ("%q", Err)} stmt, err:= Tx. Prepare ("INSERT into BC (B_code, C_code, Code_type, is_new) VALUES (?,?,?,?)")    ifErr! =Nil {fmt. Println ("Insert Err%q", Err)} Defer stmt. Close ()varMint= 1000 * 1000varTotalint= 10 *m forI: = 0; I < total; i++{_, Err= stmt. Exec (FMT. Sprintf ("b%024d", I), FMT. Sprintf ("c%024d", I), 0, 1)        ifErr! =Nil {fmt. Println ("%q", Err)} } tx.commit () Insertend:=Time . Now (). Unix ()//randomly retrieved 10M times    varCount Int64 = 0stmt, Err= db. Prepare ("Select B_code, C_code, Code_type, is_new from BC where C_code =?"))    ifErr! =Nil {fmt. Println ("Select Err%q", Err)} Defer stmt. Close () BC:=New(BCCode) forI: = 0; I < total; i++{Err= stmt. Queryrow (FMT. Sprintf ("c%024d", I)). Scan (&AMP;BC. B_code, &AMP;BC. C_code, &AMP;BC. CodeType, &BC. IsNew)ifErr! =Nil {fmt. Println ("Query Err%q", Err)} //screen output will take a lot of time Ah, the calculation time is time to switch off better        //FMT. Println ("bcode=", BC. B_code, "\tccode=", BC. C_code, "\tcodetype=", BC. CodeType, "\tisnew=", BC. IsNew)count++} readend:=Time . Now (). Unix () fmt. Println ("Insert Span=", (Insertend-start),"Read span=", (Readend-insertend),"Avg read=", float64 (readend-insertend) *1000/float64 (count))

Golang SQLite3 Performance Test