"Matthew L. Creech" <[email protected]> wrote in message news:[email protected]... > On Mon, Apr 27, 2009 at 10:25 PM, liubin liu > <[email protected]> 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
