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 <unistd.h>
 #include <iostream>
 #include <stdio.h>
 #include <fstream>
 
 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<100000;j++)
  {
     char* key = tmpnam(0);
      string ser =
 
".111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111";
 
 
      fout1<<key<<endl;
     fout<<"insert into test values 
('"<<key<<"','"<<ser<<"',"<<"'22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222');"<<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 <iostream>
 #include <fstream>
 #include <time.h>
 #include <list>
 
 #include <sys/timeb.h>
 
 #include <string>
 #include <unistd.h>
 
 #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<<data[j]<<endl;
    }
    fin.close();
 
    cout<<"Calling Test Now"<<endl;
    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="<<ms<<endl;
 
    //
    time_t end = time(0);
    cout<<"start="<<start<<endl;
    cout<<"end="<<end<<endl;
 
   return 0;
 }