You only need it around INSERT or UPDATE, not SELECT.
The transaction delays the commit to disk so only transactions that write
benefit from it.
It makes a HUGE difference in speed.
You may also find increasing your cache size could help. How big is your
database?
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Ian Hardingham [i...@omroth.com]
Sent: Friday, June 03, 2011 9:31 AM
Cc: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL?
Hey guys, once again thanks for the help.
Should really every single INSERT/UPDATE section have a begin/end
transaction around it?
I have posted this code before, so apologies for doing it again - here
is how my scripting language calls a query:
int SQLiteObject::ExecuteSQL(const char* sql, int hack)
{
int iResult;
sqlite_resultset* pResultSet;
ClearErrorString();
// create a new resultset
pResultSet = new sqlite_resultset;
if (pResultSet)
{
pResultSet->bValid = false;
pResultSet->iCurrentColumn = 0;
pResultSet->iCurrentRow = 0;
pResultSet->iNumCols = 0;
pResultSet->iNumRows = 0;
pResultSet->iResultSet = m_iNextResultSet;
pResultSet->vRows.clear();
m_iLastResultSet = m_iNextResultSet;
m_iNextResultSet++;
}
else
return 0;
iResult = sqlite3_exec(m_pDatabase, sql, Callback,
(void*)pResultSet, &m_szErrorString);
if (iResult == 0)
{
//SQLITE_OK
SaveResultSet(pResultSet);
//Con::executef(this, 1, "onQueryFinished()");
#ifdef PROFILE_DB
Con::errorf("--- NEW DB RESULT SET: %i", pResultSet->iResultSet);
#endif
return pResultSet->iResultSet;
}
else
{
// error occured
Con::executef(this, 2, "onQueryFailed", m_szErrorString);
delete pResultSet;
return 0;
}
return 0;
}
On 03/06/2011 15:28, Black, Michael (IS) wrote:
>
> And do you wrap all your updates inside a transaction?
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
> ------------------------------------------------------------------------
>
_______________________________________________
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
