Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and quad core Xeon processor.
So, since I've never worked with big data, I'm trying to understand if it is the bad query problem or is it a hardware problem? Any information is appreciated

share|improve this question
3  
Post a sample slow query with a query plan and table descriptions with indexes. –  vonPryz Sep 12 '13 at 12:28

1 Answer 1

Query optimization is a lot more than just "bad hardware" or "bad query". You also have to consider things like

  • configuration of the DB(MS): e.g., effort in query optimization (something like "how much time does the DBMS have to find the best query plan), mapping of buffer pools to the data and size of buffer pools (this should be chosen in a way so that you have a high buffer pool hit ratio, meaning that the DBMS can get most of the data from main memory, avoiding I/O)
  • user: Does the user know, what he/she really wants? Do the queries ask exactly for the data that he/she needs? I guess in over 90% the answer is "no".
  • application: If it takes more than 30 minutes until the application (I don't know, whether you hacked the query into the DB via command line or use an application, but most likely the latter case) returns the data, maybe the application needs time to process the data or just does "something" that needs time... Maybe you could change that "something" or the data processing could be done directly by database?
  • How about your network? How do you establish the connection between DB and application? This is also a point where you might save time...

When you chose new hardware that should be twice as fast as your old one, don't expect the query execution times to halve. There's even the possibility that your performance is getting worse, e.g. because the database (manager) configuration is chosen to get the best performance out of your old hardware...

I guess the first thing to do is to look at the queries. Maybe buying new hardware is the easiest way (if you have money), but considering all the points mentioned above and optimizing the queries, you'll get a much better performance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.