-----Original Message----- From: Soham Mehta [mailto:[EMAIL PROTECTED] Sent: Thursday, June 24, 2004 3:22 PM To: [EMAIL PROTECTED] Subject: [sqlite] Performance Issue with SQLite Inserts
Hello, We recently switched to SQLite in our application and have not been able to get the performance that partially motivated the switch. Our application downloads TV guide data from the internet and loads it into a local database. The data is in 6 sets of XML files (each set holds 2 days of data). We read the data and insert it into the database a set of data at a time. Other than one unique index, all of the indexes are generated after the inserts to help speed up the initial data load. Here's how the inserts performed (this will print in the output pane of the debugger): Inserting 26544 records took 32538.7241 ms. Inserting 26420 records took 36432.113 ms. Inserting 26262 records took 37901.9064 ms. Inserting 25560 records took 37948.8147 ms. Inserting 26389 records took 54225.9948 ms. Inserting 17324 records took 31084.5668 ms. Just to prove that the hit is not coming from the XML reading or other logic, here are the results for the exact same test over the exact same data with just the SQLite insert commented out: Inserting 26544 records took 4597.0134 ms. Inserting 26420 records took 4393.7441 ms. Inserting 26262 records took 4409.3802 ms. Inserting 25560 records took 4956.6437 ms. Inserting 26389 records took 5378.8184 ms. Inserting 17324 records took 3486.8503 ms. Creating indexes is also slow. Obviously, this does not match up with the performance tests that appear on the website. Is there something that we are doing wrong? Any help or guidance that you can provide would be greatly appreciated. Regards, Soham How are you loading all of this data into the sqlite database. I notice that bulk loading from a file using "COPY" works pretty good. A bunch of single inserts performs poorly. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
