Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C++ Sqlite
Hello everyone,
 
Scenario: Find all the sqlite databases(files ending with .sqlite)from a folder, read all the tags from the first column in the database and update the values correspondingly in the second column every second until one hour and then the databases are closed and the files are deleted. so this function is called every one second. Here is a piece of the C++ code:
 
function() 
{ 
    if(sqlite3_open(ch, &database) == SQLITE_OK) 
    { 
       const char *pSQL[6]; 
       pSQL[0] = "select * from TagValues"; 
       sqlite3_stmt *statement, *statement4; 
       char* errorMessage; 
       CString csValue, queryStr, Tag; 
       if ( sqlite3_prepare(database, pSQL[0], -1, &statement, 0 ) == SQLITE_OK ) 
       { 
           int ctotal = sqlite3_column_count(statement); 
           int res = 0; 
           char *szSQL; 
           sqlite3_stmt *stmt; 
           const char *pzTest; 
           char *pChar1; 
           char *pChar2; 
           sqlite3_exec(database, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);
           szSQL = "update TagValues set Value = ? where Tag = ?"; 
           int rcn = sqlite3_prepare(database, szSQL, strlen(szSQL), &stmt, &pzTest); 
          while ( 1 ) 
          { 
              res = sqlite3_step(statement); 
              if ( res == SQLITE_ROW ) 
              { 
                  for ( int i = 0; i < 1; i++ ) //read only column one which are tags by default in the database
                 { 
                     Tag = (char*)sqlite3_column_text(statement, i); 
                     USES_CONVERSION; 
                     pChar1 = T2A(csValue); 
                     pChar2 = T2A(Tag); 
                     sqlite3_bind_text(stmt, 1, pChar1, strlen(pChar1), 0); 
                     sqlite3_bind_text(stmt, 2, pChar2, strlen(pChar2), 0); 
                     sqlite3_step(stmt); 
                     sqlite3_reset(stmt); 
                 } 
              } 
              if ( res == SQLITE_DONE || res==SQLITE_ERROR) 
              { 
                 sqlite3_exec(database, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);//bulk updation should be done here.
                 sqlite3_finalize(stmt); 
                 sqlite3_finalize(statement); 
                 break;
              } 
          } 
       } 
    } 
}
 
Everything is working fine, the tags were updated everysecond with new values in the second column.
 
Case1:
10 database files with 200 tags in each of them(200 rows with two columns), the cpu usage is 17%. So the total reads are 2000 tags and total updates are 2000 per second.
 
Case2:
4 database files with 500 tags in each of them(500 rows with two columns), the cpu usage is 77%. Still the total reads are same 2000 tags and total updates are same 2000 per second.
 
Questions:
1.) Why is the CPU usage increasing in the second case? how do i reduce it?
 
2.) Is the code really doing a bulk updation?
 
3.) Which is the statement that is actually writing to the database:
is it Sqlite3_step or "COMMIT TRANSACTION"?
Posted 24-Aug-12 8:30am
amarasat1.3K
Edited 27-Aug-12 2:53am
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

While you have provided much information, it's quite hard to give you exact answers without thoroughly going through the environment.
 
But few things that hopefully are helpful
 
Bullet 1:
Most likely this happens because the amount of rows is higher and yuo do not have proper indexes to satisfy quick access to the row when updating it
Resolution:
Try creating an index for column Tag especially if this is unique
Also note that if it isn't unique, your update is updating multiple rows
 
Bullet 2:
Basically the same answer as in bullet 1
 
Bullet 3:
Typically databases write to the database file during a process which is called a checkpoint. During the transaction, all the modifications are written to the transaction log as modifications are done. Very often the exact moment of the checkpoint is unpredictable.
 
If this question is related to the performance, I would skip it. Databases optimize both read and write operations to the disk so in normal situations these are not the causes for bad performance. Typically the problem lies elsewhere (for example indexing)
 
For background information, refer to for example Write-Ahead Logging[^]
 
Addition, example of creating the index:
 
In order to create an index, refer to CREATE INDEX[^].
 
So if you want to add an index to Tag column in table TagValues , try to execute the following:
CREATE INDEX X_TagValues_Tag ON TagValues (Tag)
 
You don't have to worry about the select or the update statements. In most cases the database automatically picks the best index (if available) to perform the operation so just by creating that index once, the performance should be enhanced.
  Permalink  
v3
Comments
amarasat at 24-Aug-12 15:40pm
   
Thanks for your reply!!
 
Can you give me an example of indexing in the database and indexing in querying, i searched everywhere and was not able to understand how to implement it, is there any good example you can point me out to or you can modify my code and implement it?
Mika Wendelius at 24-Aug-12 15:59pm
   
Sure, see the updated answer.
amarasat at 24-Aug-12 16:45pm
   
Thanks a lot, for the code snippet,
 
I did exactly what you have said, i have created an index just like you said, once per every database only the first time the database is opened.
 
I don't see an any change in the CPU usage.
 
I am still reading the Write Ahead Logging.
Mika Wendelius at 24-Aug-12 17:04pm
   
Ok, so it looks like we need the heavy tools. Extract the statement (update...) and use the EXPLAIN PLAN[^] command to see what SQLite actually is going to do. What are the results you see?
 
The explain plan should be run inside a query editor, not your program, to see if it picks the correct index or if it still does a full table scan.
amarasat at 27-Aug-12 11:17am
   
it says, sqlite3_sql identifier not found, this is what i have referred
 
"http://www.sqlite.org/eqp.html", the step 2 sample code is what i am trying to implement.
Mika Wendelius at 31-Aug-12 14:25pm
   
That's odd. Could it be that the connection isn't properly opened? Can you run something else succesfully?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Your "COMMIT" statement has no effect. Every "UPDATE" statement is committed immediate on call. To use "COMMIT" you should call first the "BEGIN" or "BEGIN TRANSACTION" command statement. On "COMMIT" all your "UPDATE" statements are committed at once or you can "ROLLBACK" all statements between "BEGIN" and "ROLLBACK".
Take a look at the official page: http://www.sqlite.org/lang_transaction.html[^]
Best regards.
  Permalink  
Comments
amarasat at 27-Aug-12 8:56am
   
I am very sorry, thats a typo, when editing one of the versions i forgot to put the Begin Transaction. I have updated the code now to reflect what i have.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 469
1 ChintanShukla 305
2 Sergey Alexandrovich Kryukov 253
3 RyanDev 250
4 Richard Deeming 250
0 Sergey Alexandrovich Kryukov 8,906
1 OriginalGriff 7,601
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,923


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 27 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100