How long does it take to retrieve one record from the database? How long do you want it to take?
> -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of ??? > Sent: Tuesday, 22 December, 2015 19:49 > To: SQLite mailing list > Subject: Re: [sqlite] about compile configure > > >pragma mmap_size= 512000000; > >Pragma page_size = 8192; > >Vacuum; > >Pragma cache_size = N; > > The testing result shows that the above pragma statements could not > improve the speed of retrieving records from data base. > > Best regards > wqg > > At 2015-12-21 18:29:37, "Quan Yong Zhai" <qydi at msn.com> wrote: > >pragma mmap_size= 512000000; > >Pragma page_size = 8192; > >Vacuum; > >Pragma cache_size = N; > >________________________________ > >???: ???<mailto:2004wqg2008 at 163.com> > >????: ?2015/?12/?21 18:08 > >???: SQLite mailing list<mailto:sqlite-users at mailinglists.sqlite.org> > >??: Re: [sqlite] about compile configure > > > > The SQL statement is so easy. > > the table create statement as following: > > CREATE TABLE poiTable (poiId INTEGER NOT NULL, versionId INTEGER NOT > NULL, regionId INTEGER , postalCode TEXT , phone TEXT , attrBitMask > INTEGER , attributeBlob BLOB , primary key (poiId)); > > So the poiId is equal to the rowid. > > > > such as : select * from poiTable where poiId = ... ; > > And execute the sql by sqlite3_prepare_v2?sqlite3_step? > > how could I improve the performance? > > > > > > > > > > > > > > > > > > > >At 2015-12-21 17:15:56, "???" <2004wqg2008 at 163.com> wrote: > >>What Simon said is very helpful for me. Thank you very much. > >>I only want to improve the speed of reading data from data base. Do not > do insert?update and so on. > >> > >>I will try the following suggustion. > >>PRAGMA synchronous = OFF > >> > >>Best regards > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >>At 2015-12-21 17:03:13, "Simon Slavin" <slavins at bigfraud.org> wrote: > >>> > >>>On 21 Dec 2015, at 6:19am, ??? <2004wqg2008 at 163.com> wrote: > >>> > >>>> The meaning of "how to use sqlite_table" is that I guess the > sqlite_table may have contained some information which could help to > improve speed. > >>>> I am not meaning to modify the data structure of sqlite_master. > >>> > >>>There is nothing you can do with sqlite_table to improve speed. Unless > you have a very unusual setup there is nothing you can do with compilation > options to improve speed. > >>> > >>>Since you say you are not using multi-threading or multi-process, you > might like to read the documentation for > >>> > >>>PRAGMA synchronous = OFF > >>> > >>>This might increase speed for you. However it also means that if your > computer loses power or crashes while the database is open, you will lose > more new data. > >>> > >>><https://www.sqlite.org/pragma.html> > >>> > >>>However a big increase in speed comes from correct use of indexes. If > you have any SQL commands which include WHERE or ORDER BY, then these will > execute faster if you have an ideal index on the table they use. This can > affect INSERT and UPDATE and DELETE FROM commands. If you want help with > this you must post your SQL commands here. > >>> > >>>Another big increase in speed can come from correctly using > transactions. When you are making changes to your database it is > finishing the transaction with END or COMMIT which takes most of the time. > So if you have many INSERT commands then > >>> > >>>INSERT ... > >>>INSERT ... > >>>INSERT ... > >>> > >>>is slow but > >>> > >>>BEGIN > >>>INSERT ... > >>>INSERT ... > >>>INSERT ... > >>>COMMIT > >>> > >>>can be much faster. This can affect INSERT and UPDATE and DELETE > commands. > >>> > >>>Simon. > >>>_______________________________________________ > >>>sqlite-users mailing list > >>>sqlite-users at mailinglists.sqlite.org > >>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >>_______________________________________________ > >>sqlite-users mailing list > >>sqlite-users at mailinglists.sqlite.org > >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >_______________________________________________ > >sqlite-users mailing list > >sqlite-users at mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >_______________________________________________ > >sqlite-users mailing list > >sqlite-users at mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
