Skip to content

Instantly share code, notes, and snippets.

@SQLServerIO
Forked from SchumacherFM/db.go
Created August 7, 2016 06:52
Show Gist options
  • Save SQLServerIO/91e63f29c5f13b0f3fc269c2e068a2b5 to your computer and use it in GitHub Desktop.
Save SQLServerIO/91e63f29c5f13b0f3fc269c2e068a2b5 to your computer and use it in GitHub Desktop.
GoLang Database SQL: Selecting an unknown amount of columns from a query. Benchmark results in db_test.go
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
)
const (
// 1745 rows
// columns are: value_id, entity_type_id, attribute_id, store_id, entity_id, value
TEST_QUERY = `SELECT * FROM catalog_product_entity_varchar`
)
func main() {
db, err := sql.Open("mysql", "magento-1-8:magento-1-8@tcp(:3306)/magento-1-8")
if err != nil {
log.Fatal(err)
}
defer db.Close()
rows, err := db.Query(TEST_QUERY)
fck(err)
defer rows.Close()
columnNames, err := rows.Columns()
fck(err)
rc := NewMapStringScan(columnNames)
for rows.Next() {
// cv, err := rowMapString(columnNames, rows)
// fck(err)
err := rc.Update(rows)
fck(err)
cv := rc.Get()
log.Printf("%#v\n\n", cv)
}
}
/**
using a map
*/
type mapStringScan struct {
// cp are the column pointers
cp []interface{}
// row contains the final result
row map[string]string
colCount int
colNames []string
}
func NewMapStringScan(columnNames []string) *mapStringScan {
lenCN := len(columnNames)
s := &mapStringScan{
cp: make([]interface{}, lenCN),
row: make(map[string]string, lenCN),
colCount: lenCN,
colNames: columnNames,
}
for i := 0; i < lenCN; i++ {
s.cp[i] = new(sql.RawBytes)
}
return s
}
func (s *mapStringScan) Update(rows *sql.Rows) error {
if err := rows.Scan(s.cp...); err != nil {
return err
}
for i := 0; i < s.colCount; i++ {
if rb, ok := s.cp[i].(*sql.RawBytes); ok {
s.row[s.colNames[i]] = string(*rb)
*rb = nil // reset pointer to discard current value to avoid a bug
} else {
return fmt.Errorf("Cannot convert index %d column %s to type *sql.RawBytes", i, s.colNames[i])
}
}
return nil
}
func (s *mapStringScan) Get() map[string]string {
return s.row
}
/**
using a string slice
*/
type stringStringScan struct {
// cp are the column pointers
cp []interface{}
// row contains the final result
row []string
colCount int
colNames []string
}
func NewStringStringScan(columnNames []string) *stringStringScan {
lenCN := len(columnNames)
s := &stringStringScan{
cp: make([]interface{}, lenCN),
row: make([]string, lenCN*2),
colCount: lenCN,
colNames: columnNames,
}
j := 0
for i := 0; i < lenCN; i++ {
s.cp[i] = new(sql.RawBytes)
s.row[j] = s.colNames[i]
j = j + 2
}
return s
}
func (s *stringStringScan) Update(rows *sql.Rows) error {
if err := rows.Scan(s.cp...); err != nil {
return err
}
j := 0
for i := 0; i < s.colCount; i++ {
if rb, ok := s.cp[i].(*sql.RawBytes); ok {
s.row[j+1] = string(*rb)
*rb = nil // reset pointer to discard current value to avoid a bug
} else {
return fmt.Errorf("Cannot convert index %d column %s to type *sql.RawBytes", i, s.colNames[i])
}
j = j + 2
}
return nil
}
func (s *stringStringScan) Get() []string {
return s.row
}
// rowMapString was the first implementation but it creates for each row a new
// map and pointers and is considered as slow. see benchmark
func rowMapString(columnNames []string, rows *sql.Rows) (map[string]string, error) {
lenCN := len(columnNames)
ret := make(map[string]string, lenCN)
columnPointers := make([]interface{}, lenCN)
for i := 0; i < lenCN; i++ {
columnPointers[i] = new(sql.RawBytes)
}
if err := rows.Scan(columnPointers...); err != nil {
return nil, err
}
for i := 0; i < lenCN; i++ {
if rb, ok := columnPointers[i].(*sql.RawBytes); ok {
ret[columnNames[i]] = string(*rb)
} else {
return nil, fmt.Errorf("Cannot convert index %d column %s to type *sql.RawBytes", i, columnNames[i])
}
}
return ret, nil
}
func fck(err error) {
if err != nil {
log.Fatal(err)
}
}
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"testing"
)
//$ go test -v -bench=. -benchmem .
//testing: warning: no tests to run
//PASS
//BenchmarkMapStringScan 300 4388248 ns/op 416755 B/op 20602 allocs/op
//BenchmarkStrStrScan 300 3990020 ns/op 416629 B/op 20602 allocs/op
//BenchmarkRowMapString 200 7937005 ns/op 1505452 B/op 36304 allocs/op
func BenchmarkMapStringScan(b *testing.B) {
db, err := sql.Open("mysql", "magento-1-8:magento-1-8@tcp(:3306)/magento-1-8")
if err != nil {
b.Fatal(err)
}
defer db.Close()
b.ResetTimer()
for i := 0; i < b.N; i++ {
rows, err := db.Query(TEST_QUERY)
if err != nil {
b.Fatal(err)
}
defer rows.Close()
columnNames, err := rows.Columns()
if err != nil {
b.Fatal(err)
}
rc := NewMapStringScan(columnNames)
for rows.Next() {
err := rc.Update(rows)
if err != nil {
b.Fatal(err)
}
_ = rc.Get()
}
}
}
func BenchmarkStrStrScan(b *testing.B) {
db, err := sql.Open("mysql", "magento-1-8:magento-1-8@tcp(:3306)/magento-1-8")
if err != nil {
b.Fatal(err)
}
defer db.Close()
b.ResetTimer()
for i := 0; i < b.N; i++ {
rows, err := db.Query(TEST_QUERY)
if err != nil {
b.Fatal(err)
}
defer rows.Close()
columnNames, err := rows.Columns()
if err != nil {
b.Fatal(err)
}
rc := NewStringStringScan(columnNames)
for rows.Next() {
err := rc.Update(rows)
if err != nil {
b.Fatal(err)
}
_ = rc.Get()
}
}
}
func BenchmarkRowMapString(b *testing.B) {
db, err := sql.Open("mysql", "magento-1-8:magento-1-8@tcp(:3306)/magento-1-8")
if err != nil {
b.Fatal(err)
}
defer db.Close()
b.ResetTimer()
for i := 0; i < b.N; i++ {
rows, err := db.Query(TEST_QUERY)
if err != nil {
b.Fatal(err)
}
defer rows.Close()
columnNames, err := rows.Columns()
if err != nil {
b.Fatal(err)
}
for rows.Next() {
_, err := rowMapString(columnNames, rows)
if err != nil {
b.Fatal(err)
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment