On Mon, 29 Apr 2013 11:08:48 -0400 "peter korinis" <[email protected]> wrote:
> 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." Nowadays this decission is taken (in my opinion) if the database needs to feed a lot of users. The data size is not a priority. > > 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.) Split data in 2 or more databases and attach them. They can reside on different disks. You can partition tables too, by date for example and use UNION on the queries. You can build a temporal table with queries and its results or calculated counts. Do a select in this temp table searching for previous queries. You say that data is read only, it should work. > > 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 ??) Write better sql has two sides. It's about write better selects and find a better schema. > 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 If i need to upgrade dbms, i use Postgres. But i don't think you need it. > f. Something else ? Reread Simon's email and sail the documentation and test. > Thank you so much for your help. > > peterK HTH --- --- Eduardo Morras <[email protected]> _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
