batchsql

README

Simple multi-row INSERT support for Go SQL

INSERT statements are optimized by batching the data, that is using the multiple rows syntax.

Example: Inserting 3 new records to key, value columns

original statement:

INSERT INTO key_value (key, value) VALUES (?,?)

the statement is rewritten using the multi-row syntax and executed as a prepared statement:

INSERT INTO key_value (key, value) VALUES (?,?),(?,?),(?,?)

Multi-row statements are batched to allow for better control of database load. Some database engines put a limit on max number of multi-row values and even if a limit is not specified, unnecessarily large batch size might actually harm performance. Make sure to test performance of batched queries using variety of batch sizes and use an optimal value that offers best speed to database load ratio. Good starting points are 2000, 5000 and 10000.

The module exposes a utility function CheckQuery. Do yourself a favour and run tests on queries through it to avoid any nasty surprises (not all queries are eligible for multi-row rewrite and it's better to learn about any issues prior to executing a query on an engine).

Status

Beta

Installation

Install:

go get -u github.com/go-batchsql/batchsql

Import:

import "github.com/go-batchsql/batchsql"

Usage

Multi-row batch insert can be used with sql.Tx or sql.DB. Sample transactional usage with mysql driver and insert batch size 5000:

db, err := sql.Open("mysql", connStr)
if err != nil {
	panic(err)
}
defer db.Close()

// sql.Open doesn't open a connection. Validate DSN data:
if err = db.Ping(); err != nil {
	panic(err)
}

query := "INSERT INTO t (c1,c2,c3) VALUES (?,?,CURRENT_TIMESTAMP)"

var args []([]interface{})
args = append(args, []interface{}{"value#1-1", "value#1-2"})
args = append(args, []interface{}{"value#2-1", "value#2-2"})
args = append(args, []interface{}{"value#3-1", "value#3-2"})
...
args = append(args, []interface{}{"value#100000-1", "value#100000-2"}

tx, err := db.Begin()
if err != nil {
	panic(err)
}
mr := &batchsql.MultiRow{Conn: tx, BatchSize: 5000}
if err = mr.MultiInsert(query, args...); err != nil {
	tx.Rollback()
	panic(err)
}
tx.Commit()

License

Apache License Version 2.0 - See LICENSE file for more details

Documentation

Index

• func CheckQuery(query string) error
• type MultiRow
  • func (conn *MultiRow) MultiInsert(query string, args ...([]interface{})) error
• type Preparable

Functions

func CheckQuery

func CheckQuery(query string) error

CheckQuery is checking if query string is eligible for rewriting into a multi-row insert.

Types

type MultiRow

type MultiRow struct {
	Conn      Preparable
	BatchSize int
}

MultiRow is a wrapper around sql.DB or sql.Tx supporting multi-row insert statements execution with arbitrary batch size

func (*MultiRow) MultiInsert

func (conn *MultiRow) MultiInsert(
	query string,
	args ...([]interface{})) error

MultiInsert is executing multi-row insert query iterating over the list of arguments in args. The query string is checked for eligibility for multi-row rewrite. INSERT statements are optimized by batching the data using common multi-row syntax and results are discarded

type Preparable

type Preparable interface {
	Prepare(query string) (*sql.Stmt, error)
}

Preparable is an interface for any type able to prepare SQL statement