[sqlite] Improving Performance of INSERT INTO?

[Roger Dant]

Hi,
 
 
I'm working on a program that builds a table with 2 columns and up to 500,000 rows.  
I'm using INSERT INTO to add each row, and the process takes 2 - 3 hours to finish.
 
Since INSERT INTO is the only way I know of to populate a table, I'm assuming that 
I'll have to find a way to optimize these calls.  Do you have any tips for optimizing 
mass INSERTs in Sqlite?
 
I'm on a Windows XP system w/ 192 MB of RAM, and I built the library from the source.  
I was able to benchmark enough to know the slowdown was the INSERTs themselves.
 
Here's the slow code:
 
sqlite3* db;
CString sql;
sqlite3_open("c:\\test.db", &db);
sqlite3_exec(db, "PRAGMA SYNCHRONOUS", NULL, NULL, NULL);
sqlite3_exec(db, "CREATE TABLE X (I LONG, J LONG)", NULL, NULL, NULL);
for (int i = 0; i < 1000; i++) {
   for (int j = 0; j < 500; j++) {
      sql.Format("INSERT INTO X VALUES (%d,%d)", i, j);
      sqlite3_exec(db, sql, NULL, NULL, NULL);
   }
}
sqlite3_close(db);


 

                
---------------------------------
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!