>> >pragma mmap_size= 512000000;
>> >Pragma page_size = 8192;
>> >Vacuum;
>> >Pragma cache_size = N;
The above code has nothing to do with the speed of retrieving records,
especially the beginning retrieve records from database.
Is this the right?
At 2015-12-23 11:00:44, "???" <2004wqg2008 at 163.com> wrote:
>>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
>_______________________________________________
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
