Here are the top reasons for slow database:
1.) Often a database is originally designed with a small set of tables and small dataset just to “get it working” without regard to expansion and growth of the dataset. As the business grows, so does the volume of data the DB must crunch through. For example an inventory table that started out with 100 inventory items is quite fast search and retrieve data. However when that inventory table grows to thousands or tens of thousands of items, the original search routine can no longer keep up. This why data needs to be designed with fast indexing in mind. The solution for this is to redesign the tables or create faster retrieval method using indexing.
2.) Data from multiple tables needs to be “joined” together to create a cohesive output but the “join operation” implemented in the database access routines are designed poorly. For example an inventory table may need to be joined with a separate supplier table to get a list of all vendors for that product. There are multiple ways to “join” these tables and if done incorrectly will slow the query down dramatically. This can be sped up by examining and replacing incorrect joins with more efficient “inner” or “outer” joins.