>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
