Other Tips&Tricks

How to lower number database queries?

You are working on big project like CRM, CMS or something similar and you want to present as many information to your users as possible. But that comes with a cost. If you have large number of queries to your database, it can create bottle-neck, or it can slow down your entire application, or (at best) user 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 many 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 million, rows of data. At that level every byte counts (trust me I’ ve been there).

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

What does above mean? It means you have to query only 10-20 rows of data, per time, and you, probably, don’ t need 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 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 database. You can save it to localStorage, to cookies, or if you have to you can use Sessions on server side.

Or if you have a opportunity use service workers to cache all data to browser and than 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 server. This is similar to caching except this is “permanent cache.” How so? No matter whether your user visited your app/web earlier from exact same device, you will be able to present data.

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

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