· 8 years ago · Feb 28, 2018, 10:36 AM
1/*************************************************************
2 Baseline code to experiment with SQLite performance.
3
4 Input data is a 28 MB TAB-delimited text file of the
5 complete Toronto Transit System schedule/route info
6 from http://www.toronto.ca/open/datasets/ttc-routes/
7
8**************************************************************/
9#include <stdio.h>
10#include <stdlib.h>
11#include <time.h>
12#include <string.h>
13#include "sqlite3.h"
14
15#define INPUTDATA "C:\TTC_schedule_scheduleitem_10-27-2009.txt"
16#define DATABASE "c:\TTC_schedule_scheduleitem_10-27-2009.sqlite"
17#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)"
18#define BUFFER_SIZE 256
19
20int main(int argc, char **argv) {
21
22 sqlite3 * db;
23 sqlite3_stmt * stmt;
24 char * sErrMsg = 0;
25 char * tail = 0;
26 int nRetCode;
27 int n = 0;
28
29 clock_t cStartClock;
30
31 FILE * pFile;
32 char sInputBuf [BUFFER_SIZE] = " ";
33
34 char * sRT = 0; /* Route */
35 char * sBR = 0; /* Branch */
36 char * sVR = 0; /* Version */
37 char * sST = 0; /* Stop Number */
38 char * sVI = 0; /* Vehicle */
39 char * sDT = 0; /* Date */
40 char * sTM = 0; /* Time */
41
42 char sSQL [BUFFER_SIZE] = " ";
43
44 /*********************************************/
45 /* Open the Database and create the Schema */
46 sqlite3_open(DATABASE, &db);
47 sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
48
49 /*********************************************/
50 /* Open input file and import into Database*/
51 cStartClock = clock();
52
53 pFile = fopen (INPUTDATA,"r");
54 while (!feof(pFile)) {
55
56 fgets (sInputBuf, BUFFER_SIZE, pFile);
57
58 sRT = strtok (sInputBuf, "t"); /* Get Route */
59 sBR = strtok (NULL, "t"); /* Get Branch */
60 sVR = strtok (NULL, "t"); /* Get Version */
61 sST = strtok (NULL, "t"); /* Get Stop Number */
62 sVI = strtok (NULL, "t"); /* Get Vehicle */
63 sDT = strtok (NULL, "t"); /* Get Date */
64 sTM = strtok (NULL, "t"); /* Get Time */
65
66 /* ACTUAL INSERT WILL GO HERE */
67
68 n++;
69 }
70 fclose (pFile);
71
72 printf("Imported %d records in %4.2f secondsn", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
73
74 sqlite3_close(db);
75 return 0;
76}
77
78sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
79sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);
80
81sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
82
83pFile = fopen (INPUTDATA,"r");
84while (!feof(pFile)) {
85
86 ...
87
88}
89fclose (pFile);
90
91sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
92
93/* Open input file and import into the database */
94cStartClock = clock();
95
96sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
97sqlite3_prepare_v2(db, sSQL, BUFFER_SIZE, &stmt, &tail);
98
99sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
100
101pFile = fopen (INPUTDATA,"r");
102while (!feof(pFile)) {
103
104 fgets (sInputBuf, BUFFER_SIZE, pFile);
105
106 sRT = strtok (sInputBuf, "t"); /* Get Route */
107 sBR = strtok (NULL, "t"); /* Get Branch */
108 sVR = strtok (NULL, "t"); /* Get Version */
109 sST = strtok (NULL, "t"); /* Get Stop Number */
110 sVI = strtok (NULL, "t"); /* Get Vehicle */
111 sDT = strtok (NULL, "t"); /* Get Date */
112 sTM = strtok (NULL, "t"); /* Get Time */
113
114 sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
115 sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
116 sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
117 sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
118 sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
119 sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
120 sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);
121
122 sqlite3_step(stmt);
123
124 sqlite3_clear_bindings(stmt);
125 sqlite3_reset(stmt);
126
127 n++;
128}
129fclose (pFile);
130
131sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
132
133printf("Imported %d records in %4.2f secondsn", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
134
135sqlite3_finalize(stmt);
136sqlite3_close(db);
137
138return 0;
139
140/* Open the database and create the schema */
141sqlite3_open(DATABASE, &db);
142sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
143sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
144
145/* Open the database and create the schema */
146sqlite3_open(DATABASE, &db);
147sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
148sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
149
150/* Open the database and create the schema */
151sqlite3_open(DATABASE, &db);
152sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
153sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
154sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
155
156#define DATABASE ":memory:"
157
158pFile = fopen (INPUTDATA,"r");
159while (!feof(pFile)) {
160
161 fgets (sInputBuf, BUFFER_SIZE, pFile);
162
163 sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "t"), -1, SQLITE_TRANSIENT); /* Get Route */
164 sqlite3_bind_text(stmt, 2, strtok (NULL, "t"), -1, SQLITE_TRANSIENT); /* Get Branch */
165 sqlite3_bind_text(stmt, 3, strtok (NULL, "t"), -1, SQLITE_TRANSIENT); /* Get Version */
166 sqlite3_bind_text(stmt, 4, strtok (NULL, "t"), -1, SQLITE_TRANSIENT); /* Get Stop Number */
167 sqlite3_bind_text(stmt, 5, strtok (NULL, "t"), -1, SQLITE_TRANSIENT); /* Get Vehicle */
168 sqlite3_bind_text(stmt, 6, strtok (NULL, "t"), -1, SQLITE_TRANSIENT); /* Get Date */
169 sqlite3_bind_text(stmt, 7, strtok (NULL, "t"), -1, SQLITE_TRANSIENT); /* Get Time */
170
171 sqlite3_step(stmt); /* Execute the SQL Statement */
172 sqlite3_clear_bindings(stmt); /* Clear bindings */
173 sqlite3_reset(stmt); /* Reset VDBE */
174
175 n++;
176}
177fclose (pFile);
178
179sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
180sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
181...
182
183...
184sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
185sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
186
187SELECT COUNT(*) FROM table
188
189SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>
190
191int limit = (count + n_threads - 1)/n_threads;
192
193int offset = thread_index * limit