On Tue, Nov 1, 2011 at 6:50 PM, Tal Tabakman <tal.tabak...@gmail.com> wrote:
> Yes!, now it flys.
> can you please elaborate ? did originally reset dumped all data to disk ?
> suppose that I open a db for read only, is it recommended to issue the
> "BEGIN" command at the start of analysis ?
>
When you didn't reset the first statement, that held the transaction open,
causing things to run faster.
There is a lot of overhead associated with starting a transaction. It is
best to do multiple operations within the same transaction whenever
possible.
> thanks in advance
> Tal
> On Wed, Nov 2, 2011 at 12:33 AM, Richard Hipp <d...@sqlite.org> wrote:
>
> > On Tue, Nov 1, 2011 at 6:25 PM, Tal Tabakman <tal.tabak...@gmail.com>
> > wrote:
> >
> > > I have a slowness problem when running sqlite3 based application.
> > > I have a small db (500 K on disk, only 10000 rows) and I have a loop
> that
> > > perform 10000 selections according to
> > > the primary key of a certain table.
> > > for some reason in takes 15-20 seconds to run the loop below.
> > > by experimentation I found that this issue is related to the use of
> > > sqlite3_reset. i.e, by creating a new statement in every loop iteration
> > > without reseting and reusing it I got good performance (took a
> > > second,needless to say that I want to avoid this since it causes mem
> > > leaks.)
> > > any idea why sqlite3_reset has such a bad performance impact on my
> > program
> > > ?
> > >
> >
> > Please try the change below and let us know how it helps.
> >
> >
> > >
> > > int main() {
> > > sqlite3 * handle;
> > > sqlite3_stmt * m_entrySelectSnumStmt;
> > > sqlite3_open("entries.db",&handle);
> > > std::ostringstream query;
> > > query << "SELECT * FROM entries where SNUM = ? LIMIT 1;";
> > > sqlite3_prepare_v2(handle, query.str().c_str(),
> > > query.str().length()+1, &m_entrySelectSnumStmt, 0);
> > >
> >
> > sqlite3_exec(handle, "BEGIN", 0, 0, 0);
> >
> >
> > > for(int i = 0; i < 10000; i ++){
> > > sqlite3_bind_int(m_entrySelectSnumStmt,1,i);
> > > int rc = sqlite3_step(m_entrySelectSnumStmt);
> > > sqlite3_clear_bindings(m_entrySelectSnumStmt);
> > > sqlite3_reset(m_entrySelectSnumStmt);
> > > }
> > >
> >
> > sqlite3_exec(handle, "COMMIT", 0, 0, 0);
> >
> >
> >
> > > }
> > > _______________________________________________
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > _______________________________________________
> > 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
