如何优化sqlite的查询速度

展开阅读全文

更快的sqlite3查询吗? 我需要尽快处理100万以上的行

12-28

<div class="post-text" itemprop="text"> <p>What's the fastest way to read a sqlite3 table in golang?</p> <pre><code>package main import ( "fmt" "database/sql" _ "github.com/mattn/go-sqlite3" "log" "time" ) func main() { start := time.Now() db, err := sql.Open("sqlite3", "/Users/robertking/go/src/bitbucket.org/thematicanalysis/optimization_test/robs.db") if err != nil { log.Fatal(err) } defer db.Close() rows, err := db.Query("select * from data") if err != nil { log.Fatal(err) } defer rows.Close() for rows.Next() { } err = rows.Err() if err != nil { log.Fatal(err) } fmt.Println(time.Since(start)) } </code></pre> <p>This takes 8 seconds in Go because <code>.Next</code> is <a href="https://github.com/mattn/go-sqlite3/issues/379" rel="noreferrer">slow</a>. In python a <code>fetchall</code> takes only 4 seconds! I'm rewriting in GO to gain performance not lose performance.</p> <p>Here is the python code, I couldn't find an equivalent of <code>fetchall</code> in go:</p> <pre><code>import time start = time.time() import sqlite3 conn = sqlite3.connect('/Users/robertking/go/src/bitbucket.org/thematicanalysis/optimization_test/robs.db') c = conn.cursor() c.execute("SELECT * FROM data") x = c.fetchall() print time.time() - start </code></pre> <p>Edit: adding bounty. I'm reading the data in go, python and C, here are results. Dont want to use C, but will stick with python if GO isnt faster.:</p> <pre><code>py: 2.45s go: 2.13s (using github.com/mxk/go-sqlite/sqlite3 instead of github.com/mattn/go-sqlite3) c: 0.32s </code></pre> <p>I feel like go should be closer to the c side of thing? anyone know how to make it faster? is it possible to avoid mutex with readonly mode?</p> <p>edit:</p> <p>It seems like all the sqlite3 implementations are slow (too much reflection and too many cgo calls for conversions). So i'll have to just write my own interface.</p> <p>Here's the schema:</p> <pre><code>CREATE TABLE mytable ( c0 REAL, c1 INTEGER, c15 TEXT, c16 TEXT, c17 TEXT, c18 TEXT, c19 TEXT, c47 TEXT, c74 REAL DEFAULT 0, c77 TEXT, c101 TEXT, c103 TEXT, c108 TEXT, c110 TEXT, c125 TEXT, c126 TEXT, c127 REAL DEFAULT 0, x INTEGER PRIMARY KEY ); </code></pre> <p>and the query is dynamic but usually something like this:</p> <pre><code>SELECT c77,c77,c125,c126,c127,c74 from mytable </code></pre> <p>edit:</p> <p>looks like i'll fork the sqlite3 implementation and make some methods that focus on performance, </p> <p>this is an example of some code which is much faster:.</p> <pre><code>package main /* #cgo LDFLAGS: -l sqlite3 #include "sqlite3.h" */ import "C" import ( //"database/sql" "log" "reflect" "unsafe" ) type Row struct { v77 string v125 string v126 string v127 float64 v74 float64 } // cStr returns a pointer to the first byte in s. func cStr(s string) *C.char { h := (*reflect.StringHeader)(unsafe.Pointer(&s)) return (*C.char)(unsafe.Pointer(h.Data)) } func main() { getDataFromSqlite() } func getDataFromSqlite() { var db *C.sqlite3 name := "../data_dbs/all_columns.db" rc := C.sqlite3_open_v2(cStr(name+"\x00"), &db, C.SQLITE_OPEN_READONLY, nil) var stmt *C.sqlite3_stmt; rc = C.sqlite3_prepare_v2(db, cStr("SELECT c77,c125,c126,c127,c74 from data\x00"), C.int(-1), &stmt, nil); rc = C.sqlite3_reset(stmt); var result C.double result = 0.0 rc = C.sqlite3_step(stmt) for rc == C.SQLITE_ROW { C.GoString((*C.char)(unsafe.Pointer(C.sqlite3_column_text(stmt, 0)))) C.GoString((*C.char)(unsafe.Pointer(C.sqlite3_column_text(stmt, 1)))) C.GoString((*C.char)(unsafe.Pointer(C.sqlite3_column_text(stmt, 2)))) C.sqlite3_column_double(stmt, 3) result += C.sqlite3_column_double(stmt, 4) rc = C.sqlite3_step(stmt) } log.Println(result) } </code></pre> </div> 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览