In Databases, If A Query Is Taking A Lot Of Time To Execute, What Could Be The Reason If It Worked Quickly Only The Day Before?

There are two things which, if both are true, could make the query run much more slowly.

The first is that the database now has a lot more data in it and so there are more rows to be searched. But usually this is unlikely to happen the next day, unless you have been using a small set of data in some tests, and then uploaded a huge set of data for the final production version of the database. Even then this is unlikely to have a big effect, as indexes are designed to remove this problem. You do use indexes, don't you?

The most obvious one is that the query is badly written and has several extra steps in it which force it to re-do things again and again. A typical example would be a query with a sub query inside it. Often the sub-query can be run once, the data stored in memory, and then each row compared to the stored data. But if the sub query has to use data which is specifically related to the main query, then it has to run through the entire table every time it examines a single row for the main query! So for 100 rows, 100x100 execution steps occur.

So if the query is badly written and the database has got a bit bigger, the query will get slower and slower as more data is added.

If it is really badly designed, it might be doing a cartesian join between tables and everything will grind to a halt if that occurs on a big data set!

Add Comment

Report