[sqlite] ABOUT ROWID

2015-12-11 Thread
Hi , Everyone, When I use methodone : select * from table_name where rowid = somenumber ? When I use methodtwo : select * from table_name where ordinaryid = somenumber ? (ordinary is not index) The table_name have at least two columns. I have some questions, as follows: 1.The

[sqlite] ABOUT ROWID

2015-12-11 Thread
HI,Hick Gunter, Thanks for you reply. I find a strange problem. For example, tableA contains two columns: implicit rowid, A_id. we create index on A_id. firstly, we used rowid to select the row, cost about 400 seconds; secondly, we used A_id to select the row,

[sqlite] ABOUT ROWID

2015-12-11 Thread
Because of I want to test some funtions of the SQLite. According to Clemens said, the testing result may be influenced by cache. How could avoid the influence of cache? Is there funtion can eliminate the influence? best regards. WQG At 2015-12-11 15:40:56,

[sqlite] ABOUT ROWID

2015-12-11 Thread
1.Reboot computer 2.Shut down computer ,and then start computer the two method above , can also solve the cache perfectly. the testing computer is dell. At 2015-12-11 16:22:44, "Clemens Ladisch" wrote: >Hick Gunter wrote: >> 2) run each query in a new process (so each one will need to read

[sqlite] ABOUT ROWID

2015-12-11 Thread
1.Reboot computer 2.Shut down computer ,and then start computer the two method above , can not also solve the cache perfectly. the testing computer is dell. At 2015-12-11 16:29:09, "???" <2004wqg2008 at 163.com> wrote: >1.Reboot computer > 2.Shut down computer ,and then start computer >

[sqlite] ABOUT ROWID

2015-12-11 Thread
Thanks for your help! What you have said makes sense. I will try it! As soon as possible, I will tell you the testing result on Monday.. At 2015-12-11 20:16:24, "Keith Medcalf" wrote: > >Actually, you need to pull the power plug after you shut it down for more than >30 seconds,

[sqlite] Very Strange and Interesting Problem

2015-12-14 Thread
hi, every one. Here is a very strange and interesting problem. I used the following SQL to create the table teacher. CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY, versionId INTEGER NOT NULL,

[sqlite] 答复: Very Strange and Interesting Problem

2015-12-14 Thread
https://www.sqlite.org/lang_createtable.html#rowid The above link websit is very valuable. Thanks for Quan Yong Zhai. At 2015-12-14 19:08:59, "Quan Yong Zhai" wrote: >https://www.sqlite.org/lang_createtable.html#rowid > >???: ???

[sqlite] Is rowid the fastest?

2015-12-14 Thread
hi, create a table using the following sql. CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); So the x is the alias of the rowid. Retrieving records by rowid around twice as fast as other indexs values. Because of the x is the alias of rowid, so retrieving records by x is also as

[sqlite] Is rowid the fastest?

2015-12-14 Thread
You said that "You are probably falling into the cache effect trap again. There is no point in indexing on the primary key, it only wastes space and CPU cycles ". I do not agree with you. let me tell you why. Before I retrieve by index which created for primary key. The speed of retrieve by

[sqlite] Is rowid the fastest?

2015-12-15 Thread
When it revert back after dropping the index. The speed does not become slower. At 2015-12-14 21:35:03, "Hick Gunter" wrote: >Does it revert back to slower speed after dropping the index? >Can you compare the EXPLAIN output produced with and without the index? > >There is no difference on

[sqlite] Fastest read?

2015-12-15 Thread
hi,all I want to improve the speed of the retrieve records. I try so many methods. and find the result is not good. Such as retrieve by rowid, index and so on. Is there any other method which can improve the retrieve speed? Best regards. WQG

[sqlite] get blob colums values by sqlite3_get_table?

2015-12-15 Thread
int sqlite3_get_table( sqlite3 *db, /* An open database */ const char *zSql, /* SQL to be evaluated */ char ***pazResult,/* Results of the query */ int *pnRow, /* Number of result rows written here */ int *pnColumn,/* Number of result columns written

[sqlite] about attach database

2015-12-16 Thread
hi,all There are two ways to open a database. 1.sqlite3_open 2.ATTACH DATABASE Because there are so many data base. So we used attach database to open them. But the efficiency of the programming is not ideal. which one is faster? Is the efficiency between

[sqlite] about attach database

2015-12-16 Thread
I mean only compare the two ways of get the database handl. 1.sqlite3_open 2.ATTACH DATABASE Do not consider the next operation, such as select,update and so on. At 2015-12-16 10:51:31, "Richard Hipp" wrote: >On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: >> hi,all >> There

[sqlite] about attach database

2015-12-16 Thread
After testing the Sqlite3_open and ATTACH DATABASE, I found that the attach database is slower than sqlite3_open. there is attachment after the mail which includ the speed information ( millisecond ). At 2015-12-16 10:59:27, "Richard Hipp" wrote: >On 12/15/15, ???

[sqlite] about attach database

2015-12-16 Thread
After testing the Sqlite3_open and ATTACH DATABASE, I found that the attach database is slower than sqlite3_open. there is attachment after the mail which includ the speed information ( millisecond ).

[sqlite] about attach database

2015-12-16 Thread
Thanks for everyone. You are right. According to you help, I understand the problem. Just open or attach database , open operation is faster than attach database. if add a query statement after open or attach database. The time which they cost almost the same. Best regards. what Dominique

[sqlite] batch or one by one?

2015-12-17 Thread
hi,all There is an interesting phenomenon.As you know, SQLite can retrieve records by batch or one by one. 1.Retrieve by batch such as sqlite3_get_table. 2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step together. Using the above two methods to retrieve

[sqlite] batch or one by one?

2015-12-17 Thread
Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and sqlite3_step together. In fact. At 2015-12-17 14:48:18, "Igor Tandetnik" wrote: >On 12/16/2015 11:24 PM, ??? wrote: >> There is an interesting phenomenon.As you know, SQLite can retrieve >> records by batch or

[sqlite] compile configure

2015-12-17 Thread
hi,all When I compile the sqlite3 source, will I select difference parameter effect the performance of the sqlite funtion such as speed?

[sqlite] about compile configure

2015-12-21 Thread
hi,all When we compile the sqlite source code, there are so many configuration, such as SQLITE_DEFAULT_FILE_FORMAT=4; SQLITE_MAX_ATTACHED=30; SQLITE_ENABLE_COLUMN_METADATA; SQLITE_ENABLE_FTS4; My question is that could different configuration influence

[sqlite] about compile configure

2015-12-21 Thread
How about the following settings: SQLITE_DEFAULT_FILE_FORMAT=4; SQLITE_MAX_ATTACHED=30; SQLITE_ENABLE_COLUMN_METADATA; SQLITE_ENABLE_FTS4; SQLITE_ENABLE_FTS4_PARENTHESIS; SQLITE_ENABLE_RTREE; SQLITE_THREADSAFE=2; NDS_ENABLE_ZLIB; NDS_ENABLE_AES; NDS_ENABLE_COLLATIONS At 2015-12-21 11:42:14,

[sqlite] about compile configure

2015-12-21 Thread
Thank you very much. At 2015-12-21 11:54:13, "Simon Slavin" wrote: > >On 21 Dec 2015, at 3:50am, ??? <2004wqg2008 at 163.com> wrote: > >> How about the following settings: > >Read the documentation for those settings. Given what they do which ones, if >any, do you think will influence

[sqlite] about compile configure

2015-12-21 Thread
hi,Simon. The reason why I asked the question is that I want to improve the spped, although the current SQLite is not too slow. But the speed is not very ideal. So I still try any methods to improve the performance of SQLite. I will try the following aspects: first method ,

[sqlite] about compile configure

2015-12-21 Thread
>Is your speed problem with a SELECT ? If so, can you past your SELECT into a >message for us ? The speed problem has nothing to do with a SELECT >Are you using multiple threads or multiple processes ? NO At 2015-12-21 12:53:09, "Simon Slavin" wrote: > >On 21 Dec 2015, at

[sqlite] about compile configure

2015-12-21 Thread
>What do you mean "how to use sqlite_table"? Do you mean how to design >effective database tables and query them efficiently using SQL? 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

[sqlite] about compile configure

2015-12-21 Thread
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" wrote: >

[sqlite] about compile configure

2015-12-21 Thread
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

[sqlite] 答复: about compile configure

2015-12-21 Thread
>pragma mmap_size = 51200; >Pragma page_size = 8192; The number 51200 and 8192, how do you compute them? >Vacuum; >Pragma cache_size = N; What number is N? At 2015-12-21 18:29:37, "Quan Yong Zhai" wrote: >pragma mmap_size= 51200; >Pragma page_size = 8192; >Vacuum;

[sqlite] about compile configure

2015-12-22 Thread
about sqlite3_exec(m_pDB, "PRAGMA synchronous = OFF;", 0,0,0); The testing shows that It can not improve the speed of retrieving records from data base. Thank Simon all the same. At 2015-12-21 18:20:49, "Simon Slavin" wrote: > >On 21 Dec 2015, at 10:07am, ??? <2004wqg2008 at

[sqlite] about compile configure

2015-12-23 Thread
>pragma mmap_size= 51200; >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" wrote: >pragma

[sqlite] about compile configure

2015-12-23 Thread
>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" wrote: > >How long does it take to retrieve one record from the database? >How long do you want it to take? > >> -Original

[sqlite] about compile configure

2015-12-23 Thread
>> >pragma mmap_size= 51200; >> >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, "???"

[sqlite] about compile configure

2015-12-23 Thread
>Setting the mmap_size will normally help read performance a lot. >However, if you are reading from a ZIPVFS database, the extra layer of >decryption and decompression that sits in between the disk and your >application will render the mmap_size mote - it won't matter. Thanks very much. What

[sqlite] about compile configure

2015-12-23 Thread
Thank D. Richard Hipp very much. I can understand what you said. That is very helpful for me. Best regards At 2015-12-23 12:34:33, "Richard Hipp" wrote: >On 12/22/15, ??? <2004wqg2008 at 163.com> wrote: >>What do you mean of "render the mmap_size mote "? > >The "PRAGMA mmap_size"

[sqlite] about compile configure

2015-12-23 Thread
I know the reason why "PRAGMA mmap_size" could not improve the speed of retrieving records from ZIPVFS DB. However, I find sqlite3_soft_heap_limit64() , cache_szie and so on also which could not help improve the speed. Is all because of the ZIPVFS? At 2015-12-23 12:34:33, "Richard

[sqlite] In the case of ZIPVFS

2015-12-23 Thread
HI,all SQLite retrieves the compressed records is slower than the uncompressed records about 30%. How can improve the problem? and anyone any suggustion? best regards wqg

[sqlite] In the case of ZIPVFS

2015-12-24 Thread
Thank you very much. At 2015-12-23 17:32:26, "Richard Hipp" wrote: >On 12/23/15, ??? <2004wqg2008 at 163.com> wrote: >> HI,all >> SQLite retrieves the compressed records is slower than the uncompressed >> records about 30%. >> How can improve the problem? and anyone any suggustion? >>

[sqlite] HELP sqlite3 used in vxworks has someproblem?

2014-08-02 Thread
hi , Can Sqlite3.c and sqlite.h be compiled in Workbench3.2 for Vxworks6.8 ? When I compile them , there have so many problems . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem?

2014-08-25 Thread
4-08-12 08:10:13,"Jan Nijtmans" <jan.nijtm...@gmail.com> 写道: 2014-08-03 9:56 GMT+02:00 Jan Nijtmans <jan.nijtm...@gmail.com>: 2014-08-02 16:00 GMT+02:00 王庆刚 <2004wqg2...@163.com>: > hi , Can Sqlite3.c and sqlite.h be compiled in Workbench3.2 for > Vxworks6.8

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem?

2014-08-26 Thread
that is your problem. Regards Andy Ling From: 王庆刚 [2004wqg2...@163.com] Sent: 25 August 2014 13:13 To:sqlite-users@sqlite.org; Andy Ling; Jan Nijtmans Subject: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem? hi I modified the code sqlite3.c according to you method, as fo

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem?

2014-08-27 Thread
. What did you have to do? Are there any more changes that need feeding back into the source? Regards Andy Ling From:王庆刚 [mailto:2004wqg2...@163.com] Sent: 26 August 2014 13:44 To: Andy Ling Cc:sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: Re:Re: [sqlite] HELP sqlite3 used

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem?

2014-08-28 Thread
be good to find out what is causing the disk I/O error. Did you try putting some debug in unixUnlink? Regards Andy Ling From:王庆刚 [mailto:2004wqg2...@163.com] Sent: 27 August 2014 16:17 To: Andy Ling Cc:sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: [sqlite] HELP sqlite3 used

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem?

2014-08-29 Thread
,22:42,Andy Ling <andy.l...@quantel.com> 写道: Sorry, I meant unixDelete My guess is that because you are using the host filing system vxWorks will be setting yet another error code for a file that doesn’t exist. So it will need another check adding to unixDelete Regards Andy Ling

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

2014-09-02 Thread
firstly : Even if I used #if OS_VXWORKS || osAccess(zPath,0) != 0 #endif or I used #if OS_VXWORKS }else if(errno == 0x380003 || errno == 13 ) rc = SQLITE_IOERR_DELETE_NOENT: #endif Without the SQLITE_ENABLE_LOCKING_STYLE compile option, I tried the above two

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

2014-09-02 Thread
Thank Andy Ling. best wishes. regards Wang Qinggang. At 2014-09-02 09:45:47, "Andy Ling" wrote: >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of ??? >> Sent: 02 September 2014

[sqlite] about qnx embedded operating system

2015-11-15 Thread
Hi, everyone, The same code about sqlite, running on qnx is lower efficency than Windows or linux. I do not know why. best regards.

[sqlite] tesing mail

2016-02-17 Thread
testing mail

[sqlite] A question

2016-02-17 Thread
I have a question: there are two tables: CREATE TABLE poiTable ( poiId INTEGER NOT NULL, catId INTEGER NOT NULL, mortonCode INTEGER NOT NULL, versionId INTEGER NOT NULL, iconSetId INTEGER , catIconSetId INTEGER , brandIconSetId INTEGER , regionId INTEGER , attrBitMask INTEGER ,

[sqlite] About vacuum

2016-01-04 Thread
hi,all Before I retrieve the record from the sqlite data base, I execute vacuum function. Can this method improve the speed of retrieve the records from sqlite data base? best regards. WQG