>How long does it take to retrieve one record from the database? >How long do you want it to take?
The faster,the better. At 2015-12-23 10:58:13, "Keith Medcalf" <kmedcalf at dessus.com> wrote: > >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 > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
