On 29 Apr 2013, at 4:08pm, peter korinis <[email protected]> wrote:
Thanks for your detailed description of your use of SQLite which saved a lot of 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." I don't think so. We have never arrived at any rule-of-thumb like "If your database is more than 20Gig, you should probably think about Progres instead.". > 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.) Think carefully about what indexes you have. Indexes on a single column are often a waste of time and space. An index should satisfy the demands of a SELECT (WHERE, ORDER BY, and JOIN CLAUSES) or the WHERE clause of an UPDATE. People who create databases tend to just index all 'important' columns without thinking through when each index would be used and how it could be improved. I really need to write my 'SQL indexes for fun and profit' web pages, don't I ? > a. add 4GB or 12GB more memory ? > > b. get faster HD for target DB . ext. eSATA SSD (say 256GB) ? A lot of these depend on how the database is consulted. If your user tends to concentrate on a small cluster of data for a long time then move on, caching is good. If your user tends to span almost all of the database, or to hop frequently from one part to another, caching is not important. > c. get faster ext. eSATA 10,000rpm HD for DB ? Many desktop computers have really terrible external bus speeds these days. Without something like Thunderbolt you might lose a lot of that speed just by trying to funnel so much data through the connection. A faster internal hard disk is a different matter. > d. make performance changes to current DB settings ? (or learn to write > better SQL ??) See my comments on 2. Open the database in read-only mode. Since your database is never edited, you can try some PRAGMAs which kill the ACID abilities of SQLite (turn synchrony off ?) though I have no idea if these will actually speed things up for your particular solution. There are a few optimizations which have no effect on other SQL engines but are great for SQLite. For instance, COUNT(*) and COUNT(rowid) do identical things but in SQLite the former is far faster. I'm not sure how you'd find these things out: I don't know of anyone who has collected them. > f. Something else ? Fewer but bigger transactions are good. Even when you're just doing a bunch of SELECTs, combining them in one transaction speeds things up. And lastly, something that involves spending money, but you can get free expert opinion on whether it will help before you buy. Your description seems to suit the Compressed and Encrypted Read-Only extension <http://www.hwaci.com/sw/sqlite/cerod.html> down to the ground. Having the database compressed means that all the raw data-handling parts of your application are reading less data and moving less data around. And the Hwaci company is run by the main author of SQLite. You couldn't wish to get a more expert opinion. If Dr Hipp or another of the team doesn't comment on this thread you could email the address on that site and ask. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
