Ускоренный запрос sqlite 3 в go? Мне нужно обработать 1 миллион + строк как можно быстрее

Какой самый быстрый способ прочитать таблицу sqlite3 в 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))
}

Это занимает 8 секунд в Go, потому что .Next slow. В python a fetchall занимает всего 4 секунды! Я переписываю GO, чтобы получить производительность, не теряя производительности.

Вот код python, я не смог найти эквивалент fetchall в 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

Изменить: добавление баунти. Я читаю данные в go, python и C, вот результаты. Не хотите использовать C, но будет придерживаться python, если GO не быстрее.:

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

Мне кажется, что go должен быть ближе к стороне? кто-нибудь знает, как сделать это быстрее? можно ли избежать мьютекса с режимом readonly?

изменить:

Кажется, что все реализации sqlite3 медленны (слишком много отражений и слишком много вызовов cgo для конверсий). Поэтому мне придется просто написать собственный интерфейс.

Здесь схема:

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

и запрос является динамическим, но обычно что-то вроде этого:

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

изменить:

похоже, что я разблокирую реализацию sqlite3 и сделаю несколько методов, которые будут сосредоточены на производительности,

это пример кода, который намного быстрее:.

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

Ответы

Ответ 1

Введение

Мое предположение заключалось в том, что у нас есть проблема с измерением производительности здесь, поэтому я написал небольшую программу Go для создания записей и сохранения их в базе данных SQLite, а также для реализации Python и Go небольшой задачи по этим записям.

Вы можете найти соответствующий репозиторий в https://github.com/mwmahlberg/sqlite3perf

Модель данных

Созданные записи состоят из

Схема таблицы относительно проста:

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

Сначала я сгенерировал записи на 1.5M и затем очистил базу данных sqlite с помощью

$ ./sqlite3perf generate -r 1500000 -v

Затем я назвал реализацию Go в отношении этих записей за 1,5М. Как Go, так и реализация Python в основном выполняют одну и ту же простую задачу:

  • Прочитайте все записи из базы данных.
  • Для каждой строки декодируйте случайное значение из hex, затем создайте шестнадцатеричный SHA256 из результата.
  • Сравните сгенерированную шестнадцатеричную строку SHA256 с той, которая хранится в базе данных
  • Если они совпадают, продолжайте, иначе перерыв.

Предположения

Мое предположение явно заключалось в том, что Python сделал некоторый тип ленивой загрузки и/или, возможно, даже выполнение SQL-запроса.

Результаты

Go реализация

$ ./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

Обратите внимание на значения для "time after query" (время, которое потребовала команда запроса для возврата), и время, затрачиваемое на получение первого набора результатов после начала итерации по набору результатов.

Реализация 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

Снова обратите внимание на значение "время после запроса" и время, необходимое для доступа к первому набору результатов.

Резюме

Потребовалось некоторое время, чтобы вернуться после запроса SELECT, в то время как Python, казалось, быстро сверкал в сравнении. Однако с момента, когда потребовалось фактически получить доступ к первому набору результатов, мы видим, что реализация Go более чем в 500 раз быстрее, чтобы фактически получить доступ к первому набору результатов (5.372329ms против 2719.312ms) и примерно вдвое быстрее для задачи как реализация Python.

Примечания

  • Чтобы доказать предположение, что Python фактически выполняет ленивую загрузку в результирующем наборе, нужно было получить доступ к каждой строке и столбцу, чтобы убедиться, что Python вынужден фактически считывать значение из базы данных.
  • Я выбрал хеширование, потому что предположительно реализация SHA256 сильно оптимизирована на обоих языках.

Заключение

Python, похоже, делает ленивую загрузку наборов результатов и, возможно, даже не выполняет запрос, если на самом деле не получен соответствующий набор результатов. В этом симулированном сценарии матовый драйвер SQLite для Go превосходит Python примерно на 100% и порядков в зависимости от того, что вы хотите сделать.

Изменить. Поэтому, чтобы иметь быструю обработку, выполните свою задачу в Go. Хотя для отправки фактического запроса требуется больше времени, доступ к отдельным строкам набора результатов намного быстрее. Я предлагаю начать с небольшого подмножества ваших данных, скажем, 50 тыс. Записей. Затем, чтобы улучшить код, используйте profiling, чтобы определить ваши узкие места. В зависимости от того, что вы хотите сделать во время обработки, pipelines, например, может помочь, но как улучшить скорость обработки задачи под рукой трудно сказать без фактического кода или подробного описания.

Ответ 2

Сканирование значений из извлеченных строк пример прочитайте шаг 10.
Поскольку Query() и QueryRow() возвращает указатель на строки и указатель на строку из запроса базы данных соответственно, мы можем использовать Scan() функции на строках Rows и Row, чтобы получить доступ к значениям в строках struct.

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

Мы также использовали функцию Scan() из Row struct. Scan() - единственный метод, объявленный в Row Struct.

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