[sqlite] UPDATE query: why is that code sooo slow?

[Luc Vandal]

Hi,

Perhaps I’m not doing  things correctly, but this code will take about 1
minute to execute (2400 records on a total of around 44000 records):

Basically, I’m getting records that need to be updated. Then, I go through
each record and update it. Should be fast IMHO but I don’t see why it’s that
slow!

Is there another way that would be faster?


if( nRet == SQLITE_OK )
{
        sqlite3_free_table( paszResults );

        strQuery.Format( _T( "SELECT something, thatThing, otherthing, stuff
FROM table WHERE hello = %d;" ), VARIANT_TRUE );

        nRet = sqlite3_get_table( m_pDB, strQuery, &paszResults, &nRows,
&nCols, &szError);

        if( nRet == SQLITE_OK )
        {
                if( nRows > 0 )
                {
                        nRet = sqlite3_exec( m_pDB, _T("BEGIN;"), 0, 0,
&szError );

                        //Go through all the records
                        for(INT nCurrRow=1;nCurrRow<=nRows;nCurrRow++)
                        {
                                strQuery.Format( "UPDATE something SET
thatThing = 0, otherthing = %f WHERE stuff = %d AND hello = %d AND bye =
-1", … );

                                nRet = sqlite3_exec( m_pDB, strQuery, 0, 0,
&szError );

                                nColBad+=4;
                                nColGood+=4;
                        }

                        nRet = sqlite3_exec( m_pDB, _T("COMMIT;"), 0, 0,
&szError );
                }

                sqlite3_free_table( paszResults );
        }

Thanks!

Luc