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