C ++ SQLite批量更新我做对了吗? [英] C++ SQLite Bulk Updation am i doing it right?

问题描述

阅读代码时，请找到问题1:

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);
            }
        }
    } 
}
}

更新pSQL [4]的400个条目需要2秒钟.请给我建议.如果同时删除结束事务和开始事务，则时间仍然相同.

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.

推荐答案

事务不会使事情变得更快.它实际上可能会减慢它的速度，事务的重点是立即进行所有更新，也就是说，您需要中途决定放弃更新并将数据库状态恢复到事务处理时的状态.

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.

这很慢，因为您一次又一次地动态创建UPDATE语句，并一次又一次地进行准备.尝试使用参数化查询并立即进行准备.每次您准备语句时，都会重新编译查询.因此，每次只更新参数时，您将立即准备一次，而不是每次都仅更新参数时，才用更新参数" +重建查询" +准备" +执行". .

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".

这篇关于C ++ SQLite批量更新我做对了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！