[Cppcms-users] Fast bulk-insert into SQLITE3 using cppDB

[Vizcayno T. <viz...@gm...>]

Hello:
I show you the main part of a C++ which loads a 540MB delimited ("|") text
file into an SQLite3 db using last cppDB (trunk 1875):

int main ()
{
	Timer t;
	cout << t.ShowStart() << endl;

	ifstream input("in1.txt");
	const size_t size = 512 * 1024;
	char buffer[size];
	string* f = new string[10];

	cppdb::session sql("sqlite3:db=db.db; Page Size=65536; Cache
Size=65536; Synchronous=Off; Journal Mode=Off;");
	sql << "DROP TABLE IF EXISTS ttb" << cppdb::exec;
	sql << "CREATE TABLE ttb (alm varchar(4), alm2 varchar(4), \		mat
varchar(18), ser varchar(20), fac varchar(18), almttb varchar(10),
\		cant integer, sts varchar(1), ser_2_20 varchar(20), rowid_sap
integer, \		stsmat varchar);" << cppdb::exec;

	cppdb::transaction guard(sql);
	cppdb::statement stat;
	stat = sql <<
		"INSERT INTO ttb(alm,alm2,mat,ser,fac,almttb,cant,sts,ser_2_20,rowid_sap,stsmat)
"
		"VALUES(?,?,?,?,?,?,?,?,?,?,?)";

	int cont = 0;
	int regLei = 0;
	while (input)    // Read a text file with fields separated by "|"
	{
		input.read(buffer, size);
		size_t readBytes = input.gcount();
		for (size_t  i = 0; i < readBytes; i++)
		{
			if (*(buffer+i) == '\n') // || *(buffer+i) == '\r')
			{
				if (regLei++ > 0)  // Don't save first line, it's header
				{
					stat.reset();
					stat.bind(f[0]);
					stat.bind(f[1]);
					stat.bind(f[2]);
					stat.bind(f[3]);
					stat.bind(f[4]);
					stat.bind(f[5]);
					stat.bind(f[7]);
					stat.bind_null();
					stat.bind_null();
					stat.bind_null();
					stat.bind(f[9]);
					stat.exec();
				}
	
				for (int j=0;j<10;f[j++]="");  // Reset string arrays of fields
				cont = 0;
			}
			else
				if (*(buffer+i) != '|')
					f[cont] += *(buffer+i);  // Build each field char by char
				else
					cont++;  // Build data on next string of the array f
		}
	}
	guard.commit();

	t.Stop();
	cout << t.ShowEnd() << endl;
	cout << "Executed in: " << t.ElapsedSeconds() << " seconds." << endl;

	input.close();
	return 0;
}


Please, be patient with my C++ style. There is a part of the code that is
trying to read the file as fast as possible (using a buffer), but I can't
get to save the data into Sqlite at the same speed that I read.
In the code you can see and attemp to accelerate the writing but, I am not
sure if it's working because the journal file continues generating data, I
am sure I am doing something wrong:
     cppdb::session sql("sqite3:db=db.db; Page Size=65536; Cache Size=65536;
Synchronous=Off; Journal Mode=Off;");

My questions are:
1) What is bad in the cppdb::session syntax I wrote?
2) Checking the code, can you find a way to speed-up the writing of the data
into the SQLite DB (I am sure that the reading is very fast), the command
compilation in VS2010 is: cl /EHsc /Ox /Ob2 /Oi for both, the cppDB and my
C++ program; i'm under windows 7 and program generation is for 32 bits.
3) I also wrote a program in C# and it is always 10-15 seconds faster
than my C++ and, as a curiosity, the file size of the database running C# is
386MB and after running the C++ the result size is 408MB (one possible
reason because C++ is slower than my C# program); the records and fields
generated are the same in both databases and I'm breaking my head trying to
figure out the reason of this difference in sizes; may be you know possible
reasons?

Many thanks for your attention.