Click here to Skip to main content
13,046,337 members (125,155 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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:

    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); 
                     pChar1 = T2A(csValue); 
                     pChar2 = T2A(Tag); 
                     sqlite3_bind_text(stmt, 1, pChar1, strlen(pChar1), 0); 
                     sqlite3_bind_text(stmt, 2, pChar2, strlen(pChar2), 0); 
              if ( res == SQLITE_DONE || res==SQLITE_ERROR) 
                 sqlite3_exec(database, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);//bulk updation should be done here.

Everything is working fine, the tags were updated everysecond with new values in the second column.

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.

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.

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
Updated 27-Aug-12 2:53am
Rate this: bad
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
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.
amarasat 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 24-Aug-12 15:59pm
Sure, see the updated answer.
amarasat 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 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 27-Aug-12 11:17am
it says, sqlite3_sql identifier not found, this is what i have referred

"", the step 2 sample code is what i am trying to implement.
Mika Wendelius 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
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:[^]
Best regards.
amarasat 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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 27 Aug 2012
Copyright © CodeProject, 1999-2017
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