"Matthew L. Creech" <mlcreech-re5jqeeqqe8avxtiumw...@public.gmane.org> wrote
in message
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu
> <7101227-k+ct0dcb...@public.gmane.org> wrote:
>> thanks
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
> -- 
> Matthew L. Creech

If he wants go faster he can use BEGIN TRANSACTION (EXCLUSIVE or other)
before the 100,000 iterations.
In this way sqlite doesn't lock the database file 100.000 times, but only 1
I don't know if he can use this trick, but maybe he can group 10, 50 or 100
select into a TRANSACTION.

Giacomo Mussati

> sqlite-users mailing list
> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

sqlite-users mailing list

Reply via email to