SQLite-How to Insert Multiple Rows At a Time In C

Previous Preparation

I ran the below code in Vistual studio 2017. If you don’t know how to use SQLite in Vistual studio. You can visit my another article – How to Use SQLite in Visual Studio 2017.

Background

I developed an application program which I need to insert data into database. In the beginning, I inserted the data into the database one by one. The application program was broken when the data size is bigger.

Originally, I though it was very quick to finish insertion operation. it just needs about 10 ms for one insertion operation. But once the data size is bigger, if it has 100 records, it needs 1-2 seconds to finish insertion operation. Thus, it will spend more time to finish insertion operation if we have more records. So if we can insert multiple records at a time, it will be a good choice.

Here we go, below is the code to implement the batch insertion operation.

void insertByBatch(sqlite3 *db, int times)
{
	DWORD start, stop;
	start = GetTickCount();
	const char *sql = "INSERT INTO COMPANY VALUES(NULL, 'Emily', '26', 'CHINA', '12345');";
	int rc = 0;
	char *zErrMsg = 0;

	rc = sqlite3_exec(db, "BEGIN;", NULL, 0, &zErrMsg);
	if (rc != SQLITE_OK) {
		fprintf(fp, "SQL error: %s in %s\n", zErrMsg, __FUNCTION__);
		sqlite3_free(zErrMsg);
	}

	for (int i = 0; i < times; i++) {
		rc = sqlite3_exec(db, sql, NULL, 0, &amp;zErrMsg);
		if (rc != SQLITE_OK) {
			fprintf(fp, "SQL error: %s in %s\n", zErrMsg, __FUNCTION__);
			sqlite3_free(zErrMsg);
		}
	}

	rc = sqlite3_exec(db, "COMMIT;", NULL, 0, &amp;zErrMsg);
	if (rc != SQLITE_OK) {
		fprintf(fp, "SQL error: %s in %s\n", zErrMsg, __FUNCTION__);
		sqlite3_free(zErrMsg);
	}


	stop = GetTickCount();
	fprintf(fp, "times = %d, GetTickCount: %lu ms in %s\n", times, stop - start, __FUNCTION__);
}

Below is the code for test.

//Insert record one yb one. Times form 500 to 3000.
for (int i = 500; i <= 3000; i += 500) {
	insertByOne(dbByOne, i);
}
//Batch insertion. Times from 500 to 3000.
for (int i = 500; i <= 3000; i += 500) {
	insertByBatch(dbByBatch, i);
}
//Batch insertion. Times from 1000 to 50000.
for (int i = 1000; i <= 50000; i += 1000) {
	insertByBatch(dbByBatch, i);
}

Below is the test result.

times = 500, GetTickCount: 9094 ms in insertByOne
times = 1000, GetTickCount: 19406 ms in insertByOne
times = 1500, GetTickCount: 27812 ms in insertByOne
times = 2000, GetTickCount: 37422 ms in insertByOne
times = 2500, GetTickCount: 47078 ms in insertByOne
times = 3000, GetTickCount: 56297 ms in insertByOne
times = 500, GetTickCount: 16 ms in insertByBatch
times = 1000, GetTickCount: 31 ms in insertByBatch
times = 1500, GetTickCount: 16 ms in insertByBatch
times = 2000, GetTickCount: 31 ms in insertByBatch
times = 2500, GetTickCount: 31 ms in insertByBatch
times = 3000, GetTickCount: 16 ms in insertByBatch
times = 1000, GetTickCount: 31 ms in insertByBatch
times = 2000, GetTickCount: 16 ms in insertByBatch
times = 3000, GetTickCount: 31 ms in insertByBatch
times = 4000, GetTickCount: 31 ms in insertByBatch
times = 5000, GetTickCount: 47 ms in insertByBatch
times = 6000, GetTickCount: 47 ms in insertByBatch
times = 7000, GetTickCount: 47 ms in insertByBatch
times = 8000, GetTickCount: 47 ms in insertByBatch
times = 9000, GetTickCount: 62 ms in insertByBatch
times = 10000, GetTickCount: 47 ms in insertByBatch
times = 11000, GetTickCount: 63 ms in insertByBatch
……

From the above result, we can see it has a big difference between insert one by one and multiple records at a time.