If you run a SaaS product with a front-end UI used on the web, you may have received performance complaints from your users. These complaints are hard to manage as making positive changes to the performance can take time.
Businesses start small with little data; as they accumulate more data, queries start to take longer, and rendering slows down. A higher number of users means your infrastructure is working harder, and this means you can start to face performance issues with no one single identifying issue.
It’s usually a combination of issues that present the same symptom of performance getting worse. Tackling these issues requires a review of the entire infrastructure and how the front and back end communicate.
I’ve collated a list of things I’ve encountered over the years which should help you make a high-performance UI.
I’ll be working with MySQL as the primary storage method.
The real goal here is to answer the question, “Is this using as few clock cycles as it could?”.
High-performance UI needs backend too
Your UI is fed with data from a storage mechanism. It could be a MySQL database, flat files, JSON files, NoSQL – anything, but I will be working with MySQL in this post. The flow of an application is this: typically, a backend process that takes an input & calculates the output, and stores it. Users log in, redirect to a page, queries happen, and data appears. It’s this process which we need to make faster.
Is your database efficient?
Are you using LIKE ‘%something%' in your queries? If so, this will prompt MySQL to do a full table scan. This can take considerable time if you have 100,000 records and need 10. If you must use LIKE, use LIMIT to assist MySQL in not having to scan all of the records.
While LIKE has its place, if you need to use this for finding regular, repeating data, it’s worth analysing if you are storing it in the best way possible. Can what you look for in LIKE be stored in its own column with an Index?
Do you need to confirm the existence of row(s)?
If you need a “yes” or “no” answer to a query, quite often, you will see something like this:SELECT COUNT(*) FROM someTable WHERE .... and then in code, you will see something like
var query = executeQuery("SQL");
if(query.length > 0){
//Row exists
}Consider changing your query to limit one row; if you don’t need an exact number, don’t query it.
You could do this: SELECT COUNT(*) FROM someTable WHERE … LIMIT 1
If a row exists, you get 1 (yes); if not, you get 0 (no).
Be cautious of cross-database queries.
While the performance of cross-database queries can be fast, the use of LIKE and lack of indexing, foreign keys etc., can create problems.
Queries which are dependant on multiple tables across multiple databases require complex reworking if you start to shard your databases.
Are you paginating results?
Use LIMIT to skip previously loaded rows. LIMIT 20,40 will skip 20 rows and take 20 rows.
If you are receiving 40 rows and doing something like this in your code, rework your query and database structures to avoid it.
IF ROW INDEX < 20:
CONTINUE50% of the data you query in this example is unwanted, yet the server still had to honour that query. If 100 users load the same page simultaneously, 50% of your computational capacity is wasted.
If the data isn’t needed, don’t ask MySQL to give it to you.
Are you using strings for constant values?
String comparison in MySQL is slower than integer comparison. It’s also more efficient for storage. You can read a little more about this here, but simply an 8-bit number has a max value of 255. An 8-bit number will take 1 byte to store. In MySQL, this is referred to as TINYINT.
A varchar field with a value of something like “pending”, “active”, “complete”, “shipped” etc will take 1 byte per character (assuming its ASCII encoding, although it could go up to 8 bytes for UTF-32 encoding), so if you have the status of “pending” that would store in MySQL as 7 bytes.
Indexes may speed this up a small amount, but if you presented “shipped” as an integer value of 4, it would be more efficient overall. You just need to keep a mapping of integer values and what value means the required status.
In the book High Performance MySQL, it says that “smaller is better”. You can read it here.
In general, try to use the smallest data type that can correctly store and represent your data. Smaller data types are usually faster, because they use less space on the disk, in memory, and in the CPU cache. They also generally require fewer CPU cycles to process.
If you can represent a 7-byte value as 1 byte, you’ve reduced your storage requirements for a single field by ~85%, and you’ve reduced the number clock cycles required to do WHERE status ='pending' by changing it to WHERE status = 4
Front end
I’m not a front-end designer, so my input here is limited, but I recommend the following.
- The UI isn’t the place to make decisions. It’s the place to display data, so keep your decision-making to a minimum. It’s acceptable to make decisions on what to display,
if true show green else red, but you shouldn’t be making business decisions here. - Tools like DataTables can often be more efficient in displaying data than growing an inbuilt system.
- Use concepts like infinite scrolling, AJAX requests, lazy loading etc. to make the app more performant.
- The UI in a web browser is on your client’s machine and you don’t know the performance of that machine. It might run fine on your laptop because it’s a robust development machine. However your client might still be running Windows 7. According to Statcounter, 11% of computers globally still are in August 2022.
- Consider using Lighthouse and exploring LCP and CLS to measure how responsive your front end is.
The bit in-between effects high-performance UI
This is perhaps the most important. Database designs, once in production, are quite often hard to change. Organisations face limitations on what, when and how they adapt these designs.
Changes are added around existing infrastructure as the project manager thinks “it’s too difficult to change the core, and we are just testing this right now”. In my experience, “just testing” evolves into a permanent feature due to typical everyday factors (timescales, budgets, sales etc.)
This is where the ‘bit in between’ can help.
When you log into a social network, the page doesn’t start with this query:
SELECT * FROM feed f WHERE userId = 12345678901234
JOIN photos p on f.id on p.feedIdWhile databases are fast, they’re not fast enough and expensive to scale. They require a lot of disk IO, memory and conflict with writes and other users.
You can shard, scale, and buy bigger machines, but these all bring issues, including complexity and cost.
In any social network, the code displaying the feed queries a data store where the data needed is kept in the format required to display it. These data stores are often high-speed, low latency systems.
Let’s study the round trip of this a little bit closer.
MySQL
MySQL is a primary storage method; Facebook and Twitter still use it.
A query to a MySQL server roughly follows this kind of flow:
- Generate query including joins and a user ID
- Open connection & authenticate
- Send the query
- MySQL server obtains the disk files, waits for disk IO & file locks if not ready
- Executes the query
- Returns data from the database
- You manipulate it, however (and if) required (usually on the web server side with some while/foreach loop)
- Send the response via AJAX call
This is all done in real time while the user waits for the feed to load. If you’re not using LIMIT, up to 50% of that time was for nothing.
It’s worth noting that because the user ID makes each query unique, there may not be significant caching gains internally. MySQL may reuse file handles if this is a frequently read table, but the query and results are different.
Cached method
This could be almost any other data-storing method; I won’t fixate on one particular. It could be a mixture of Memcached, Mongo, DynamoDB, and ElastiCache.
The flow would be something broadly similar to this:
- Get the user ID
- Connect to the connection mechanism
- Send the User ID as the key/query
- Get the data (value) from the server
- Send the data via AJAX
You would still need to do the missing steps here, but those are done before. This is explained below in more detail below.
This means that the response to the UI can be a few milliseconds, not a few seconds, because the data is ready to go.
How do you update the data?
Sticking with social networks as an example, each person has a feed that displays relevant things. Twitter and Facebook all have feeds.
Your friend post’s a status update, and the server at the social network receives the request. It logs it into MySQL and stores it (INSERT). A second process then starts, which looks at your friend’s friend list and inserts that new item into each of their friend’s feed cache.
This is called a “fan out” approach. If you are not friends with the person who just posted an update, you won’t see the post anyway, so you don’t need to worry about the other 99% of users.
Find the relevant people to a piece of information and update it for those users.
Yes, you can have “public” posts – but these are just a secondary cached feed where the logic says if post is public, insert into public feed cache so that would go off to another caching instance where it can be kept for users who might view somebodies public profile.
Social networks like Twitter use variants of Memcached to keep data in memory. You cannot get a faster storage mechanism than RAM; everything else is slower. You can easily achieve under 10ms response times by getting data from RAM.
AWS provide Memcached services and similar ones, and storage mechanisms don’t have to be overly complicated. The key takeaway should be this: Get your data ready in the format needed before your user asks for it. Doing things on demand is expensive when you need a high-performance UI.
Caching up
When a SaaS business grows with many users, the application’s design needs to evolve beyond simple queries on demand.
Keeping your data in a MySQL server is friendly and queries are easy to write, on-demand execution each time is not fast. The more data you add, the slower these systems become.
Having “a middle bit” helps keep your backend infrastructure mostly untouched. The UI queries a different place for sourcing its data. You inherit responsibility for invalidating caches, regenerating them, and handling small changes in data but these are justified costs. Especially if your users are given a high-performance UI at the end of it. After all, they pay the bills, and complaints about speed will not go away.