Repository: mattn/go-sqlite3
Pull request #188 (2015-03-23 21:22:33)

Optimize queries
Main change is running three SQLite API functions every time we cross the border to `Exec` a query.

Before:

```
BenchmarkExec           500000     162257 req/s
BenchmarkQuery          100000      60699 req/s
BenchmarkParams         100000      56971 req/s
BenchmarkStmt           200000      98856 req/s
BenchmarkRows             5000       1883 req/s
BenchmarkStmtRows         5000       1926 req/s
PASS
ok      github.com/mattn/go-sqlite3 18.560s
```

After:

```
BenchmarkExec           500000     172340 req/s
BenchmarkQuery          100000      62041 req/s
BenchmarkParams         100000      58299 req/s
BenchmarkStmt           200000     101137 req/s
BenchmarkRows             5000       1922 req/s
BenchmarkStmtRows         5000       1946 req/s
PASS
ok      github.com/mattn/go-sqlite3 18.178s
```

(These are single benchmarks, but the speedup in `BenchmarkExec` is consistent across runs.)


Patch:
diff --git a/sqlite3.go b/sqlite3.go
index 65865f3..1517280 100644
--- a/sqlite3.go
+++ b/sqlite3.go
@@ -47,14 +47,23 @@ _sqlite3_bind_blob(sqlite3_stmt *stmt, int n, void *p, int np) {
 #include <stdio.h>
 #include <stdint.h>
 
-static long
-_sqlite3_last_insert_rowid(sqlite3* db) {
-  return (long) sqlite3_last_insert_rowid(db);
+static int
+_sqlite3_exec(sqlite3* db, const char* pcmd, long* rowid, long* changes)
+{
+  int rv = sqlite3_exec(db, pcmd, 0, 0, 0);
+  *rowid = (long) sqlite3_last_insert_rowid(db);
+  *changes = (long) sqlite3_changes(db);
+  return rv;
 }
 
-static long
-_sqlite3_changes(sqlite3* db) {
-  return (long) sqlite3_changes(db);
+static int
+_sqlite3_step(sqlite3_stmt* stmt, long* rowid, long* changes)
+{
+  int rv = sqlite3_step(stmt);
+  sqlite3* db = sqlite3_db_handle(stmt);
+  *rowid = (long) sqlite3_last_insert_rowid(db);
+  *changes = (long) sqlite3_changes(db);
+  return rv;
 }
 
 */
@@ -121,8 +130,6 @@ type SQLiteTx struct {
 type SQLiteStmt struct {
 	c      *SQLiteConn
 	s      *C.sqlite3_stmt
-	nv     int
-	nn     []string
 	t      string
 	closed bool
 	cls    bool
@@ -232,14 +239,13 @@ func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, erro
 func (c *SQLiteConn) exec(cmd string) (driver.Result, error) {
 	pcmd := C.CString(cmd)
 	defer C.free(unsafe.Pointer(pcmd))
-	rv := C.sqlite3_exec(c.db, pcmd, nil, nil, nil)
+
+	var rowid, changes C.long
+	rv := C._sqlite3_exec(c.db, pcmd, &rowid, &changes)
 	if rv != C.SQLITE_OK {
 		return nil, c.lastError()
 	}
-	return &SQLiteResult{
-		int64(C._sqlite3_last_insert_rowid(c.db)),
-		int64(C._sqlite3_changes(c.db)),
-	}, nil
+	return &SQLiteResult{int64(rowid), int64(changes)}, nil
 }
 
 // Begin transaction.
@@ -261,17 +267,14 @@ func errorString(err Error) string {
 //   :memory:
 //   file::memory:
 // go-sqlite handle especially query parameters.
-//   _loc=XXX
+//   loc=XXX
 //     Specify location of time format. It's possible to specify "auto".
-//   _busy_timeout=XXX
-//     Specify value for sqlite3_busy_timeout.
 func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 	if C.sqlite3_threadsafe() == 0 {
 		return nil, errors.New("sqlite library was not compiled for thread-safe operation")
 	}
 
 	var loc *time.Location
-	busy_timeout := 5000
 	pos := strings.IndexRune(dsn, '?')
 	if pos >= 1 {
 		params, err := url.ParseQuery(dsn[pos+1:])
@@ -279,27 +282,18 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 			return nil, err
 		}
 
-		// _loc
-		if val := params.Get("_loc"); val != "" {
+		// loc
+		if val := params.Get("loc"); val != "" {
 			if val == "auto" {
 				loc = time.Local
 			} else {
 				loc, err = time.LoadLocation(val)
 				if err != nil {
-					return nil, fmt.Errorf("Invalid _loc: %v: %v", val, err)
+					return nil, fmt.Errorf("Invalid loc: %v: %v", val, err)
 				}
 			}
 		}
 
-		// _busy_timeout
-		if val := params.Get("_busy_timeout"); val != "" {
-			iv, err := strconv.ParseInt(val, 10, 64)
-			if err != nil {
-				return nil, fmt.Errorf("Invalid _busy_timeout: %v: %v", val, err)
-			}
-			busy_timeout = int(iv)
-		}
-
 		if !strings.HasPrefix(dsn, "file:") {
 			dsn = dsn[:pos]
 		}
@@ -320,7 +314,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 		return nil, errors.New("sqlite succeeded without returning a database")
 	}
 
-	rv = C.sqlite3_busy_timeout(db, C.int(busy_timeout))
+	rv = C.sqlite3_busy_timeout(db, 5000)
 	if rv != C.SQLITE_OK {
 		return nil, Error{Code: ErrNo(rv)}
 	}
@@ -379,18 +373,10 @@ func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error) {
 		return nil, c.lastError()
 	}
 	var t string
-	if tail != nil && C.strlen(tail) > 0 {
+	if tail != nil && *tail != '\000' {
 		t = strings.TrimSpace(C.GoString(tail))
 	}
-	nv := int(C.sqlite3_bind_parameter_count(s))
-	var nn []string
-	for i := 0; i < nv; i++ {
-		pn := C.GoString(C.sqlite3_bind_parameter_name(s, C.int(i+1)))
-		if len(pn) > 1 && pn[0] == '$' && 48 <= pn[1] && pn[1] <= 57 {
-			nn = append(nn, C.GoString(C.sqlite3_bind_parameter_name(s, C.int(i+1))))
-		}
-	}
-	ss := &SQLiteStmt{c: c, s: s, nv: nv, nn: nn, t: t}
+	ss := &SQLiteStmt{c: c, s: s, t: t}
 	runtime.SetFinalizer(ss, (*SQLiteStmt).Close)
 	return ss, nil
 }
@@ -414,12 +400,7 @@ func (s *SQLiteStmt) Close() error {
 
 // Return a number of parameters.
 func (s *SQLiteStmt) NumInput() int {
-	return s.nv
-}
-
-type bindArg struct {
-	n int
-	v driver.Value
+	return int(C.sqlite3_bind_parameter_count(s.s))
 }
 
 func (s *SQLiteStmt) bind(args []driver.Value) error {
@@ -428,24 +409,8 @@ func (s *SQLiteStmt) bind(args []driver.Value) error {
 		return s.c.lastError()
 	}
 
-	var vargs []bindArg
-	narg := len(args)
-	vargs = make([]bindArg, narg)
-	if len(s.nn) > 0 {
-		for i, v := range s.nn {
-			if pi, err := strconv.Atoi(v[1:]); err == nil {
-				vargs[i] = bindArg{pi, args[i]}
-			}
-		}
-	} else {
-		for i, v := range args {
-			vargs[i] = bindArg{i + 1, v}
-		}
-	}
-
-	for _, varg := range vargs {
-		n := C.int(varg.n)
-		v := varg.v
+	for i, v := range args {
+		n := C.int(i + 1)
 		switch v := v.(type) {
 		case nil:
 			rv = C.sqlite3_bind_null(s.s, n)
@@ -506,22 +471,16 @@ func (r *SQLiteResult) RowsAffected() (int64, error) {
 func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error) {
 	if err := s.bind(args); err != nil {
 		C.sqlite3_reset(s.s)
-		C.sqlite3_clear_bindings(s.s)
 		return nil, err
 	}
-	rv := C.sqlite3_step(s.s)
+	var rowid, changes C.long
+	rv := C._sqlite3_step(s.s, &rowid, &changes)
 	if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE {
 		err := s.c.lastError()
 		C.sqlite3_reset(s.s)
-		C.sqlite3_clear_bindings(s.s)
 		return nil, err
 	}
-
-	res := &SQLiteResult{
-		int64(C._sqlite3_last_insert_rowid(s.c.db)),
-		int64(C._sqlite3_changes(s.c.db)),
-	}
-	return res, nil
+	return &SQLiteResult{int64(rowid), int64(changes)}, nil
 }
 
 // Close the rows.


Test patch:
diff --git a/sqlite3_test.go b/sqlite3_test.go
index aa86011..6570b52 100644
--- a/sqlite3_test.go
+++ b/sqlite3_test.go
@@ -640,7 +640,7 @@ func TestTimezoneConversion(t *testing.T) {
 	zones := []string{"UTC", "US/Central", "US/Pacific", "Local"}
 	for _, tz := range zones {
 		tempFilename := TempFilename()
-		db, err := sql.Open("sqlite3", tempFilename+"?_loc="+url.QueryEscape(tz))
+		db, err := sql.Open("sqlite3", tempFilename+"?loc="+url.QueryEscape(tz))
 		if err != nil {
 			t.Fatal("Failed to open database:", err)
 		}
@@ -845,7 +845,7 @@ func TestStress(t *testing.T) {
 func TestDateTimeLocal(t *testing.T) {
 	zone := "Asia/Tokyo"
 	tempFilename := TempFilename()
-	db, err := sql.Open("sqlite3", tempFilename+"?_loc="+zone)
+	db, err := sql.Open("sqlite3", tempFilename+"?loc="+zone)
 	if err != nil {
 		t.Fatal("Failed to open database:", err)
 	}
@@ -888,7 +888,7 @@ func TestDateTimeLocal(t *testing.T) {
 	db.Exec("INSERT INTO foo VALUES(?);", dt)
 
 	db.Close()
-	db, err = sql.Open("sqlite3", tempFilename+"?_loc="+zone)
+	db, err = sql.Open("sqlite3", tempFilename+"?loc="+zone)
 	if err != nil {
 		t.Fatal("Failed to open database:", err)
 	}
@@ -909,39 +909,3 @@ func TestVersion(t *testing.T) {
 		t.Errorf("Version failed %q, %d, %q\n", s, n, id)
 	}
 }
-
-func TestNumberNamedParams(t *testing.T) {
-	tempFilename := TempFilename()
-	db, err := sql.Open("sqlite3", tempFilename)
-	if err != nil {
-		t.Fatal("Failed to open database:", err)
-	}
-	defer os.Remove(tempFilename)
-	defer db.Close()
-
-	_, err = db.Exec(`
-	create table foo (id integer, name text, extra text);
-	`)
-	if err != nil {
-		t.Error("Failed to call db.Query:", err)
-	}
-
-	_, err = db.Exec(`insert into foo(id, name, extra) values($1, $2, $2)`, 1, "foo")
-	if err != nil {
-		t.Error("Failed to call db.Exec:", err)
-	}
-
-	row := db.QueryRow(`select id, extra from foo where id = $1 and extra = $2`, 1, "foo")
-	if row == nil {
-		t.Error("Failed to call db.QueryRow")
-	}
-	var id int
-	var extra string
-	err = row.Scan(&id, &extra)
-	if err != nil {
-		t.Error("Failed to db.Scan:", err)
-	}
-	if id != 1 || extra != "foo" {
-		t.Error("Failed to db.QueryRow: not matched results")
-	}
-}