How do I make my website run faster 9 May 2022

How do I improve the performance of my website?

A real world client experience.

As you will be aware, we never seek to make light of the situations we encounter. They should be discussed though, to help others prevent similar disasters happening to them. If you have any concerns about what you see in this discussion, you should definitely get in touch with us at Info Rhino. In this article, we will discuss what we see is common in many businesses and is likely to be the normal operating model for companies with websites.

If you are a website development company, it is unlikely that you will have the database expertise that we have at Info Rhino. We welcome contact from other companies, service providers who wish to optimise the performance of the website platforms they deliver to their clients.

What are the typical ways recommended to improve the performance of a website? Typically, the focus is on the size of the files using bundling and compression, not having large images, and caching.

The secret - it is often the database which causes a website to perform badly.

Companies seek to focus on the website elements for performance, but there are many other reasons why websites fail to run effectively. Indeed, it is not just the performance of the website. Many solutions are on their knees and likely to fail at any moment. The culprit for this is the lack of expertise in Business Intelligence and Data Modelling. The website designers are unlikely to have the expertise in designing effective database structures, and will often combine data capture with reporting. This contention on the database rarely works, and won't scale.

Example of a large data base reporting solution and web front end

The client had an important database that is public facing and provided information and support to thousands of users. The database itself was a SQL Server database, hosted on Azure. The website is in ASP.net MVC5, There is a view to move towards DotNet Core 6 as it offers Long-term Support, and implement a rewrite of the solution and architecture. As you may be aware, we don't divulge our client portfolio because we would not wish to cause reputational damage to our clients. So we always speak in general terms. The example we are providing is very typical, so don't be surprised when things sound familiar.

The database

The database is part relational database - collecting information added by users, some being manually entered into forms and others being uploaded via spreadsheets and other file formats. The other role of the database is for reporting. The database hasn't been developed as a data warehouse and so attempts to create tables which resemble a data warehouse have been undertaken but with disastrous consequences. Most tables are designed poorly and aren't in consistent form for effective reporting. Issues found include;

• Too many indexes on many of the tables.
• No scheduled statistics gathering for optimising reports.
• Poor design of primary keys within the database and usage of clustered indexes were incorrect.
• Use of intermediate tables that served no benefit and lead to extra work.
• The lack of use of more advanced features within SQL Server such as table partitioning, column stored indexes, and in memory tables and table variables.
• No index management. So - zero index rebuilding leading to almost complete fragmentation of the database's indexes.
• Too much good duplicated code and stored procedures doing almost identical things. When perhaps a more dynamic approach to generating queries would have been optimal.
• Audit logging tables, which bloat the database without real benefit.
• Significant use of poor table design that would leave too many updates on the same row, which in itself causes more fragmentation.
• Passing in of the limited strings as parameters, which would therefore lead to extra work by the database just to get parameter lists for queries.
• a lack of use of more effective, more modern approaches to writing SQL, not using newer features that can help performance.
• Inconsistent data results as a feature of inadequate data modelling.

At this point you may be thinking. This sounds pretty bad. I had no idea that databases could be misused and neglected. Sadly, this is really common. Website Developers may have some skills in databases but won't appreciate how important it is for databases to be maintained.

In this scenario, it can be very hard to decide how to strategically solve the problems with this database. We have several issues;

• Fragmentation
• Inconsistent reporting
• Too much code
• Data contention (record locking)
• Storage issues
• Regression risk, if we go around changing data and table structures, we will see differences in results.

The above issues can become circular in nature. Fixing one may lead to more problems in another and it can start to become a never-ending challenge. An example is we may decide to try and fix the data model by resolving some of the table structures -if we don't have an effective indexing and database maintenance strategy, it can become a pointless enterprise.

The action plan

This approach makes sense because it allows us to revisit the previous step in an iterative approach - we build on each step and refine our approach if we require it.

The action plan steps to remediate the database structure;

1. Review phase. Assist the database, its structure, And how data moves in and out of the database?
2. Index analysis. Understand what types of tables exist. How tables are being indexed, the storage size of indexes, and the usage of indexes.
3. Index rebuilding strategy. We will be dropping indexes. We must have a mechanism for rebuilding them.
4. Table statistics gathering analysis. For tables to be able to optimise queries. They should have an understanding of the data cardinality.
5. Table statistics optimisation strategy.
6. Database feature analysis. What features does the licenced version of SQL Server permit to be used to help with optimising the database?
7. Optimization automation. Creation of necessary jobs and tasks to help automate the optimising of the database.

What made it harder to implement an effective solution?

Clients are using Microsoft Azure with an SQL database features, meaning the Azure SQL Server database has does not have SQL Server maintenance plans. After reviewing the features available in Azure, we set up Azure data factory pipelines for the client.

Lessons learned on why website developers fail to keep database optimised.

Databases not only need to be designed well, the data usage thought through, they need to be maintained. Website developers have to keep abreast of their own technology and continue to push the boundaries, which doesn't typically involve understanding how to build effective databases. One of the biggest issues we are seeing and an emerging trend - are the multitude of websites that perform reporting without an effective data warehouse or multidimensional database behind it.

Website developers will often shy away from understanding and working with data, which is a big error because the data is what brings websites alive to their audience.

If you are a client who has purchased a website for my website supplier, you should be wary if the supplier does not seek to offer database maintenance as part of their package.

Written with StackEdit.