Tips&Tricks Other

How to lower the number of database queries?

You are working on a big project like CRM, CMS, or something similar and you want to present as much information to your users as possible. But that comes with a cost. If you have a large number of queries to your database, it can create a bottleneck, or it can slow down your entire application, or (at best) users will experience some slow time. That is not good (you know that otherwise, you would not be a developer).

So I will try to help you with several ideas on how to get as much data as possible without killing your database, bandwidth, server, or your user’s time.

1. Avoid querying data you don’ t need

As much as this is obvious every now-then we think: “This probably will not create that much of a fuss!” And you are probably right, as long as you don’t have thousands, or even millions, rows of data. At that level, every byte counts (trust me I’ve been there).

For example, if you have a table with a list of people, or products, or anything you have to realize two things: 1. you can present only some data on your screen (regarding the width), 2. if data is not on a screen user will scroll or even better use pagination (pagination is a better choice if you ask me, regarding software like CRM and CMS).

What does the above mean? It means you have to query only 10-20 rows of data, per time, and you, probably, don’t need the whole row. Just data that you will show to your user and data that is crucial for further actions, like IDs and stuff.

2. Cache data

There is always data that is queried every time even though it’s not changing that often, or not crucial for your user’s business. For example, You use DataTables to present a list of 100.000 products in your client’s CRM. You don’t have to query every time to count how many products are there in the database. You can save it to localStorage, to cookies, or if you have to you can use Sessions on the server-side.

Or if you have an opportunity use service workers to cache all data to the browser and then change it only when it’s updated.

3. Use help

There is nothing wrong with asking for some help. Instead of using database queries for data that is not sensitive and not changing so often, you can use JSON to save it to the server. This is similar to caching except this is a “permanent cache.” How so? No matter whether your user visited your app/web earlier from the same device, you will be able to present data.

A drawback with this technique is that you have to update JSON whenever you update data presented in JSON. Also, you will have duplicates. And if you are working with a high traffic site/app you may have a problem with reading data, this also depends on your server’s architecture.

If you have any questions about me or my blog you can find me on Twitter, or you can learn more about me and my blog on this link.