Re: [sqlite] prepare peformances
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), _pStatement, );
>> 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:
>>
>> voidBind(u32 _uParam, s32 _iValue);
>> voidBind(u32 _uParam, u32 _uValue);
>> voidBind(u32 _uParam, f32 _fValue);
>> voidBind(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
Re: [sqlite] prepare peformances
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), _pStatement, );
> 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:
>
> voidBind(u32 _uParam, s32 _iValue);
> voidBind(u32 _uParam, u32 _uValue);
> voidBind(u32 _uParam, f32 _fValue);
> voidBind(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
Re: [sqlite] prepare peformances
Bruce Robertson wrote: > Thanks, but I'm gonna need some further instruction or examples or pointers > to a resource as none of these statements mean anything to me. > I also posted some sample code that is equivalent to the old sample code at http://www.sqlite.org/quickstart.html which still uses the sqlite3_exec() API and callback functions. My new version uses the prepare/step APIs and can be found at http://article.gmane.org/gmane.comp.db.sqlite.general/33742/match=quickstart HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
Bruce Robertson wrote: > Thanks, but I'm gonna need some further instruction or examples or pointers > to a resource as none of these statements mean anything to me. > Try reading this http://www.sqlite.org/cintro.html and see if it makes more sense after that. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
On Mon, Jun 09, 2008 at 03:39:01PM -0700, Bruce Robertson scratched on the wall: > Thanks, but I'm gonna need some further instruction or examples or pointers > to a resource as none of these statements mean anything to me. These are C API function calls. If you're not using the C API, none of this applies. Otherwise: http://www.sqlite.org/c3ref/funclist.html -j > > > Use this sequence: > > > > sqlite3_open > > sqlite3_prepare_v2//Compiles SQL statement > > > > loop > > sqlite3_bind... //Binds variables > > > > sqlite3_step//Executes statement > > > > sqlite3_reset //Readies compiled statement for binding to vars > > until finished > > sqlite3_finalize //Destroys compiled statement > > sqlite3_close > > > > > > Bruce Robertson wrote: > >> Can somebody point me to documentation or examples of sqlite prepare > >> statements and their purose? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
Thanks, but I'm gonna need some further instruction or examples or pointers to a resource as none of these statements mean anything to me. > Use this sequence: > > sqlite3_open > sqlite3_prepare_v2//Compiles SQL statement > > loop > sqlite3_bind... //Binds variables > > sqlite3_step//Executes statement > > sqlite3_reset //Readies compiled statement for binding to vars > until finished > sqlite3_finalize //Destroys compiled statement > sqlite3_close > > > Bruce Robertson wrote: >> Can somebody point me to documentation or examples of sqlite prepare >> statements and their purose? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
Can somebody point me to documentation or examples of sqlite prepare statements and their purose? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
> I would expect that sqlite3_prepare would be faster in such a case, and > maybe Toms is pointing out a circumstance where recreating the query > seems to be faster. Or am I misreading the post? One possible explanation (stab in the dark): If many of the bound parameters are text (or blob) based, and were bound with sqlite3_bind_text(..., SQLITE_TRANSIENT), then SQLite would need to allocate & deallocate memory to hold each bound parameter. (15 params * 384 queries = 5760 allocations.) OTOH, the previous sqlite3_exec approach may have simply sprintf'd the entire SQL statement(s) into a single pre-allocated buffer, possibly avoiding slow-ish dynamic allocations. If the mem allocation time were > than statement compilation time, the "prepare" approach would appear slower. As I said, just a possibility... ~Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
Chiming in a thought, but isn't there a subtle difference between the two? As I am reading Toms post, sqlite3_prepare would be called once and the parameters would be bound up to 384 times The call to sqlite3_exec should finalize the prepared query and re-prepare it 384 times. I would expect that sqlite3_prepare would be faster in such a case, and maybe Toms is pointing out a circumstance where recreating the query seems to be faster. Or am I misreading the post? John John Stanton wrote: > Sqlite3_exec just encapsulates sqlite3_prepare. You very likely have > something wrong with your code if yor version works slower. > > toms wrote: > >> Hi all >> I tried to use the sqlite3_prepare to increase my performances during >> requests for both writing / reading. >> The strange thing is that when using prepare on a request used many >> times with many parameters (many times = 384 max, many parameters = 15) >> it os slower than simply using the sqlite3_exec. >> Did I miss something? >> Should I be aware of some tricks about prepare? >> Many thanks >> toms >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
Sqlite3_exec just encapsulates sqlite3_prepare. You very likely have something wrong with your code if yor version works slower. toms wrote: > Hi all > I tried to use the sqlite3_prepare to increase my performances during > requests for both writing / reading. > The strange thing is that when using prepare on a request used many > times with many parameters (many times = 384 max, many parameters = 15) > it os slower than simply using the sqlite3_exec. > Did I miss something? > Should I be aware of some tricks about prepare? > Many thanks > toms > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
