As has been suggested numerous times, you should split the key. The keys you've shown are very long and only differ in the last characters. You should try yourself to split the key (maybe in two or three columns) and order the key according to the change frequency. This way sqlite doesn't have to run as many equality comparisons or detects difference earlier. Your times are only as bad as they are due to the fact that most time is spent searching for equality and hitting the differences after comparing 128 characters, which match most of the time (taken from your earlier mails.)
I would suggest splitting the key at least to two columns and changing the order key according to change frequency in the columns. You won't have any data loss, you just need to concatenate the columns again in your sql queries to obtain results in the same format as previously. Mike -----Ursprüngliche Nachricht----- Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 3. Juli 2007 12:12 An: sqlite-users@sqlite.org; [EMAIL PROTECTED] Betreff: Re: [sqlite] Re: In Mem Query Performance Hi Ken, Thanks a lot. But this would require the key to be short. Will Check if this is acceptable to all as we may not be able to port old db data if the key format is changed. regards ragha **************************************************************************** ************** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! **************************************************************************** ************* ----- Original Message ----- From: Ken <[EMAIL PROTECTED]> Date: Tuesday, July 3, 2007 0:46 am Subject: Re: [sqlite] Re: In Mem Query Performance > Timings: > > Calling Test Now > TIME_ms=1210 > start=1183394494 > end=1183394496 > > Calling Test Now > TIME_ms=1164 > start=1183394504 > end=1183394505 > > (time in Ms is 1.2 and 1.1 ... ) > > Data generated using: > #include <unistd.h> > #include <iostream> > #include <stdio.h> > #include <fstream> > > using namespace std; > > > int main() > { > fstream fout("data.sql",ios::out); > fstream fout1("query.sql",ios::out); > //Schema > fout<<"create table test (name text not null, ser text not null,doid > text,primary key (name, ser));"<<endl; > > for(int j=0; j<100000;j++) > { > char* key = tmpnam(0); > string ser = > > ".11111111111111111111111111111111111111111111111111111111111111111111 > 1111111111111111111111111111111111111111"; > > fout1<<key<<endl; > fout<<"insert into test values > ('"<<key<<"','"<<ser<<"',"<<"'2222222222222222222222222222222222222222 > 2222222222222222222222222222222222222222222222222222222222222222222222 > 222222222222222222222222222');"<<endl; > } > > return 0; > } > > > ### Load data using: > sqlite3 abc.db > > pragma page_size=4096; > > begin; > > .read data.sql > > commit; > > ============================= Performance code ================== > //Compile:g++ -g main.cpp -I. -L. -lsqlite3 -lpthread > > #include <iostream> > #include <fstream> > #include <time.h> > #include <list> > > #include <sys/timeb.h> > > #include <string> > #include <unistd.h> > > #include "sqlite3.h" > > using namespace std; > > int main() > { > struct timeb startTime; > struct timeb endTime; > double ms; > > > sqlite3* db; > int ret = sqlite3_open("abc.db",&db); > > char* errmsg; > char buf[1024]; > > sprintf(buf,"create temporary table ttest as select * from test; "); > ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg); > if (ret != SQLITE_OK) { > printf("failed to create temp table: %d\n", ret); > exit(ret); > } > > sprintf(buf,"create index tidx on ttest (name, ser);"); > ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg); > if (ret != SQLITE_OK) { > printf("failed to create index: %d\n", ret); > exit(ret); > } > > > //read all the srvName from file and store it in arr and query. > fstream fin("query.sql",ios::in); > string data[100000]; > > for(int j=0;!fin.eof();j++) > { > fin>>data[j]; > //cout<<data[j]<<endl; > } > fin.close(); > > cout<<"Calling Test Now"<<endl; > sleep(1); > > //Now Query Data. > time_t start = time(0); > ftime(&startTime); > > > > char* szError=0; > const char* szTail=0; > sqlite3_stmt* pVM; > > sprintf(buf,"select * from ttest where name = ?" ); > ret = sqlite3_prepare_v2(db, buf, -1, &pVM, &szTail); > if (ret != SQLITE_OK) exit(ret); > > for(int k=0;k<100000;k++) > { > ret = sqlite3_bind_text(pVM, 1, data[k].c_str(), > data[k].length(), SQLITE_STATIC); > ret = sqlite3_step(pVM); > ret = sqlite3_reset(pVM); > } > > > > ret = sqlite3_finalize(pVM); > ftime(&endTime); > ms = ( > ((endTime.time - startTime.time) * 1000) + > (endTime.millitm - startTime.millitm) > ) ; > > cout<<"TIME_ms="<<ms<<endl; > > // > time_t end = time(0); > cout<<"start="<<start<<endl; > cout<<"end="<<end<<endl; > > return 0; > } > > > > > > > > ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------