On Tue, Apr 24, 2012 at 6:33 PM, MB Software Solutions, LLC <[email protected]> wrote: > On 4/24/2012 6:08 PM, Ted Roche wrote: >> And what is the problem you're trying to solve? Are your data scripts >> adding new data (INSERT/UPDATE) or querying (SELECT) and what's the >> problem? I'd guess slow response, but... there's that saying about >> ASSuME.... > > Mostly, it's SELECT queries.
Quantify, please: "I load 50 million rows of data and run a query that should return 10 records and it takes 15 minutes." Or "I expect 25 million rows" in which case it should take 15 minutes! Or "It takes 7 seconds and I want it to take 5" -- these are different questions. > I load the data, add the appropriate > indexes on the key fields in my WHERE clauses (very common usually...not > much variance in my queries. E.g.., "...where region = ?cRegion and > expcat = ?cExpcat and product = ?cProduct" so I have a compound index > on region+expcat+product), and then query away. Over optimization. If you have a compound index, your query ought to be: region+expcat+product = ?mycompoundparameter It's far, far better to use one index per column and your original query, though. Though it is not Rushmore, and some VFP rules don't apply, the rule of "index expression EXACTLY matching the left side of the WHERE expression" does apply. > I had defaulted to InnoDB, and chose that for less chance of crashes. I > was actually going to lookup the prevailing current-day wisdom on using > the MyISAM engine again, but last I read, InnoDB was supposedly just as > fast now. InnoDB is for transactions, which you're not doing. It's slower on reads and writes than MyISAM, but don't go changing it until we learn some more. >> Does the database have to be on a laptop? The first, cheapest database >> performance optimization is "spindles, spindles and more spindles:" OS >> on its own disk, data on one, index on another, logs on a third. Disk >> I/O is orders of magnitude slower than CPU or memory performance and >> is cheap, cheap, cheap to solve... on a box, not a laptop. > > As I said to Steve, we've got these spare laptops (2.4 GHz processor > with 3.5 GB RAM running Windows XP) and I'm not sure they'd allow us to > buy a separate dedicated box for this data work since it's just 2 months > out of the year annually. I'm betting the idea of asking for a new > dedicated server box would get a response something akin to "we can get > it done with what we've got." You don't need a great big honking $12,000 machine. You could probably get away with it for $1000, which is less than you've wasted waiting on the machine and typing all these emails. >> Throwing RAM at the problem can't hurt, with dirt-cheap RAM prices. >> But if it's write I/O you're optimizing, you might not see much of a >> performance increase unless you tweak cache settings. > > I set my ini file to actual like a big server, trying to get the maximum > cache settings. If your database is too big to be loaded into memory completely, and each query is pulling in different results, creating too large a cache means you're spending all of your time reading records into memory (and indexing them there, and updating the cache tables) when you're going to need to read new records off disk the next time. If you've got the time to experiment, trying one index per column in the WHERE clause and trying a couple different sets of ini settings on one known query, shutting down and restarting between queries, can give you a pretty good idea which way you should look at optimizing. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cacw6n4u5w3hareuzghsbt5drkjrtj2b4rbfkfo1d9dyh0kl...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
