[ANN]sqlite3 driver using exp/sql/driver

[mattn]

Hi all.

I added 'go-sqlite3' into my github repos.

https://github.com/mattn/go-sqlite3

This is sqlite3 driver using exp/sql/driver package. The driver is implemented of 'exp/sql/Driver' completely.

I tested this driver only on windows. If you try this, please report to me. :)

Thanks.

[Brad Fitzpatrick]

Cool!  Thanks!

I see that your SQLiteStmt deals with some types that I don't think it needs to... the sql layer promises that it will only send a subset of types to the driver.

The ones I don't think you need to deal with are int and float32.  Are you actually seeing those?

I've now created this wiki page.  Feel free to edit with status or whatnot:  http://code.google.com/p/go-wiki/wiki/SQLDrivers

Did you find any bugs or omissions in the sql package?

[mattn]

> I see that your SQLiteStmt deals with some types that I don't think it needs

> to... the sql layer promises that it will only send a subset of types to the

> driver.

Yes, that's some functions in SQLiteStmt are utility functions. So it can move out of SQLiteStmt. 

> The ones I don't think you need to deal with are int and float32.  Are you

> actually seeing those?

I didn't check whether the part is passing. I just only add all possible types that I think.

> I've now created this wiki page.  Feel free to edit with status or whatnot:

> http://code.google.com/p/go-wiki/wiki/SQLDrivers

Oh, I'm grad to be added. :)

> Did you find any bugs or omissions in the sql package?

nope.

Thanks!

[mattn]

On Saturday, November 12, 2011 1:50:56 AM UTC+9, Brad Fitzpatrick wrote:

Did you find any bugs or omissions in the sql package?

One thing. It's too slow.

 

[Brad Fitzpatrick]

Could you elaborate on that?

[mattn]

> Could you elaborate on that?

Ah, It sesms that the cause is parameter of sqlite3_open_v2().

rv := C.sqlite3_open_v2(name, &db,

C.SQLITE_OPEN_FULLMUTEX |

C.SQLITE_OPEN_READWRITE |

C.SQLITE_OPEN_CREATE,

nil)

Probably, one of them make it be slow.

# cd example && make
# time ./main

real 0m28.029s

user 0m0.028s

sys 0m0.044s

This example insert just 100 records. And most of the bottleneck is calling of sqlite3_next_stmt().

Sorry, It seems that is problem of go-sqlite3.

I'll figure out this problem in later.

Thank.

[Archos]

Hi! It would be easier to test it (and greater safety) if you include
Go tests.

http://golang.org/doc/code.html#Testing

[Daniel Theophanes]

Hi mattn,

Your driver works and performs well.  Thanks!  It is your example that causes the bottleneck.  

tx, err := db.Begin()

stmt, err := tx.Prepare("insert into foo(id, name) values(?, ?)")

// code

tx.Commit()

I get

real 0m0.303s

user 0m0.008s

sys 0m0.008s

See the following for the why:

http://www.sqlite.org/faq.html#q19

Thanks again,

-Daniel

[Wei guangjing]

Nice.

FYI For windows, you can static link with libsqlite3.a by extrac
sqlite3.o from libsqlite3.a and add to CGO_OFILES then gopack it into
go-sqlite3.a. $GOROOT/misc/cgo/life/Makefile for example.

2011/11/11 mattn <matt...@gmail.com>:

[mattn]

What OS do you use?

I can't get good result on my linux box.

[Daniel Theophanes]

GNU/Linux, 64-bit.

I did modify the example code. Here's what I'm running:
https://gist.github.com/1360841

-Daniel

[mattn]

Ah, I was confused. Just fixed example.

[Guillermo Estrada]

One thing. It's too slow.

I've been doing some tests with the driver on Windows Go1 amd64. It looks pretty nice and the database/sql interface makes it pretty easy to use. But I can say the same thing about it. It's too slow. Not because of the example not making a transaction. Some time ago I made a benchmak of SQLite using D for a project I needed (http://sqlitemark.heroku.com any additional data is hugely appreciated). The D code (if someone is interested) uses binding for the sqlite library directly (calling the C funcs, and uses hardcoded binds on the values and such, pretty much close to the metal. I implemented the first part of the test (single table) using the Go driver and the examples provided. I can say its from 80% to 100% slower that the D example just on the INSERT part. The select part with the Scan method is around 400% to 600% slower. If someone can help me achieve a better performance with Go it would be HUGELY appreciated.

The D code:
https://gist.github.com/2395594

Basically it does the next:

Generate 3 float arrays of N random values. (default N = 1 million, M = 1000)
// can run more benchmarks using sqlitemark.exe VALUES TABLES as long as VALUES%TABLES == 0
Create database with 1 table
Insert N values in the table
Select N values from the table and assert that they're the same.
// This is done opening and closing the database in each step.
Create a database with M tables
Insert N values evenly distributed between the M tables
Select N values from the M tables and assert equality.

// This is done opening and closing the database in each step.
Ask user for some CPU info and post the results online.

I dunno if the linux 32 bit is working correctly but it should. Windows 32 bit D program (results online) compared to my Go program on 64 bit was around 80% to 100% slower on insert and between 400% and 600% slower on select. Multiple runs yield pretty much the same results. Thats on my Core2Quad Q6600 I uploaded to more results today using the D version for comparison.

generate: 273 [ms]
SINGLE:  single.db
    create: 103 [ms]
    insert: 12482 [ms]
    select: 6349 [ms]

Thanks for your comments and insight on the matter.

[Daniel Theophanes]

Could you post the Go code too?

[Guillermo Estrada]

Of course! It's incomplete (it just does the first part with no assertion) I stopped after checking the performance penalty. Its a little messy but I was basing it on the example on the repo.

https://gist.github.com/2395675 (Go incomplete version)

Any insight pretty appreciated :D