"Matthew L. Creech" <mlcreech-re5jqeeqqe8avxtiumw...@public.gmane.org> wrote
in message
news:5ee96a840904271946o315df05dxb45024d5c0474...@mail.gmail.com...
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
