1. Use bind vars... prepare the statement once, execute it many times binding values each time.
1a. Drop the index. 2. Use a transaction. 3. Commit every <whatever your tolerance>. In your case with only 10,000 recs, I would commit only at the very end. 4. Build the index at the end. Really, 10k recs should insert in a coupla seconds. On 8/3/07, Stephen Sutherland <[EMAIL PROTECTED]> wrote: > Hi > > Here's My Situation: > ------------------------------------- > I have an XML file which contains user created data. It can have maybe > 10,000 or more nodes. The XML needs to be queried infrequently for use in the > application, in a way that lends itself more to SQL. For example the only > query that I will use is > "SELECT * FROM myTable WHERE BookID IN > (2,3,4,5,7,8,23,24,25,26,27,38,67,66)" > > That's a good example. The IN parameters can vary with numbers ranging form > 1 to 66. > > IN COMES SQLITE3 > --------------------------------------- > > So I decided to pull all the XML nodes into the Sqlite3 databse at > application start up. > > But I have discovered some slowness. > So far I have simply reused the quick start code. > > I modified it to do the following: > 1. Open the Database. > 2. Drop the 2 tables if they exist in the database > 3. Created 2 tables if not exist - Lookup_BookID and Question > LookupID has a primary key on BookID and Question has no index. > > 4. Then I simply insert 66 records into lookup_bookID at application start > up. > Well simply by inserting 66 3 field records into my LookupID , I count 7 > seconds. > > So it makes me a bit concerned that when I have to drop my XML repository > with 7,000 to 10,000 records it will take forever at application start up ? > > Is there a way to execute all these inserts much faster than I am currently > doing ? > > I am currently simply using sqlite3_exec(db, "INSERT SQL STATEMENT ", ... > etc etc ) > for every single insert statement. > > Is there away to maximize this speed dramatically to do 10,000 inserts in > less than 5 seconds. > > > Thanks > > Stephen > > > --------------------------------- > Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ S&T Policy Fellow, National Academy of Sciences http://www.nas.edu/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
