That's not what I meant. I meant the following: Take the key in the format 'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk490' and split it into two(66 characters each) /three (44 characters each) colums of equal length, e.g. key0_0 = 'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk', key0_1 = 'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk', key0_2 = 'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk490'. If you can then reduce the select to compare only key0_2, you reduced the work required to find a row by 2/3. If you could even reverse the keys, you'd benefit even more - as the difference would occur in the first few bytes. (All this is just guessing based on your examples - without knowing the actual structure of your data.)
So you would have 3 columns instead of one. The ones, which change most frequently will go into the primary key as it benefits most by not having duplicates. Once you've done that you should be able to reduce your query times fundamentally by only applying a comparison to a certain column. If that doesn't help and you still have a lot of equality in your keys, I'd throw them out of the actual data and put them into some kind of dictionary table and use joins to map them together. Given your current examples of your dataset, there isn't much that can be done without optimizing the data for storage and lookup - this isn't something where a set of compiler flags will help a lot. The optimizers are smart, but they can't correct bad design in the first place. You have to reduce the actual code being executed by the CPU (e.g. bytes to compare) to get faster. Mike -----Ursprüngliche Nachricht----- Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 3. Juli 2007 18:21 An: sqlite-users@sqlite.org; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Betreff: Re: [sqlite] Re: In Mem Query Performance Hi, As said, i used a seperate ID (hash) but it made the performance very bad. Now it takes minutes.[Version: 3.4.0] regards ragha unsigned long idGen(const string & id) { const char* __s = id.c_str(); unsigned long __h = 0; for ( ; *__s; ++__s) { __h = 5*__h + *__s; } return size_t(__h); } gen: program int main() { fstream fout("data.sql",ios::out); fstream fout1("query.sql",ios::out); fout<<"Begin;"<<endl; fout<<"create table test (serName text,doid text,id integer,primary key (serName,doid,id));"<<endl; fout<<"create index serNameIdx1 on test(id);"<<endl; for(int j=0; j<100000;j++) { char* ptr = tmpnam(0); string key = "111111111111111111111111111111111111111111111111111111111111111111111111111 1111111111111111111111111111111111111111111111111111111111111111111111111111 11111111111111111111111."; key += ptr; fout1<<key<<endl; unsigned long idKey = idGen(key); fout<<"insert into test values ('"<<key<<"',"<<"'2222222222222222222222222222222222222222222222222222222222 2222222222222222222222222222222222222222222222222222222222222222222222222222 2222222222222222222222222222222222222222222222222222222222222222222222222222 2222222222222222222',"<<idKey<<");"<<endl; } fout<<"commit;"<<endl; return 0; } Perf Prg: int main() { sqlite3* db; int ret = sqlite3_open("abc",&db); char* errmsg; char buf[1024]; //read all the srvName from file and store it in arr and query. fstream fin("query.sql",ios::in); string data[100000]; long long idKeyArr[100000]; for(int j=0;!fin.eof();j++) { fin>>data[j]; //cout<<data[j]<<endl; idKeyArr[j] = idGen(data[j]); } fin.close(); sprintf(buf,"create temporary table ttest(column1 text,column2 text,id integer,primary key (column1,column2,id));"); ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg); sprintf(buf,"create index idx1 on ttest(id));"); ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg); sprintf(buf,"insert into ttest select * from test;"); ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg); sleep(5); cout<<"Calling Test Now"<<endl; list<string> lst; char* szError=0; const char* szTail=0; sqlite3_stmt* pVM; string clauseSt; //memset(buf,0,1024); //sprintf(buf,"select doid from test where id = :xyz"); sprintf(buf,"select column2 from ttest where id = :xyz"); ret = sqlite3_prepare(db, buf, strlen(buf), &pVM, &szTail); cout<<__LINE__<<" ret="<<ret<<endl; long long idKey = 0; //Now Query Data. time_t start = time(0); for(int k=0;k<100000 ;k++) { //idKey = idGen(data[k]); ret = sqlite3_bind_int64(pVM, sqlite3_bind_parameter_index(pVM, ":xyz"), idKeyArr[k]); ret = sqlite3_step(pVM); //cout<<"data="<<sqlite3_column_text(pVM,0)<<endl; sqlite3_reset(pVM); } time_t end = time(0); cout<<"start="<<start<<endl; cout<<"end="<<end<<endl; cout<<"diff="<<end - start<<endl; return 0; } **************************************************************************** ************** 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 10:23 pm Subject: Re: [sqlite] Re: In Mem Query Performance > Just a thought regarding this key issue. > > enhancement for sqlite, enable a "reverse Key" indexing method. > Would this be a simple change? > > Ken > > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi > > >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. > > > > Perhaps the key can be modified only for comparation. You store > the > key as you want, but before compare it do a rle compression. You > can > store the rle compressed key in database too. Note that rle is a > one-to- > one transform, that is one key has only one compressed key and > one > compressed key has only one key. Working that way you can compare > 200,1,1,2 with 199,1,2,2 (four bytes) instead 1111..(196 > '1')..11112 > with 1111..(195 '1')..111122. > > > HTH > > >regards > >ragha > > > ------------------------------------------------------------------- > ---------- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------- > ---------- > > > ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
