Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm working on a largish webapp that was developed by several other programmers and I keep running into performance issues caused by poorly written SQL queries and queries that don't use an index. I'm working on fixing the problems by logging how long each query takes to complete and if it's using an index.

I was wondering if other people have had this problem before and what they've done to keep it from happening again. My initial idea is to throw an exception if any query doesn't use an index so it has to be taken care of in devel but to continue to log the large/slow queries so we can see long term where the slowness is.

Update 2012-03-05 11:56 EDT

We're using MySQL and my plan WAS to run explain on each query after it's run to check for indexs.

share|improve this question
    
Did you included performance unittests ? –  deadalnix Mar 2 '12 at 15:24
2  
@Scott Warren - What database(s) do you use? Most enterprise databases will have the ability to record query plans and elapsed time for individual SQL statements so that you can run queries to get reports on the longest-running queries, queries that don't have at least 1 index in the plan, queries that involve full table scans, etc. –  Justin Cave Mar 2 '12 at 15:34
1  
Might this be better migrated to dba.stackexchange.com/questions ? –  FrustratedWithFormsDesigner Mar 2 '12 at 15:57
    
I know my customers would love it if any of these slipped into production... User: "Your program crashed", You: "Oh, that's because the other programmers suck at indexes" –  Paul Mar 2 '12 at 16:12
1  
@Phoenix I was guessing that if Scott was worried about throwing an exception for inefficient queries at run time, he doesn't know up front what the queries will be--that is, the app is building SQL dynamically. Moreover, it sounds like he wants to check the performance of each dynamically built query (again at run time). How? Running an explain every time you run the query sounds dreadfully inefficient. –  Matthew Flynn Mar 4 '12 at 0:44
show 4 more comments

7 Answers

My initial idea is to throw an exception if any query doesn't use an index so it has to be taken care of in devel but to continue to log the large/slow queries so we can see long term where the slowness is.

I think you should not do this, because:

  • Your application can never know if a query used an index or not

  • Exceptions are for real errors not for performance checks

  • Poor performance of queries can be attributed to indexes but not to indexes alone. It could be because the amount of data returned is so large, or the database is badly designed for the purpose required, or too many columns are read that are not needed, etc. Also, re-writing the query may make it use an index (indexable and non-indexable queries)

  • Adding indexes sometimes slows performance of inserts and updates, so be careful

To solve this problem, you need to:

  • Identify poor performing queries

  • Ensure that the problem is in SQL

  • Ensure that only needed rows and columns are retrieved

  • Profile the queries using a profiling tool and take the necessary action based on the results.

Also check with DBAs Forum

share|improve this answer
    
Your comment "Your application can never know if a query used an index or not" got me thinking; actually, I am pretty sure that you could perform automatic execution plan checking (at least on some DBMSs, I am most familiar with MS SQL Server). This could result in an alternative report to the usual "queries taking the longest to run" types of reports. That said, I don't think it would add much value, the "usual" reports are typically sufficient. –  Daniel B Mar 6 '12 at 7:50
    
@DanielB, you are correct, what I was thinking of is that one won't check the execution plan from within the application code at run-time. –  Emmad Kareem Mar 6 '12 at 9:29
add comment

Do you in every single case want to make sure an index is being used? Sure, most of the time you should be using an index. But sometimes its not worth the cost. So throwing an exception might be problematic in those cases.

Sometimes you'll want to just put a query in there to get the right results before making sure its properly optimized and using an index. Throwing an exception forces the optimization before you've tested for correctness.

How about making long running queries more annoying in the development environment? Popup a message saying, "long running query." The app won't die, and you can test your feature, but you'll notice that it needs some work.

share|improve this answer
add comment

I tend to incorporate instrumentation of some form in all apps I build. JavaMelody is pretty nice and non-intrusive and it provides good JDBC-level instrumentation. If you are using Oracle, it even shows you execution plans for all queries.

share|improve this answer
add comment

I would say that you just need to increase the logging severity of such issues. I dont think you should throw an exception in such a case unless you can configure your project to only do so in the development environments.

share|improve this answer
add comment

You have a way to catch the culprits, but causing an error is a bad idea. It's also a false sense of security. A query design may not change at all, but the data can change and affect performance. Depending on the RDBMS lack of statistics can cause performance problems. You're going to keep monitoring them anyway. Just because a query uses at least one index doesn't mean it shouldn't utilize others.

This is another way to create bad blood with your programmers and users. Who is really getting punished here?

share|improve this answer
add comment

I Understand your frustration. But penalizing the user with exceptions may not be a good idea in this case.

What you can do is :

Whenever you come across this problem in the code you can call a special logger say PerformanceIssueLogger.log which will log these issues in a different log with the specific query which you can scan it later and fix the issues.

Better yet, You can encapsulate the logic that PerformanceIssueLogger.log , which can throw an exception in addition to logging in the file if the environment != prod.

share|improve this answer
add comment

Such a priori rules don't make much make sense. In a first step, just log what queries take how much time and how often they are called.

Then you can really see, which queries are an effective problem and work to fix it. Performance optimization is only possible after determining bottlenecks through proper profiling. Once a bottleneck is discovered, you must first understand it and then evaluate the different options. When it comes to SQL, slapping indices on the involved tables is one option. Denormalization, sharding or caching (through memcache or redis) are others. Much like there's no simple rule that will tell you how much resources a given query will eat up (because this depends on things that you can either speculate about or simply measure), there's no simple rule to how you can reduce the resource consumption.

share|improve this answer
add comment

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.