adding "begin transaction" before inserting data
then "commit transaction" will surely boost the inserts
Kalle Last wrote:
>
> Hello,
>
> I'm having a big performance problem while trying to use sqlite as a
> data output for my application. The application writes out a bunch of
> packets relatively fast. There are about 10 different types of them
> with all but one sizing under 100 bytes. Those small ones I write out
> roughly once per second or less. The big one is the "real" data that
> gets logged from ~10-1000x per second. It's size can be from 2-3kb to
> 32kb and it's size depends on other parameters that don't change very
> often, generally not more than every few minutes. Raw data output rate
> is at most ~8MiB/s and generally is around 3-5. At the time of writing
> the files are never altered, just a bunch of data gets added to them
> up to around 1GB is reached, then a new file is created.
>
> When using my current flat-file output it doesn't cause almost any
> measurable system load. When using the DB it pretty much halts the
> application without causing significant CPU load. The data still gets
> logged to DB but at a fraction of the speed it should be (~1MB per 15s
> vs 5MB/s). I'm quite new to sqlite so I'm not really sure how to
> benchmark it or how to know what is causing the slowdown. So far it
> seems if I disable saving the big packets it works perfectly. The
> overhead of using SQLite is needed to be absolutely minimal as the
> application itself is doing a whole lot of raw computations already.
> Flat file output has almost zero overhead but any modifications to
> file format make things quite a bit complicated.
>
> The big packet table is created like this:
>
> char *errMsg = 0;
> sqlite3_exec(db,
> "CREATE TABLE Data ( \
> NumRows INTEGER NOT NULL, \
> NumColumns INTEGER NOT NULL, \
> RowSize FLOAT NOT NULL, \
> Begin FLOAT NOT NULL, \
> End FLOAT NOT NULL, \
> DataBegin FLOAT NOT NULL, \
> DataEnd FLOAT NOT NULL, \
> Timestamp DOUBLE NOT NULL, \
> PacketID INTEGER NOT NULL, \
> Data BLOB NOT NUlL, \
> ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)",
> 0,
> 0,
> &errMsg);
>
>
> Data is inserted with this piece of code:
>
> DataPing* ping = packet.dataPing;
> const string queryStr = "INSERT INTO DataPing (NumRows,
> NumColumns, Begin, End, DataBegin, DataEnd, Timestamp, PacketID,
> Data)"
> "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
> sqlite3_stmt* query(NULL);
> long rc = SQLITE_OK;
>
>
> if (query == 0) {
> rc = sqlite3_prepare_v2(db, queryStr.c_str(), queryStr.size(),
> &query, NULL);
> if (rc != SQLITE_OK) {
> TRACE_PRINT("error", ("SQL error: %s", sqlite3_errmsg(db)))
> }
> }
>
>
> sqlite3_bind_int( query, 1, ping->numRows);
> sqlite3_bind_double( query, 2, ping->numColumns);
> sqlite3_bind_double( query, 3, ping->begin);
> sqlite3_bind_double( query, 4, ping->end);
> sqlite3_bind_int( query, 5, ping->dataBegin);
> sqlite3_bind_double( query, 6, packet.dataEnd);
> sqlite3_bind_int( query, 7, packet.timestamp);
> sqlite3_bind_int( query, 8, packet.packetId);
> sqlite3_bind_blob( query, 9, &ping->data[0],
> ping->numRows*ping->numColumns*sizeof(ping->data[0]), 0);
> if (rc != SQLITE_OK) {
> TRACE_PRINT("error", ("SQL error: %s", sqlite3_errmsg(db)))
> }
> rc = sqlite3_step(query);
> if (rc != SQLITE_OK && rc!=SQLITE_DONE) {
> TRACE_PRINT("error", ("SQL error: %d: %s", rc,
> sqlite3_errmsg(db)))
> }
> rc = sqlite3_reset(query);
>
>
> Basic data flow is program receives packet -> packet is parsed ->
> packet is logged -> data from packet is used in a lot of computations
> -> packet is destroyed (memory freed)
>
>
> I have no idea how to fine-tune sqlite behavior, if I'm doing the
> querys correctly or if I'm doing something else wrong. I tried to use
> transactions by doing a pair of end/begin queries once per second but
> it didn't seem to change much. Data got saved just fine but it was
> still horribly slow. My best guess at the moment is that program hangs
> because of excessive file synchronization. Anyone has any better
> guesses what could be the problem and how to solve them?
>
> --
> Kalle Last
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
View this message in context:
http://www.nabble.com/Big-performance-problem-with-inserting-lots-of-data-tp25224450p25234485.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
