mattn/go-sqlite3

Next and Scan appear to be relatively slow?

Open

#379 opened on Feb 7, 2017

View on GitHub
 (10 comments) (3 reactions) (0 assignees)C (7,000 stars) (1,105 forks)batch import
help wantedresearch

Description

Howdy. I'm not sure this really merits a Issues or if it would be better as a conversation some place. In using sqlite3 for a project and I noticed that it's quite a bit slower to select a large number of rows as compared to doing the same via their CLI client. ~4-5x slower. A little profiling shows that the time is mostly spent in Next and Scan. For small row sets it's not a big deal but I'm testing up to ~500K rows.

I suppose my starting question is how much overhead is expected going back and forth between Go and the C API? If it's expected to be fairly fast/low-overhead, any suspicions on where I might start looking?

Here's a sketch of approximately what the code is doing:

https://gist.github.com/mhat/ded97fe58922b4af9d03f375b465c339

There are two variations, one uses the new ScanTypes while the other is cheating with a list of string pointers. Neither appreciably moved the needly forScan.

Contributor guide

Tech stack
gosqlite
Domain
database
Issue type
performance
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
4
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
1-2 days
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
stale
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
needs investigation
Prerequisites
Go programmingSQLite C APIProfiling with pprof
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
15
Research direction
Profile the Next and Scan functions in the go sqlite3 driver. Examine the Go C callback mechanism and compare with native sqlite3 CLI. The attached gist shows sample code. No linked PRs or maintainer comments found.