On Thu, Jun 26, 2008 at 11:58 AM, Brooks, Phil <[EMAIL PROTECTED]> wrote: > Hi, I am new to the list and to using sqlite - I am looking at > using sqlite for a 2 way lookup table for my application. > I have a home grown file based solution, but was hoping to > find better performance and more functionality by putting > the lookup tables into sqlite. > ... trimmed ... > > Now the Question > Can anyone suggest additional things I might try to: > > 1) reduce db creation time > - or - > 2) reduce the size of the file? > > I could actually live with a larger than 7GB file size if > I could beat the 30-40 minute runtime of my original solution > by a resonable margin. Also, I am assuming that importing > a .csv file using .import will be similar in speed to > creating the database using the c++ interface using the > sqlite3_prepare/sqlite3_bind/sqlite3_step/sqlite3_reset > interfaces -- is that a valid assumption? >
Import speed is easily fixed - don't index until after the data is inserted. It will be *much* faster. Unfortunately this is a worst-case scenario for when it comes to file size. Indexes store a duplicate of the data they are indexing, so each of those indexes are copying a large portion of data. One way to improve size might be to have a separate table just mapping strings to integers, and use integers in your main table. -- Cory Nelson http://www.int64.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
