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",&db);
> > 
> >   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,&errmsg);
> > 
> > 
> >   sprintf(buf,"insert into ttest select * from test;");
> >   ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg);
> > 
> > 
> >   //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);
> >    list<string> lst;
> > 
> >    char* szError=0;
> >    const char* szTail=0;
> >    sqlite3_stmt* pVM;
> > 
> >    for(int k=0;k<100000;k++)
> >    {
> >       sprintf(buf,"select * from ttest where column1 = 
> '%s'",data[k].c_str());      
> >           
> > 
> >       ret = sqlite3_prepare(db, buf, -1, &pVM, &szTail);  
> >       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]
-----------------------------------------------------------------------------

Reply via email to