Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C++Sqlite
Please find question1 as you read along the code:
 
function()
{
if(sqlite3_open(ch, &database) == SQLITE_OK)
{
    const char *pSQL[6];
    pSQL[3] = "select * from TagValues";
    sqlite3_stmt *statement, *statement4;
    char* errorMessage;
    CString csValue, queryStr, Tag;
    if ( sqlite3_prepare(database, pSQL[3], -1, &statement, 0 ) == SQLITE_OK )
    {
        int ctotal = sqlite3_column_count(statement);
        int res = 0;
        sqlite3_exec(database, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);
        while ( 1 )
	{
	    res = sqlite3_step(statement);
            if ( res == SQLITE_ROW ) 
	    {
		for ( int i = 0; i < 1; i++ ) 
		{
		    Tag = (char*)sqlite3_column_text(statement, i);
                    queryStr.Format(L"update TagValues set Value='%s' where Tag='%s'", csValue, Tag);
		    pSQL[4] = T2A(queryStr);
                    sqlite3_prepare(database, pSQL[4], -1, &statement4, 0 );//Question 1: is this really executing in a bulk??? or is it executing right away?
                }
            }
            if ( res == SQLITE_DONE)    
	    {
		sqlite3_exec(database, "END TRANSACTION", NULL, NULL, &errorMessage);
            }
        }
    } 
}
}
 
Its taking 2 seconds to update 400 entries of pSQL[4]. Please suggest me. If i remove both end and begin transactions, the time is still the same.
Posted 2-Aug-12 9:46am
amarasat1.3K
Edited 2-Aug-12 9:53am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

It is slow because you build the UPDATE statement dinamically, again and again, and prepare as well again and again. Try to use parametrized query and prepare it at once. Each time you prepare statement the query is recompiled. So instead of "update parameter"+"rebuild the query"+"prepare"+"execute" each time you will update only parameters, you will prepare at once, after that in each iteration you will only "update parameter"+"execute".
  Permalink  
Comments
amarasat at 3-Aug-12 10:43am
   
I have been trying a lot , can you tell me how to do it? Or point me to a good example so that i can proceed myself. Thanks a lot!!
armagedescu at 3-Aug-12 11:48am
   
See more information on functions sqlite3_bind_parameter*. Look for info on parametrized queries, or binding values to prepared statement. Parameters are either identified by index, or by name. See how to compile/prepare statements, how to use parameters, to bind them, so on. Hope this info will help: http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob
amarasat at 6-Aug-12 10:46am
   
This is what i have tried, there was still not much improvement, tell me if there is anything more i can do to improve the speed. function() { if(sqlite3_open(ch, &database) == SQLITE_OK) { const char *pSQL[6]; pSQL[3] = "select * from TagValues"; sqlite3_stmt *statement, *statement4; char* errorMessage; CString csValue, queryStr, Tag; if ( sqlite3_prepare(database, pSQL[3], -1, &statement, 0 ) == SQLITE_OK ) { int ctotal = sqlite3_column_count(statement); int res = 0; char *szSQL; sqlite3_stmt *stmt; const char *pzTest; 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++ ) { Tag = (char*)sqlite3_column_text(statement, i); char *pChar1; char *pChar2; 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) { sqlite3_finalize(stmt); sqlite3_finalize(statement); } } } } } Now it takes 1.1secs to update 400 entries and sometimes lesser then that, but still that's too much time for just 400 entries, can you suggest me anything? or am i doing something wrong still?
armagedescu at 7-Aug-12 3:04am
   
Ok, you have to move the bind and char* outside the for loop. You have to bind the parameter only once, execute once, and interate with for loop. Also I don't understand why you use resed inside for. It have to be before or after for loop.
amarasat at 7-Aug-12 16:46pm
   
Ok, i got what you are saying, Just another question completely out of the discussion. Is there anyway to know, wehere is the maximum cpu usage in the above code? In general do you have any idea if the cpu usage will be more for binding or preparing. The total cpu usage to execute above code for 400 entries is 57%, is there anyway to know how much cpu usage each statement is taking? Pls throw some suggestions on this.
armagedescu at 8-Aug-12 4:07am
   
If application is CPU intensive, this means something goes wrong. Do you see, in the query a search condition, where tag=?. I guess the tag column is not idexed. Try to create a index on tag column, and the query will execute much faster. And please make the variable name more informative. Instead of statement use selectTagsStmt. Instead of stmt use updateTagValuesStmt. Making a more informative name means to make the program more informative, understandable and maintainable. And making a longer name does not affect the program performance.
amarasat at 8-Aug-12 14:46pm
   
Thanks a lot, your suggestions have helped me and solved my issues in attaining what i want, thanks a lot for your time and responses, i will accept your solution now, and also i will ask for more help here if i need and more info in the future, is it ok?
armagedescu at 8-Aug-12 17:12pm
   
Yep, just ask.
amarasat at 20-Aug-12 14:49pm
   
Hai sorry to bother you again. I have two questions. sqlite3_bind_text(stmt, 1, pChar1, strlen(pChar1), 0); sqlite3_bind_text(stmt, 2, pChar2, strlen(pChar2), 0); sqlite3_step(stmt); sqlite3_reset(stmt); sqlite3_exec(database, "COMMIT TRANSACTION", NULL, NULL, &errorMessage); sqlite3_finalize(stmt); 1.)In the whole code if we consider the above statements, which is the statement that is actually writing to the database? Is it sqlite3_step(stmt) or sqlite3_finalize(stmt) or sqlite3_bind_text or "COMMIT TRANSACTION"? Instead of writing the whole 400 entries at the same time, i jsu want to write only a chunk of 100 entries at a time and see if that makes a diff in the cu usage.
armagedescu at 21-Aug-12 2:25am
   
I am not sure about the specifics of sqlite, but I think there is no sence to call COMMIT TRANSACTION if you have not executed BEGIN TRANSACTION. And I don't know what are step and reset doing. In normal API for databases, you just call exec on the stmt, and no step, no reset. Any exec of an update/insert/delete statements usualy are transactional. Maybe I am wrong, you have to check more detaily in documentation. I have experience with other APIs, and very little experience with sqlite.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

A transaction does not make things faster. It can actually slow it down, the point of a transaction is for the update to happen all at once, that is, for you to decide half way through to abandon the process and restore the state of your DB to what it was when the transaction started.
  Permalink  
Comments
amarasat at 3-Aug-12 10:50am
   
can you suggest me which way to proceed to reduce the time when updating a sqlite database with c++. Or point me to a good example so that i can proceed myself. Thanks a lot!!

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 390
1 Shai Vashdi 349
2 Sergey Alexandrovich Kryukov 325
3 OriginalGriff 265
4 Peter Leow 171
0 Sergey Alexandrovich Kryukov 8,979
1 OriginalGriff 5,280
2 Peter Leow 4,010
3 Maciej Los 3,535
4 Abhinav S 3,263


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 3 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid