query - sqlite sql commands

¿Consulta más rápida de sqlite 3 en ir? Necesito procesar 1 millón+filas lo más rápido posible

Question

¿Cuál es la forma más rápida de leer una tabla sqlite3 en golang?

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))
}

Esto lleva 8 segundos en Go porque .Next es slow . En python un fetchall toma solo 4 segundos! Estoy reescribiendo en GO para obtener un rendimiento, no perder el rendimiento.

Aquí está el código de python, no pude encontrar un equivalente de fetchall en go:

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

Edición: añadiendo recompensa. Estoy leyendo los datos en go, python y C, aquí están los resultados. No quiero usar C, pero se quedará con Python si GO no es más rápido:

py: 2.45s
go: 2.13s (using github.com/mxk/go-sqlite/sqlite3 instead of github.com/mattn/go-sqlite3)
c:  0.32s

Siento que ir debería estar más cerca del lado c de la cosa? ¿Alguien sabe cómo hacerlo más rápido? ¿Es posible evitar la exclusión mutua con el modo de solo lectura?

editar:

Parece que todas las implementaciones de sqlite3 son lentas (demasiada reflexión y demasiadas llamadas de cgo para conversiones). Así que tendré que escribir mi propia interfaz.

Aquí está el esquema:

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
);

y la consulta es dinámica pero usualmente algo como esto:

SELECT c77,c77,c125,c126,c127,c74 from mytable

editar:

parece que voy a bifurcar la implementación de sqlite3 y hacer algunos métodos que se centran en el rendimiento,

Este es un ejemplo de código que es mucho más rápido:

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)
}

Answer 1

Introducción

Supongo que tenemos un problema con la forma en que se mide el rendimiento aquí, por lo que escribí un pequeño programa Go para generar registros y guardarlos en una base de datos SQLite, así como en la implementación de Python and Go de una pequeña tarea que realizar en esos registros. .

Puede encontrar el repositorio correspondiente en https://github.com/mwmahlberg/sqlite3perf

El modelo de datos

Los registros generados consisten en

• ID : un ID de fila generado por SQLite
• rand : un valor hexadecimal de 8 bytes codificado en hexadecimal
• hash : un hash SHA256 codificado en hexadecimal del rand codificado

El esquema de la tabla es relativamente simple:

sqlite> .schema
CREATE TABLE bench (ID int PRIMARY KEY ASC, rand TEXT, hash TEXT);

Primero generé 1.5M registros y luego limpié la base de datos sqlite con

$ ./sqlite3perf generate -r 1500000 -v

Luego llamé a la implementación de Go contra esos registros de 1.5M. Tanto la implementación de Go como la de Python básicamente hacen la misma tarea simple:

1. Lea todas las entradas de la base de datos.
2. Para cada fila, descodifique el valor aleatorio de hexadecimal, luego cree un hexadecimal SHA256 del resultado.
3. Compare la cadena hexadecimal SHA256 generada con la que está almacenada en la base de datos
4. Si coinciden, continuar, de lo contrario romper.

Suposiciones

Mi suposición explícitamente fue que Python realizó algún tipo de carga perezosa y, posiblemente, incluso la ejecución de la consulta SQL.

Los resultados

Ir implementacion

$ ./sqlite3perf bench
2017/12/31 15:21:48 bench called
2017/12/31 15:21:48 Time after query: 4.824009ms
2017/12/31 15:21:48 Beginning loop
2017/12/31 15:21:48 Acessing the first result set 
    ID 0,
    rand: 6a8a4ad02e5e872a,
    hash: 571f1053a7c2aaa56e5c076e69389deb4db46cc08f5518c66a4bc593e62b9aa4
took 548.32µs
2017/12/31 15:21:50 641,664 rows processed
2017/12/31 15:21:52 1,325,186 rows processed
2017/12/31 15:21:53 1,500,000 rows processed
2017/12/31 15:21:53 Finished loop after 4.519083493s
2017/12/31 15:21:53 Average 3.015µs per record, 4.523936078s overall

Anote los valores de "tiempo después de la consulta" (el tiempo que tardó en regresar el comando de consulta) y el tiempo que tomó acceder al primer conjunto de resultados después de que se inició la iteración sobre el conjunto de resultados.

Implementación de Python

$ python bench.py 
12/31/2017 15:25:41 Starting up
12/31/2017 15:25:41 Time after query: 1874µs
12/31/2017 15:25:41 Beginning loop
12/31/2017 15:25:44 Accessing first result set
    ID: 0
    rand: 6a8a4ad02e5e872a
    hash: 571f1053a7c2aaa56e5c076e69389deb4db46cc08f5518c66a4bc593e62b9aa4
took 2.719312 s
12/31/2017 15:25:50 Finished loop after 9.147431s
12/31/2017 15:25:50 Average: 6.098µs per record, 0:00:09.149522 overall

Nuevamente, tenga en cuenta el valor para "tiempo después de la consulta" y el tiempo que llevó acceder al primer conjunto de resultados.

Resumen

La implementación de Go tardó bastante tiempo en volver después de que se envió la consulta SELECT, mientras que Python parecía ser muy rápido en comparación. Sin embargo, desde el momento en que se tardó en acceder al primer conjunto de resultados, podemos ver que la implementación de Go es más de 500 veces más rápida que el acceso al primer conjunto de resultados (5.372329ms vs. 2719.312ms) y casi el doble de rápido para la tarea a mano como la implementación de Python.

Notas

• Para probar la suposición de que Python realmente realiza una carga lenta en el conjunto de resultados, se tuvo que acceder a todas y cada una de las filas y columnas para asegurarse de que Python está obligado a leer el valor de la base de datos.
• Elegí una tarea de hash porque presumiblemente la implementación de SHA256 está altamente optimizada en ambos idiomas.

Conclusión

Python parece hacer una carga perezosa de conjuntos de resultados y posiblemente ni siquiera ejecute una consulta a menos que realmente se acceda al conjunto de resultados correspondiente. En este escenario simulado, el controlador SQLite de mattn para Go supera a Python''s entre aproximadamente el 100% y los órdenes de magnitud, según lo que quieras hacer.

Editar : Para tener un procesamiento rápido, implementa tu tarea en Go. Si bien lleva más tiempo enviar la consulta real, acceder a las filas individuales del conjunto de resultados es mucho más rápido. Yo sugeriría comenzar con un pequeño subconjunto de sus datos, digamos 50k registros. Luego, para mejorar aún más su código, use la profiling de profiling para identificar sus cuellos de botella. Dependiendo de lo que quiera hacer durante el procesamiento, las pipelines , por ejemplo, pueden ayudar, pero es difícil decir cómo mejorar la velocidad de procesamiento de la tarea en cuestión sin un código real o una descripción completa.

Answer 2

Los valores de escaneo del ejemplo de filas recuperadas leen el paso 10 .
Como Query() & QueryRow() devuelve un puntero a Filas y puntero a Fila desde la consulta de la base de datos, podemos usar las funciones Scan() en Estructuras de filas y filas para obtener acceso a los valores en la estructura de filas.

for rows.Next() {

 var empID sql.NullInt64
 var empName sql.NullString
 var empAge sql.NullInt64
 var empPersonId sql.NullInt64

 if err := rows.Scan(&empID, &empName, &empAge, 
                           &empPersonId); err != nil {
          log.Fatal(err)
 }

 fmt.Printf("ID %d with personID:%d & name %s is age %d/n",       
                   empID.Int64, empPersonId.Int64, empName.String, empAge.Int64)
}

También usamos la función Scan () de Row struct. Scan () es el único método declarado en el Struct de la fila.

func (r *Row) Scan(dest ...interface{}) error