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 =
"111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.";
key += ptr;
fout1<<key<<endl;
unsigned long idKey = idGen(key);
fout<<"insert into test values
('"<<key<<"',"<<"'2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222',"<<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]
-----------------------------------------------------------------------------
