discvova.blogg.se - Sqlite transaction increased speed

Sqlite transaction increased speed code#
Sqlite transaction increased speed windows#

WHONET is currently only natively-compatible with Microsoft Windows,īut SQLite may allow emulation systems to function much better than with the dBASE structure on MacOS and Linux.

Compatible with modern versions of Microsoft Windows, MacOS, and Linux.

Modern database platform, described as "the most used database engine in the world".

WHONET and BacLink are now ready to support a newer and more modernĭata structure called SQLite which offers many advantages over dBASE. Which has worked well for WHONET users for many years. Using a transaction was a huge improvement, but recompiling the SQL statement for every insert doesn’t make sense if we using the same SQL over-and-over.For the past twenty years, WHONET has relied on a simple but robust data file structure called dBASE (also known as DBF files),

Simply wrapping all of our inserts in a single transaction improved our performance to 23 000 inserts-per-second. Sqlite3_exec ( db, "END TRANSACTION", NULL, NULL, & sErrMsg ) **************************************************************/ #include #include #include #include #include "sqlite3.h" #define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt" #define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite" #define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)" #define BUFFER_SIZE 256 int main ( int argc, char ** argv ) Input data is a 28 Mb TAB-delimited text file of theĬomplete Toronto Transit System schedule/route info The Code: A simple C program that reads the text file line-by-line, splits the string into values and then will inserts the data into an SQLite database. The SQLite version I happen to have is a bit older (3.6.7), but I suspect these results will be comparable to the latest release (please leave a comment if you think otherwise). I’m using the SQLite “Amalgamation”, compiled directly into my test application.

Sqlite transaction increased speed code#

The code is compiled with MSVC 2005 as “Release” with “Full Optimization” (/Ox) and Favor Fast Code (/Ot).

Sqlite transaction increased speed windows#

My test machine is a 3.60 GHz P4 running Windows XP.A 28 meg TAB-delimited text file (approx 865000 records) of the complete transit schedule for the city of Toronto.We’re going to start with some simple data: “Use a transaction!”), I thought it best to write some C code and actually measure the impact of various options. The Experiment: Rather than simply talking about performance tips in the general sense (i.e. It was not a trivial matter to figure-out what all of the options and techniques were, so I though it prudent to create this community wiki entry to share the results with SO readers in order to save others the trouble of the same investigations. It turns-out that the performance of SQLite can vary significantly (both for bulk-inserts and selects) depending on how the database is configured and how you’re using the API. Rationale: Initially I was disappointed with the performance I was seeing. SQLite is ideal for this situation because it’s fast, it requires no specialized configuration and the database is stored on disk as a single file. We have large amounts of configuration data stored in XML files that are parsed and loaded into an SQLite database for further processing when the application is initialized. Bulk-insert performance of a C application can vary from 85 inserts-per-second to over 96 000 inserts-per-second!īackground: We are using SQLite as part of a desktop application.