just a little up since I have had no answers?
I'm still having the pb and cannot understand why? :)
Dennis Cote a écrit :
>
> 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
