Re: [sqlite] expression syntax

[ljucerk]

Thanks Ulrik,

It worked out with pre-compiled statements (using sqlite3_bind_int).
But I still wonder if there is a way to set integer values (dynamic values) 
into DB with INSERT and UPDATE?

NK


> Hi NK,
> 
> [EMAIL PROTECTED] wrote:
> 
> >Hi,
> >what is the correct syntax to use for WHERE expression:
> >
> >if (sqlite3_exec(test_db, "CREATE TABLE ana_db (item1 integer, item2 
> integer, item3 integer, item4 integer, item5 integer);", NULL, 0, NULL))
> >  sqlite_error(test_db);
> >
> >// create index
> > if (sqlite3_exec(test_db, "CREATE INDEX item1idx ON ana_db (item1);", NULL, 
> > 0, 
> NULL))
> >  sqlite_error(test_db);
> >
> >  // insert values for 1000 records
> >  for (ii=0; ii< 1000; ii++)
> > {
> >   if (error_code = sqlite3_exec(test_db, "INSERT INTO ana_db VALUES 
> ('ii', 1, 0, 1, 100);", NULL, 0, NULL))
> > {
> >   sqlite_error(test_db);
> >   return (-1);
> > }
> > }
> >
> > // update values for 1000 records
> >  for (ii=0; ii< 1000; ii++)
> > {
> >   if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET 
> item2=item2+1 WHERE item1='ii';", NULL, 0, NULL))
> > {
> >   sqlite_error(test_db);
> >   return (-1);
> > }
> >}
> >
> >It looks like I'm not using the right syntax in UPDATE statement, I tried 
> >with: 
> WHERE item1 = $ii, w/out success. 
> >What am I doing wrong?
> >
> >Thanks,
> >
> >NK
> >  
> >
> 
> you want the sqlite3_bind_int API.  Look it up on the www.sqlite.org 
> website.
> 
> HTH
> 
> Ulrik
> 
> 
> 
> -- 
> Ulrik Petersen, MA, B.Sc.
> University of Aalborg, Denmark
> Homepage: http://ulrikp.org
> 
> 

[sqlite] expression syntax

[ljucerk]

Hi,
what is the correct syntax to use for WHERE expression:

if (sqlite3_exec(test_db, "CREATE TABLE ana_db (item1 integer, item2 
integer, item3 integer, item4 integer, item5 integer);", NULL, 0, NULL))
  sqlite_error(test_db);

// create index
 if (sqlite3_exec(test_db, "CREATE INDEX item1idx ON ana_db (item1);", NULL, 0, 
NULL))
  sqlite_error(test_db);

  // insert values for 1000 records
  for (ii=0; ii< 1000; ii++)
{
  if (error_code = sqlite3_exec(test_db, "INSERT INTO ana_db VALUES 
('ii', 1, 0, 1, 100);", NULL, 0, NULL))
{
  sqlite_error(test_db);
  return (-1);
}
}

 // update values for 1000 records
  for (ii=0; ii< 1000; ii++)
{
  if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET 
item2=item2+1 WHERE item1='ii';", NULL, 0, NULL))
{
  sqlite_error(test_db);
  return (-1);
}
}

It looks like I'm not using the right syntax in UPDATE statement, I tried with: 
WHERE item1 = $ii, w/out success. 
What am I doing wrong?

Thanks,

NK

Re: [sqlite] perfromance of UPDATE

[ljucerk]

no difference, even if each statement is enveloped with "BEGIN;" and "COMMIT;".
According to SQLite speed comparison chart, UPDATE should be somewhat slower 
than INSERT, but not that much.
I'm using in-memory database, no sync. 

Thanks,

NK


> On Mon, 17 Jan 2005 19:09:14 +, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
> 
> > My question is, why did the two UPDATES take so much time, compared to 
> > INSERT.
> 
> How fast are all those INSERTs and UPDATEs if you put them into a transaction?

[sqlite] perfromance of UPDATE

[ljucerk]

Hi,
I'm using sqlite 3.0.8 on Linux, and the performance of UPDATE command seems to 
be slow. Here is the test program:

  error_code = sqlite3_open(":memory:", _db);
  if (error_code)
 {
   return (-1);
 }
  // set PRAGMAs
if (sqlite3_exec(test_db, "PRAGMA synchronous=OFF;", NULL, 0, NULL))
  sqlite_error(test_db);
if (sqlite3_exec(test_db, "PRAGMA temp_store=MEMORY;", NULL, 0, NULL))
  sqlite_error(test_db);
if (sqlite3_exec(test_db, "PRAGMA default_temp_store=MEMORY;", NULL, 0, 
NULL))
  sqlite_error(test_db);
if (sqlite3_exec(test_db, "CREATE TABLE ana_db (item1 integer, item2 
integer, item3 integer, item4 integer, item5 integer);", NULL, 0, NULL))
  sqlite_error(test_db);
 // insert values for 1 records
 for (ii=0; ii< 1; ii++)
 {
   if (error_code = sqlite3_exec(test_db, "INSERT INTO ana_db VALUES ('ii', 1, 
0, 1, 100);", NULL, 0, NULL))
 {
   sqlite_error(test_db);
   return (-1);
 }
 }

This 1 INSERTs take about 2.2 seconds, not bad.


   if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET item2=item2+1 
WHERE item1==1;", NULL, 0, NULL))
 {
   sqlite_error(test_db);
   return (-1);
 }

This first UPDATE took about 37 miliSec.

   if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET item2=item2+1 
WHERE item1==;", NULL, 0, NULL))
 {
   sqlite_error(test_db);
   return (-1);
 }

This second UPDATE took about 36.8 miliSec.

if (error_code = sqlite3_exec(test_db, "SELECT SUM(item2)  FROM  ana_db 
WHERE item5==100;", myCallBack2, , NULL))
{
   sqlite_error(test_db);
   return (-1);
}

Finally, SELECT took about 48 miliSec.

My question is, why did the two UPDATES take so much time, compared to INSERT.

Thanks,

Nenad