A SQL statement can be slow for a lot of reasons. Here, we will give a short list of these reasons, with at least one way of recognizing each.
The core issues are likely to be the following:
- You're asking it to do too much work
- Something is stopping it from doing the work
This might not sound that helpful at first, but it's good to know that there's nothing really magical going on that you can't understand if you look.
In more detail, the main reasons are as follows:
- Returning too much data
- Processing too much data index needed
- Wrong plan for other reasons
- Cache or I/O problems
- Locking problems
The first reason can be handled as described in the Reducing the number of rows returned recipe. The rest of the preceding reasons can be investigated from two perspectives:theSQLitselfand the objects that the SQL touches. Let's start by looking at the SQLitselfby running...