"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
