sqlite3: reduce C to Go string conversions in SQLiteConn.{query,exec}

[charlievieth]

This commit fixes an issue where SQLiteConn.{exec,query} would use an exponential amount of memory when processing a prepared statement that consisted of multiple SQL statements ("stmt 1; stmt 2; ...").

Previously both exec/query used SQLiteConn.prepare() which converts the "tail" pointer set by sqlite3_prepare_v2() back into a Go string, which then has to be converted back to a C string for the next call to prepare() (assuming there are multiple SQL statements in the provided query).

This commit fixes this by changing both exec and query to use the returned "tail" pointer for the next call to exec/query.

It also changes prepare() to use pointer arithmetic to calculate the offset of the remaining "tail" portion of the query as a substring of the original query. This saves a call to C.GoString() and an allocation.

Benchmarks:

goos: darwin
goarch: arm64
pkg: github.com/mattn/go-sqlite3
                            │ base.10.txt  │             new.10.txt              │
                            │    sec/op    │   sec/op     vs base                │
Suite/BenchmarkExec-10         1.351µ ± 1%   1.247µ ± 1%   -7.74% (p=0.000 n=10)
Suite/BenchmarkQuery-10        3.830µ ± 1%   3.558µ ± 1%   -7.11% (p=0.000 n=10)
Suite/BenchmarkParams-10       4.221µ ± 0%   4.228µ ± 1%        ~ (p=1.000 n=10)
Suite/BenchmarkStmt-10         2.906µ ± 1%   2.864µ ± 1%   -1.45% (p=0.001 n=10)
Suite/BenchmarkRows-10         149.1µ ± 4%   148.2µ ± 1%   -0.61% (p=0.023 n=10)
Suite/BenchmarkStmtRows-10     147.3µ ± 1%   145.6µ ± 0%   -1.16% (p=0.000 n=10)
Suite/BenchmarkExecStep-10    1898.9µ ± 3%   889.0µ ± 1%  -53.18% (p=0.000 n=10)
Suite/BenchmarkQueryStep-10   1848.0µ ± 1%   894.6µ ± 1%  -51.59% (p=0.000 n=10)
geomean                        38.56µ        31.30µ       -18.84%

                            │  base.10.txt   │               new.10.txt               │
                            │      B/op      │     B/op      vs base                  │
Suite/BenchmarkExec-10            184.0 ± 0%     176.0 ± 0%   -4.35% (p=0.000 n=10)
Suite/BenchmarkQuery-10           864.0 ± 0%     856.0 ± 0%   -0.93% (p=0.000 n=10)
Suite/BenchmarkParams-10        1.289Ki ± 0%   1.281Ki ± 0%   -0.61% (p=0.000 n=10)
Suite/BenchmarkStmt-10          1.078Ki ± 0%   1.078Ki ± 0%        ~ (p=1.000 n=10) ¹
Suite/BenchmarkRows-10          34.45Ki ± 0%   34.45Ki ± 0%   -0.02% (p=0.000 n=10)
Suite/BenchmarkStmtRows-10      34.40Ki ± 0%   34.40Ki ± 0%        ~ (p=1.000 n=10) ¹
Suite/BenchmarkExecStep-10    5334.61Ki ± 0%   70.41Ki ± 0%  -98.68% (p=0.000 n=10)
Suite/BenchmarkQueryStep-10    5397.4Ki ± 0%   133.2Ki ± 0%  -97.53% (p=0.000 n=10)
geomean                         17.06Ki        6.208Ki       -63.62%
¹ all samples are equal

                            │ base.10.txt  │              new.10.txt               │
                            │  allocs/op   │  allocs/op   vs base                  │
Suite/BenchmarkExec-10          13.00 ± 0%    12.00 ± 0%   -7.69% (p=0.000 n=10)
Suite/BenchmarkQuery-10         46.00 ± 0%    45.00 ± 0%   -2.17% (p=0.000 n=10)
Suite/BenchmarkParams-10        54.00 ± 0%    53.00 ± 0%   -1.85% (p=0.000 n=10)
Suite/BenchmarkStmt-10          49.00 ± 0%    49.00 ± 0%        ~ (p=1.000 n=10) ¹
Suite/BenchmarkRows-10         2.042k ± 0%   2.041k ± 0%   -0.05% (p=0.000 n=10)
Suite/BenchmarkStmtRows-10     2.038k ± 0%   2.038k ± 0%        ~ (p=1.000 n=10) ¹
Suite/BenchmarkExecStep-10    13.000k ± 0%   8.004k ± 0%  -38.43% (p=0.000 n=10)
Suite/BenchmarkQueryStep-10   11.013k ± 0%   6.017k ± 0%  -45.36% (p=0.000 n=10)
geomean                         418.6         359.8       -14.04%
¹ all samples are equal

[codecov-commenter]

Codecov Report

Patch coverage: 70.90% and project coverage change: +0.68 :tada:

Comparison is base (edc3bb6) 46.72% compared to head (1c1a89a) 47.40%.

:mega: This organization is not using Codecov’s GitHub App Integration. We recommend you install it so Codecov can continue to function properly for your repositories. Learn more

Additional details and impacted files

@@            Coverage Diff             @@
##           master    #1133      +/-   ##
==========================================
+ Coverage   46.72%   47.40%   +0.68%     
==========================================
  Files          12       12              
  Lines        1526     1542      +16     
==========================================
+ Hits          713      731      +18     
  Misses        671      671              
+ Partials      142      140       -2     

Impacted Files	Coverage Δ
sqlite3.go	53.89% <70.90%> (+1.03%)	:arrow_up:

Help us with your feedback. Take ten seconds to tell us how you rate us. Have a feature suggestion? Share it here.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Do you have feedback about the report comment? Let us know in this issue.

[charlievieth]

~It would also be great to get https://github.com/mattn/go-sqlite3/pull/1130 merged since it makes working on this library with go1.19+ much nicer.~

This was fixed by https://github.com/mattn/go-sqlite3/pull/1208

[charlievieth]

@rittneje could you take another look at this PR?

[charlievieth]

@mattn would you or someone else be able to take a look at this PR since it solves a performance issue and a bug https://github.com/mattn/go-sqlite3/issues/950?

[charlievieth]

@rittneje I created https://github.com/mattn/go-sqlite3/pull/1296 which only addresses the issues with SQLiteConn.Exec. I will eventually create another PR that separately addresses the issues here with Query (but that preserves its current behavior) - once that PR is posted I will close this one.