I am forwarding this to the list in the hope that someone else will have
a good idea since the OP, toms, is having trouble posting himself.
Dennis Cote
toms wrote:
> ok here is the code I use for the statements:
>
> here is the SQL:
>
> "UPDATE Statistics_Players"
> " SET "
> " iMVP = ?,"
> " Inflicted_iPasses = ?,"
> " Inflicted_iInterceptions = ?,"
> " Inflicted_iCatches = ?,"
> " Inflicted_iTouchdowns = ?,"
> " Inflicted_iKO = ?,"
> " Inflicted_iCasualties = ?,"
> " Inflicted_iDead = ?"
> " WHERE idPlayer_Listing=?"
>
> I init the statement like this (where _szSQL is the previous string)
>
> const char* szTail = 0;
> int rc = sqlite3_prepare(m_pDB->GetSqliteDB(), _szSQL,
> StrLen(_szSQL), &m_pStatement, &szTail);
> if( rc != SQLITE_OK )
> {
> SDK_ASSERTMSG(0, "Query Init error: %s\n",
> sqlite3_errmsg(m_pDB->GetSqliteDB()));
> return;
> }
>
> then, when I want to use the query, I retrieve it (it is kept in a wrapper
> CQuery class), and then bind every params:
>
> CQueryPlayersWriteStats* pQuery = GetDB()->GetQueryPlayersWriteStats();
> pQuery->Bind(CQueryPlayersWriteStats::Params::PLAYERID ,
> (u32)GetID() );
> pQuery->Bind(CQueryPlayersWriteStats::Params::MVP ,
> (u32)statsPlayer.m_uMVP );
> pQuery->Bind(CQueryPlayersWriteStats::Params::PASSES ,
> (u32)statsPlayer.m_uNbAccuratePass );
> pQuery->Bind(CQueryPlayersWriteStats::Params::INTERCEPTIONS ,
> (u32)statsPlayer.m_uNbInterception );
> pQuery->Bind(CQueryPlayersWriteStats::Params::CATCHES ,
> (u32)statsPlayer.m_uNbReception );
> pQuery->Bind(CQueryPlayersWriteStats::Params::TD ,
> (u32)statsPlayer.m_uNbTouchDown );
> pQuery->Bind(CQueryPlayersWriteStats::Params::KO ,
> (u32)statsPlayer.m_uNbKo );
> pQuery->Bind(CQueryPlayersWriteStats::Params::CASUALTIES ,
> (u32)statsPlayer.m_uNbCasualties );
> pQuery->Bind(CQueryPlayersWriteStats::Params::DEAD ,
> (u32)statsPlayer.m_uNbDeath );
> pQuery->SingleExec();
>
> where each CQueryPlayersWriteStats::Params is an enum value between 1 and 8
>
> And here is the code of the bind method:
>
> void CQuery::Bind(u32 _uParam, s32 _iValue)
> {
> int rc = sqlite3_bind_int(m_pStatement, (int)_uParam, _iValue);
> if( rc != SQLITE_OK )
> {
> SDK_ASSERTMSG(0, "Query Bind error: %s\n",
> sqlite3_errmsg(m_pDB->GetSqliteDB()));
> return;
> }
> }
>
> And I have many versions of the bond method:
>
> void Bind (u32 _uParam, s32 _iValue);
> void Bind (u32 _uParam, u32 _uValue);
> void Bind (u32 _uParam, f32 _fValue);
> void Bind (u32 _uParam, const char* _szValue);
>
> And after the bond, I only call the Step once:
>
> void CQuery::SingleExec()
> {
> Reset();
>
> int rc = sqlite3_step(m_pStatement);
>
> if (rc == SQLITE_ROW)
> {
> SDK_WARNING(0, "Seems like a query would be more appropriate");
> return;
> }
>
> if (rc != SQLITE_DONE)
> {
> SDK_WARNING(0, "Could not execute SingleExec!");
> return;
> }
>
> Reset();
> }
>
> The class CQueryPlayersWriteStats inheritates from CQuery
>
> The only doubt I have is about calling Reset twice in the SingleExec method.
> It was more secure for me to do this.
> Any idea?
That isn't necessary. You should reset either before or after (I would
suggest before), but I doubt that is the cause of the slowdown either.
> As I still cannot send on the mailling list, please feel free to forward this
> on it if you want :)
>
> Thanks for the help! :-)
>
>
> Dennis Cote a écrit :
>> toms wrote:
>>> this is not easily doable: the database is on a server, in order to change
>>> fields I have to change it on the server :/
>>
>> I don't need to see the database itself, only the code that accesses the
>> database through SQLite. I thought you were concerned about leaking info
>> about the database by disclosing table and field names etc.
>>
>>
>>> yes: I only prepare it once, and then use this prepared statement to bind
>>> parameters.
>>> Then step, but only once (because it is an UPDATE) and then reset
>>> Maybe the gain is not so much when not used in a SELECT?
>>>
>>
>> That should not be the case. The sqlite3_prepare() call does the SQL
>> language parsing and VDBE code generation for the query once. The VDBE code
>> is then executed multiple times.
>>
>> When using sqlite3_exec() the string containing the query is parsed, and
>> VDBE code is generated and then executed for each call.
>>
>> It doesn't really matter what type of a statement is being executed. The
>> benefit is amortizing one parsing and code generation time over the the 300
>> calls instead of incurring that same cost for each execution.
>>
>> I am suspicious about the way you are handling the reset and binding calls.
>>
>> Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
