Re: [sqlite] Re: In Mem Query Performance

[RaghavendraK 70574]

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<10;j++)
 {
char* ptr = tmpnam(0); 
 string key = 
"11.";
 key += ptr;
 fout1<<key<<endl;
 unsigned long idKey = idGen(key);
   fout<<"insert into test values 
('"<<key<<"',"<<"'2',"<<idKey<<");"<<endl;



 }
  fout<<"commit;"<<endl;


 return 0;
}

Perf Prg:
  int main()
 {
   sqlite3* db;
   int ret = sqlite3_open("abc",);
 
   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[10];
   long long idKeyArr[10];

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,);

   sprintf(buf,"create index idx1 on ttest(id));");   
   ret = sqlite3_exec(db , buf, NULL ,NULL,);

   sprintf(buf,"insert into ttest select * from test;");
   ret = sqlite3_exec(db , buf, NULL ,NULL,);


sleep(5);
 
cout<<"Calling Test Now"<<endl;

list 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), , );  
cout<<__LINE__<<" ret="<<ret<<endl;

long long idKey = 0;

//Now Query Data.
time_t start = time(0);  
 
for(int k=0;k<10 ;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="<
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 ..(196 
> '1')..2 
> with ..(195 '1')..22.
> 
> 
> HTH
> 
> >regards
> >ragha
> 
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: In Mem Query Performance

[Ken]

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 ..(196 '1')..2 
with ..(195 '1')..22.


HTH

>regards
>ragha


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: In Mem Query Performance

[[EMAIL PROTECTED]]

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 ..(196 '1')..2 
with ..(195 '1')..22.


HTH

>regards
>ragha


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: In Mem Query Performance

[RaghavendraK 70574]

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 
> #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));"<<endl;
> 
>  for(int j=0; j<10;j++)
>  {
> char* key = tmpnam(0);
>  string ser =
> 
> ".";
>  
> 
>  fout1<<key<<endl;
> fout<<"insert into test values 
> ('"<<key<<"','"<<ser<<"',"<<"'2');"<<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 
> #include 
> #include 
> #include 
> 
> #include 
> 
> #include 
> #include 
> 
> #include "sqlite3.h"
> 
> using namespace std;
> 
> int main()
> {
>  struct timeb  startTime;
>  struct timeb  endTime;
>  doublems;
> 
>   
>   sqlite3* db;
>   int ret = sqlite3_open("abc.db",);
> 
>   char* errmsg;
>   char buf[1024];
> 
>   sprintf(buf,"create temporary table ttest as select * from 
> test; ");
>ret = sqlite3_exec(db , buf, NULL ,NULL,);
>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,);
>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[10];
> 
>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();
> 
> 
> 
>char* szError=0;
>const char* szTail=0;
>sqlite3_stmt* pVM;
> 
>sprintf(buf,"select * from ttest where name = ?" );
>ret = sqlite3_prepare_v2(db, buf, -1, , );
>if (ret != SQLITE_OK) exit(ret);
> 
>for(int k=0;k<10;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();
> 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]
-

Re: [sqlite] Re: In Mem Query Performance

[Ken]

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));"< 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;
  doublems;
 
   
   sqlite3* db;
   int ret = sqlite3_open("abc.db",);
 
   char* errmsg;
   char buf[1024];
 
   sprintf(buf,"create temporary table ttest as select * from test; ");
ret = sqlite3_exec(db , buf, NULL ,NULL,);
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,);
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[10];
 
for(int j=0;!fin.eof();j++)
{
   fin>>data[j];
   //cout<Re: [sqlite] Re: In Mem Query Performance


2007-07-02

Thread
Eduardo Morras


At 12:16 02/07/2007, you wrote:

Hi,

Pls notify me if it can be brought down to 1 sec.
There is no mem constraint.


I don't know x86 asm or gcc for x86, but in powerpc asm, if i use 
altivec libraries (libfreevec, f.e.) for string comparations, mem 
moves/copies, sorts etc... i get from x2 to x16 speed improvements 
(and more). Perhaps changing the comparation routines with a 
sse/sse2/3dNow or similar asm code you can get a speedup boost.



regards
ragha


**
This document represent my ideas.They are original from me.
It's forbidden think the same than me, without previous payment.
If you agree me, PAYME. LOOKOUT: Lawyer BITES. 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






Re: [sqlite] Re: In Mem Query Performance


2007-07-02

Thread
RaghavendraK 70574

Hi,

Pls notify me if it can be brought down to 1 sec.
There is no mem constraint.

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: RaghavendraK 70574 <[EMAIL PROTECTED]>
Date: Sunday, July 1, 2007 11:58 pm
Subject: Re: [sqlite] Re: In Mem Query Performance

> Hi Joe/John
> 
> Appreciate your reply.
> Result: with your suggestions it is to down to 3 secs.
> But i need to get it to 1sec. Possible?
> 
> Here are the findings,
> 1) With Mem db default compile options and with suggestions(bind+ 
> index) you have
> provided to use bind performance stands at 10-11secs.
> 2) With File db default compile options it stands at 13-14secs.
> 3) begin and commit seems to have no affect on the query.
>   Seperate index on mem table seems to have no affect.
> 4) Earlier Joe had suggetsed to make changes to page size,with that
> it is down to 3 secs.[Along with your suggestions]
>  Compile Option changes:
>  ./configure --enable-threadsafe
> 
>  BCC = gcc -g -O2
> 
>  # C Compile and options for use in building executables that
>  # will run on the target platform.  (BCC and TCC are usually the
>  # same unless your are cross-compiling.)
>  #
>  TCC = gcc -g -O2 -I. -I${TOP}/src
> 
>  # Define -DNDEBUG to compile without debugging (i.e., for 
> production usage)
>  # Omitting the define will cause extra debugging code to be 
> inserted and
>  # includes extra comments when "EXPLAIN stmt" is used.
>  #
>  TCC += -DNDEBUG=1 -DSQLITE_DEFAULT_CACHE_SIZE=32768 -
> DSQLITE_DEFAULT_PAGE_SIZE=8192 -
> DSQLITE_DEFAULT_TEMP_CACHE_SIZE=32768 -DTEMP_STORE=3
> 
> 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: Joe Wilson <[EMAIL PROTECTED]>
> Date: Sunday, July 1, 2007 12:47 pm
> Subject: Re: [sqlite] Re: In Mem Query Performance
> 
> > In addition,
> > 
> > - make a new index only on column1
> > - move the prepare before the loop to avoid reparsing the SELECT 
> > each time
> > - use sqlite3_bind_* and sqlite3_reset in the loop.
> > - move finalize after the loop.
> > - query the test table directly - not the temporary ttest table.
> > - don't SELECT * if you only need a column or two
> > 
> > --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > > int main()
> > > {
> > >   sqlite3* db;
> > >   int ret = sqlite3_open("abc",);
> > > 
> > >   char* errmsg;
> > >   char buf[1024];
> > >   sprintf(buf,"create temporary table ttest(column1 
> text,column2 
> > text,primary key
> > > (column1,column2));"); 
> > >   ret = sqlite3_exec(db , buf, NULL ,NULL,);
> > > 
> > > 
> > >   sprintf(buf,"insert into ttest select * from test;");
> > >   ret = sqlite3_exec(db , buf, NULL ,NULL,);
> > > 
> > > 
> > >   //read all the srvName from file and store it in arr and query.
> > >   fstream fin("query.sql",ios::in);
> > >   string data[10];
> > >
> > >for(int j=0;!fin.eof();j++)
> > >{
> > >   fin>>data[j];
> > >   //cout<<data[j]<<endl;
> > >} 
> > >fin.close();
> > > 
> > >cout<<"Calling Test Now"<<endl;
> > >sleep(1);
&



Re: [sqlite] Re: In Mem Query Performance


2007-07-01

Thread
RaghavendraK 70574

Hi Joe/John

Appreciate your reply.
Result: with your suggestions it is to down to 3 secs.
But i need to get it to 1sec. Possible?

Here are the findings,
1) With Mem db default compile options and with suggestions(bind+ index) you 
have
provided to use bind performance stands at 10-11secs.
2) With File db default compile options it stands at 13-14secs.
3) begin and commit seems to have no affect on the query.
   Seperate index on mem table seems to have no affect.
4) Earlier Joe had suggetsed to make changes to page size,with that
it is down to 3 secs.[Along with your suggestions]
  Compile Option changes:
  ./configure --enable-threadsafe

  BCC = gcc -g -O2

  # C Compile and options for use in building executables that
  # will run on the target platform.  (BCC and TCC are usually the
  # same unless your are cross-compiling.)
  #
  TCC = gcc -g -O2 -I. -I${TOP}/src

  # Define -DNDEBUG to compile without debugging (i.e., for production 
usage)
  # Omitting the define will cause extra debugging code to be inserted and
  # includes extra comments when "EXPLAIN stmt" is used.
  #
  TCC += -DNDEBUG=1 -DSQLITE_DEFAULT_CACHE_SIZE=32768 
-DSQLITE_DEFAULT_PAGE_SIZE=8192 -DSQLITE_DEFAULT_TEMP_CACHE_SIZE=32768 
-DTEMP_STORE=3

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: Joe Wilson <[EMAIL PROTECTED]>
Date: Sunday, July 1, 2007 12:47 pm
Subject: Re: [sqlite] Re: In Mem Query Performance

> In addition,
> 
> - make a new index only on column1
> - move the prepare before the loop to avoid reparsing the SELECT 
> each time
> - use sqlite3_bind_* and sqlite3_reset in the loop.
> - move finalize after the loop.
> - query the test table directly - not the temporary ttest table.
> - don't SELECT * if you only need a column or two
> 
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > int main()
> > {
> >   sqlite3* db;
> >   int ret = sqlite3_open("abc",);
> > 
> >   char* errmsg;
> >   char buf[1024];
> >   sprintf(buf,"create temporary table ttest(column1 text,column2 
> text,primary key
> > (column1,column2));"); 
> >   ret = sqlite3_exec(db , buf, NULL ,NULL,);
> > 
> > 
> >   sprintf(buf,"insert into ttest select * from test;");
> >   ret = sqlite3_exec(db , buf, NULL ,NULL,);
> > 
> > 
> >   //read all the srvName from file and store it in arr and query.
> >   fstream fin("query.sql",ios::in);
> >   string data[10];
> >
> >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);
> >list lst;
> > 
> >char* szError=0;
> >const char* szTail=0;
> >sqlite3_stmt* pVM;
> > 
> >for(int k=0;k<10;k++)
> >{
> >   sprintf(buf,"select * from ttest where column1 = 
> '%s'",data[k].c_str());  
> >   
> > 
> >   ret = sqlite3_prepare(db, buf, -1, , );  
> >   ret = sqlite3_step(pVM); 
> >   //lst.push_back();
> >   ret = sqlite3_finalize(pVM);
> >}
> >//
> >time_t end = time(0);
> >cout<<"start="<<start<<endl;
> >cout<<"end="<<end<<endl;
> > 
> >   return 0;
> > }
> > 
> > //Result: It takes 17 sec to read 100,000 records. Can it be 
> reduced to 1 sec.???
> 
> 
> 
>   
> 
> Take the Internet to Go: Yahoo!Go puts the Internet in your 
> pocket: mail, news, photos & more. 
> http://mobile.yahoo.com/go?refer=1GNXIC
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






Re: [sqlite] Re: In Mem Query Performance


2007-07-01

Thread
John Stanton

It looks as if you would do better to run your job as one or more 
transactions and to use the Sqlite API, with the sequence:

  sqlite3_prepare
  begin transaction
loop
  sqlite3_bind
  sqlite3_step
  sqlite3_reset
end
  commit
  sqlite3_finalize
You may need to split your job into multiple transactions to manage 
their size.


RaghavendraK 70574 wrote:

Hi All,

Pls find the sample test below,
A) Using Sqlite 3.3.17,SuSE9 version,8GB Ram,4 CPU machine,
1) Compile the generate the test data program and generate the data
2) Import the data to DB
3) Compile the perf Data program and then generate perf result.
4) Result for 100,000 records it takes 17 sec. Find one record at a time.

regards
ragha
@@1) Program to generate the test data:

//Compile: g++ -g gen.cpp -o gen

#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 (serName text,doid text,primary key 
(serName,doid));"<<endl;

 for(int j=0; j<10;j++)
 {
char* ptr = tmpnam(0); 
 string key = ".";

 key += ptr;
 fout1<<key<<endl;
fout<<"insert into test values 
('"<<key<<"',"<<"'2');"<<endl;

 }

 return 0;
}

@@2) Use sqlite3 to import the data to "abc". use begin and end.To make it 
faster.

@@3) Program to check the performance
//VersionUsed:3.3.17
//Compile:g++ -g main.cpp -I. -L. -lsqlite3  -lpthread

#include 
#include 
#include 
#include 

#include 
#include 

#include "sqlite3.h"

using namespace std;

int main()
{
  sqlite3* db;
  int ret = sqlite3_open("abc",);

  char* errmsg;
  char buf[1024];
  sprintf(buf,"create temporary table ttest(column1 text,column2 text,primary key (column1,column2));"); 
  ret = sqlite3_exec(db , buf, NULL ,NULL,);



  sprintf(buf,"insert into ttest select * from test;");
  ret = sqlite3_exec(db , buf, NULL ,NULL,);


  //read all the srvName from file and store it in arr and query.
  fstream fin("query.sql",ios::in);
  string data[10];
   
   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);
   list lst;

   char* szError=0;
   const char* szTail=0;
   sqlite3_stmt* pVM;

   for(int k=0;k<10;k++)
   {
  sprintf(buf,"select * from ttest where column1 = '%s'",data[k].c_str());  
	  

  ret = sqlite3_prepare(db, buf, -1, , );  
  ret = sqlite3_step(pVM);		 
  //lst.push_back();
  ret = sqlite3_finalize(pVM);
   }

   //
   time_t end = time(0);
   cout<<"start="<<start<<endl;
   cout<<"end="<<end<<endl;

  return 0;
}

//Result: It takes 17 sec to read 100,000 records. Can it be reduced to 1 
sec.???
//All help appreciated.

//*

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: "A. Pagaltzis" <[EMAIL PROTECTED]>
Date: Saturday, June 30, 2007 9:48 pm
Subject: [sqlite] Re: In Mem Query Performance



Hi RaghavendraK,

* RaghavendraK 70574 <[EMAIL PROTECTED]> [2007-06-25 08:45]:


When the DB is opened in "in Memory mode",performance of query
does not improve. For table which has 10 columns of type Text
and each column having 128bytes data and having a total of
1 records.


that is small enough to fit into memory, and therefore small
enough to fit into the OS disk cache. In such a case, the
performance of SQLite does not differ significantly between
in-memory and on-disk databases.

Your problem is elsewhere. I



Re: [sqlite] Re: In Mem Query Performance


2007-06-30

Thread
Joe Wilson

In addition,

- make a new index only on column1
- move the prepare before the loop to avoid reparsing the SELECT each time
- use sqlite3_bind_* and sqlite3_reset in the loop.
- move finalize after the loop.
- query the test table directly - not the temporary ttest table.
- don't SELECT * if you only need a column or two

--- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> int main()
> {
>   sqlite3* db;
>   int ret = sqlite3_open("abc",);
> 
>   char* errmsg;
>   char buf[1024];
>   sprintf(buf,"create temporary table ttest(column1 text,column2 text,primary 
> key
> (column1,column2));"); 
>   ret = sqlite3_exec(db , buf, NULL ,NULL,);
> 
> 
>   sprintf(buf,"insert into ttest select * from test;");
>   ret = sqlite3_exec(db , buf, NULL ,NULL,);
> 
> 
>   //read all the srvName from file and store it in arr and query.
>   fstream fin("query.sql",ios::in);
>   string data[10];
>
>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);
>list lst;
> 
>char* szError=0;
>const char* szTail=0;
>sqlite3_stmt* pVM;
> 
>for(int k=0;k<10;k++)
>{
>   sprintf(buf,"select * from ttest where column1 = 
> '%s'",data[k].c_str());  
> 
> 
>   ret = sqlite3_prepare(db, buf, -1, , );  
>   ret = sqlite3_step(pVM); 
>   //lst.push_back();
>   ret = sqlite3_finalize(pVM);
>}
>//
>time_t end = time(0);
>cout<<"start="<cout<<"end="< 
>   return 0;
> }
> 
> //Result: It takes 17 sec to read 100,000 records. Can it be reduced to 1 
> sec.???



   

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






Re: [sqlite] Re: In Mem Query Performance


2007-06-30

Thread
Joe Wilson

Your keys are too large and have the same leading characters. 
Since the first hundred characters are the same you waste a lot 
of CPU time in comparisons.

Try to get your total key size down to a fraction of that size.

At least change your program to generate keys of this form 
instead and it will be much faster:

  key = ptr + key;

--- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>  for(int j=0; j<10;j++)
>  {
> char* ptr = tmpnam(0); 
>  string key =
>
".";
>  key += ptr;
>  fout1< fout<<"insert into test values
>
('"< 
>  }



   

Looking for a deal? Find great prices on flights and hotels with Yahoo! 
FareChase.
http://farechase.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






Re: [sqlite] Re: In Mem Query Performance


2007-06-30

Thread
RaghavendraK 70574

Hi All,

Pls find the sample test below,
A) Using Sqlite 3.3.17,SuSE9 version,8GB Ram,4 CPU machine,
1) Compile the generate the test data program and generate the data
2) Import the data to DB
3) Compile the perf Data program and then generate perf result.
4) Result for 100,000 records it takes 17 sec. Find one record at a time.

regards
ragha
@@1) Program to generate the test data:

//Compile: g++ -g gen.cpp -o gen

#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 (serName text,doid text,primary key 
(serName,doid));"<<endl;

 for(int j=0; j<10;j++)
 {
char* ptr = tmpnam(0); 
 string key = 
".";
 key += ptr;
 fout1<<key<<endl;
fout<<"insert into test values 
('"<<key<<"',"<<"'2');"<<endl;

 }

 return 0;
}

@@2) Use sqlite3 to import the data to "abc". use begin and end.To make it 
faster.

@@3) Program to check the performance
//VersionUsed:3.3.17
//Compile:g++ -g main.cpp -I. -L. -lsqlite3  -lpthread

#include 
#include 
#include 
#include 

#include 
#include 

#include "sqlite3.h"

using namespace std;

int main()
{
  sqlite3* db;
  int ret = sqlite3_open("abc",);

  char* errmsg;
  char buf[1024];
  sprintf(buf,"create temporary table ttest(column1 text,column2 text,primary 
key (column1,column2));"); 
  ret = sqlite3_exec(db , buf, NULL ,NULL,);


  sprintf(buf,"insert into ttest select * from test;");
  ret = sqlite3_exec(db , buf, NULL ,NULL,);


  //read all the srvName from file and store it in arr and query.
  fstream fin("query.sql",ios::in);
  string data[10];
   
   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);
   list lst;

   char* szError=0;
   const char* szTail=0;
   sqlite3_stmt* pVM;

   for(int k=0;k<10;k++)
   {
  sprintf(buf,"select * from ttest where column1 = '%s'",data[k].c_str());  

  

  ret = sqlite3_prepare(db, buf, -1, , );  
  ret = sqlite3_step(pVM);   
  //lst.push_back();
  ret = sqlite3_finalize(pVM);
   }
   //
   time_t end = time(0);
   cout<<"start="<<start<<endl;
   cout<<"end="<<end<<endl;

  return 0;
}

//Result: It takes 17 sec to read 100,000 records. Can it be reduced to 1 
sec.???
//All help appreciated.

//*

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: "A. Pagaltzis" <[EMAIL PROTECTED]>
Date: Saturday, June 30, 2007 9:48 pm
Subject: [sqlite] Re: In Mem Query Performance

> Hi RaghavendraK,
> 
> * RaghavendraK 70574 <[EMAIL PROTECTED]> [2007-06-25 08:45]:
> > When the DB is opened in "in Memory mode",performance of query
> > does not improve. For table which has 10 columns of type Text
> > and each column having 128bytes data and having a total of
> > 1 records.
> 
> that is small enough to fit into memory, and therefore small
> enough to fit into the OS disk cache. In such a case, the
> performance of SQLite does not differ significantly between
> in-memory and on-disk databases.
> 
> Your problem is elsewhere. If you provide your schema and
> queries, someone might be able to tell you what about them makes
> SQLite go so slow and how to make it faster.
> 
> Regards,
> -- 
> Aristotle Pagaltzis // <http://plasmasturm.org/>
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-