Re: [sqlite] Re: In Mem Query Performance

[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",&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[10];
>
>for(int j=0;!fin.eof();j++)
>{
>   fin>>data[j];
>   //cout<} 
>fin.close();
> 
>cout<<"Calling Test Now" 
>//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, &pVM, &szTail);  
>   ret = sqlite3_step(pVM); 
>   //lst.push_back();
>   ret = sqlite3_finalize(pVM);
>}
>//
>time_t end = time(0);
>cout<<"start="   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

[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

[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));"<
#include 
#include 
#include 

#include 
#include 

#include "sqlite3.h"

using namespace std;

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[10];
   
   for(int j=0;!fin.eof();j++)
   {
  fin>>data[j];
  //cout< 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, &pVM, &szTail);  
  ret = sqlite3_step(pVM);   
  //lst.push_back();
  ret = sqlite3_finalize(pVM);
   }
   //
   time_t end = time(0);
   cout<<"start="<
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 // 
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

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

Re: [sqlite] Re: Oddball query required

[Lucas (a.k.a T-Bird or bsdfan3)]

Thank you very much Igor.  This will do exactly what I need.

Igor Tandetnik wrote:


Lucas (a.k.a T-Bird or bsdfan3)
<[EMAIL PROTECTED]> wrote:


I am using SQLite in an application within a game where I have a table
of player nicknames and cumulative player scores (among other things).
How do I figure out at what (numeric) one of these records would be at
if the table were sorted, say, by the cumulative scores column?



select count(*) from tableName
where scrore > (select score from tableName where playerId = :player);

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







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

[sqlite] Re: Oddball query required

[Igor Tandetnik]

Lucas (a.k.a T-Bird or bsdfan3)
<[EMAIL PROTECTED]> wrote:

I am using SQLite in an application within a game where I have a table
of player nicknames and cumulative player scores (among other things).
How do I figure out at what (numeric) one of these records would be at
if the table were sorted, say, by the cumulative scores column?


select count(*) from tableName
where scrore > (select score from tableName where playerId = :player);

Igor Tandetnik 



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

[sqlite] Re: In Mem Query Performance

[A. Pagaltzis]

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 // 

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

[sqlite] Oddball query required

[Lucas (a.k.a T-Bird or bsdfan3)]

I am using SQLite in an application within a game where I have a table 
of player nicknames and cumulative player scores (among other things).  
How do I figure out at what (numeric) one of these records would be at 
if the table were sorted, say, by the cumulative scores column?  Or 
should I just store that position in the database itself?




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