This question regards SQLite (3.6) performance. (Lengthy because I want to describe the environment.)
. Win-7 (64-bit, though I don't know if SQLite uses 64-bit). . 3 year old HP laptop with Intel Core Duo CPU P8400 @ 2.27GHz with only 4GB memory . 286GB HD (50% full) + 1TB 7200rpm external eSata HD (90% free) - contains target SQLite DB. . Target DB is single user, read-only, static . as follows o DB size = 24GB: in 8 tables. 2 most used tables contain 43million rows and 86 million rows - others are small look-up tables with 50-10,000 rows. 1-3 indices per table. o imported from 44GB CSV file with 45million rows of 600 columns o Used for: SQL query only (CLI or GUI), no updates, no deletes, no inserts, or no transactions, no apps. . Future: Double the size . intend to clone existing DB and populate with another year's data. Additional years will likely be added at later time. Now to the questions: 1. Is my current DB too large for SQLite to handle efficiently? I just read in O'Reilly, Using SQLite, book, "If you need to store and process several gigabytes or more of data, it might be wise to consider a more performance-oriented product." 2. Adding which resources would most improve performance ??? (Many queries, like a SELECT COUNT (and GROUP) of indexed column on a JOIN of the 2 large tables may take 30-60 or more minutes.) a. add 4GB or 12GB more memory ? b. get faster HD for target DB . ext. eSATA SSD (say 256GB) ? c. get faster ext. eSATA 10,000rpm HD for DB ? d. make performance changes to current DB settings ? (or learn to write better SQL ??) e. convert from SQLite to MySQL or something else? I like the simplicity and low admin or SQLite - so I prefer not to switch DBMS f. Something else ? Thank you so much for your help. peterK _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
