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