Re: [sqlite] Improving Performance of INSERT INTO?

2004-09-15 Thread Will Leshner 
Roger Dant wrote:
I didn't realize sqlite3 had transaction support!  I must have missed it in the documentation -- off to re-read it.
Sqlite2 has transaction support and I don't think it's been removed for sqlite3.

Re: [sqlite] Improving Performance of INSERT INTO?

2004-09-15 Thread Roger Dant 


Will Leshner <[EMAIL PROTECTED]> wrote:
 
>Try doing all the INSERTs in a single transaction.
 
I didn't realize sqlite3 had transaction support!  I must have missed it in the 
documentation -- off to re-read it.
 
Thanks.
 
 
 


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

Re: [sqlite] Improving Performance of INSERT INTO?

2004-09-15 Thread Matt Wilson 
On Wed, Sep 15, 2004 at 02:14:58PM -0700, Roger Dant wrote:
>  
> 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);

Adding a BEGIN at the start and COMMIT at the end makes this test
complete in 14.5 seconds for me.

Cheers,

Matt

Re: [sqlite] Improving Performance of INSERT INTO?

2004-09-15 Thread Will Leshner 
Roger Dant wrote:
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?
Try doing all the INSERTs in a single transaction.

[sqlite] Improving Performance of INSERT INTO?

2004-09-15 Thread 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!