[sqlite] Sqlite3 Delete using sqlite3_step is very slow.

[Scott Zhang]

Hello.
   I am using Sqlite3 in my applicaiton. I have a simple table:

CREATE TABLE doc_cat_link (
   id       INTEGER PRIMARY KEY,
   doc_id   INTEGER,
   cat_id   INTEGER,
   [ACTION] TEXT    DEFAULT '',
   is_dirty INTEGER
);

the problem is it only has 10 rows, but each time I delete a row using
"delete from doc_cat_link where doc_id=?" in my application, it took
about 200ms.

After tracking down to the real function call,
[code]
wxLogMessage(wxString::Format(_T("ExecuteUpdate1 is %d\n"), clock()));
 int rc = sqlite3_step((sqlite3_stmt*) m_stmt);
       wxLogMessage(wxString::Format(_T("ExecuteUpdate1 is %d\n"), clock()));
 if (rc == SQLITE_DONE)
 {
         wxLogMessage(wxString::Format(_T("ExecuteUpdate3 is %d\n"), clock()));
   int rowsChanged = sqlite3_changes((sqlite3*) m_db);
wxLogMessage(wxString::Format(_T("ExecuteUpdate4 is %d\n"), clock()));
   rc = sqlite3_reset((sqlite3_stmt*) m_stmt);

   if (rc != SQLITE_OK)
   {
     localError = sqlite3_errmsg((sqlite3*) m_db);
     throw wxSQLite3Exception(rc, wxString::FromUTF8(localError));
   }
wxLogMessage(wxString::Format(_T("ExecuteUpdate5 is %d\n"), clock()));
[/code]


The line "  int rc = sqlite3_step((sqlite3_stmt*) m_stmt);"  is not stable.

21:59:55: ExecuteUpdate1 is 82321

21:59:55: ExecuteUpdate1 is 82454

21:59:55: ExecuteUpdate3 is 82459

21:59:55: ExecuteUpdate4 is 82462

21:59:55: ExecuteUpdate5 is 82465

I can see it take 130 ms during this run.

The interesting thing is when I do same sql in sqliteStudio(sqlite
3.7.4) , it only took
 1 row(s) affected in 0.001199 second(s).
very comparable performance. I am using sqlite3.dll whose version
should be 3.7.5.


Does anyone know why and how can I optimize this?

Thanks.
Regards.
Scott
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users