High CPU usage, Which statement is updating the database?

Question

0.00/5 (No votes)

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:

C++

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

amarasat

Updated 27-Aug-12 2:53am

v5

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2012-08-24T09:18:00

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:

SQL

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.

mbue · Answer 2 · 2012-08-26T05:11:00

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.

High CPU usage, Which statement is updating the database?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0