Ditto.
my test case proved conclusively that the concatenated KEY string in the first
example is very poor. In general columns should not contain concatenated data.
The test results show timings with a 4k page_size and default cache size. The
end result was a 1.2 to 1.1 second access timing:
Hardware:
AMD x2 3800
2 gig ram
7200 rpm ide
(linux 2.6 kernel)
Michael Ruck <[EMAIL PROTECTED]> wrote: 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
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
> #include
> #include
> #include
>
> 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));"<
>
> for(int j=0; j<100000;j++)
> {
> char* key = tmpnam(0);
> string ser =
>
> ".11111111111111111111111111111111111111111111111111111111111111111111
> 1111111111111111111111111111111111111111";
>
> fout1<<
> fout<<"insert into test values
> ('"<<<"','"<<<"',"<<"'2222222222222222222222222222222222222222
> 2222222222222222222222222222222222222222222222222222222222222222222222
> 222222222222222222222222222');"<
> }
>
> 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
> #include
> #include
> #include
>
> #include
>
> #include
> #include
>
> #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<<
> }
> fin.close();
>
> cout<<"Calling Test Now"<
> 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="<<
>
> //
> time_t end = time(0);
> cout<<"start="<<
> cout<<"end="<<
>
> return 0;
> }
>
>
>
>
>
>
>
>
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
