How to debug a database query for performance

Question

I am absolutely new in databases and SQL queries.

Q1. What are the common problems one generally face in database queries? Probably extremely large queries, performance issues, timeout and connectivity errors, import export and restoration errors

Q2. What approach and tools one should be aware of while debugging a SQL query performance?

It will be great if you can share some of the problems faced and how did you went forward for resolving it.

Answer 1

Try to use EXPLAIN before your queries, that will give you a great overview of what's going on under the hood.

Also see: SQL Performance Tips

Answer 2

Probably one of the most important things you will want to understand is indexes. One big potential bottleneck for query performance is when you are searching on a column that is not part of an index and it causes a table scan. A table scan means that every row is being scanned, so naturally the more records in the table(s), the worse the performance.

I'm not sure what database you are using, but from a SQL Server perspective you would be well served learning how to use SQL Profiler. You can also view the execution plan of a query through SQL Management Studio, this will point out where the performance problems in your query might be.

Answer 3

I debugged with the method shown here and one of the method worked for me.

I checked for the query running for the longest time and came to know that certain queries was stuck and was running since more than 3-4 hours. To check since how much time the query is running, run the following command:

SELECT max(now() - xact_start) FROM pg_stat_activity
                               WHERE state IN ('idle in transaction', 'active');

If this is more than an hour, than this is the issue. Kill the long running connection and limit the max age of the connection from application side.

Answer 4

The most important thing for debugging SQL Queries is SQL Server Profiler http://msdn.microsoft.com/en-us/library/ms187929.aspx

This will give you reads, writes, time spent, etc.

Execution plans are also very helpful and will show you whether it did a scan instead of a seek or vice versa. Look into these as well http://msdn.microsoft.com/en-us/library/ms178071.aspx

Answer 5

If you truly are interested in performance tuning database queries you need to get a big fat book on the subject for the database of your choice. Performance tuning is very database specific. That said, one commmon issue in tuning queries that you must be aware of no matter the database is that the tuned query should return exactly the same result set as the original query but be faster. Sometimes people tuning forget this basic thing and end up with a query that is faster but doesn't do the same thing.

Problems in database queries -

Wrong results - the query doesn't actually do what you want it to do

Performance - the query times out or is too slow. Many things can cause this but a couple of common causes are bad database design (do not use EAV tables for instance), use of poorly performing query techinques such as correlated subqueries, looping through records one at a time, lack of correct indexing, statistics out of date, queries that are not sargeable and of course hardware and network issues.

For Inserts,updates, deletes - there can be problems with data integrity due to poor database design (lack of foreign keys, lack of default values, lack of constraints) or query design. SOmetimes people choose to make thier own autoincrementing field instead of using whatever comes with the datbase. These can run into concurrency issues.

Security - database does not properly protect it's data (encryption of Personal data, coding to avoid injection attacks, limiting rights to do things to the dat, etc.)

Lack of testing of edge cases - this is a frequent problem in dynamic SQl and triggers especially when developers only consider the most common case and the thing breaks when the edge case hits it.

Answer 6

This is a good article on how to optimize your SQL statements and things you have take into account: