Speeding up a Slow Website Part 2 – SQL Server
Following up on the previous article – speeding up a legacy website – this posting shows how we tackled the SQL database portion of the optimization.
Database Design
Any developer can create a database. Most developers can create a relational database and some developers can normalize and optimize a database. However very few developers can design a database that will perform effectively over years and as transactional volume increase. Initially a poor design is not apparent due to the lack of traffic and companies marvel at how quick and easy the process was. As traffic increases, the database can become a bottleneck and site slowness and other problems start to appear. Companies typically respond to this by asking developers to optimize queries or by throwing in more powerful servers, memory and updating the operating systems and database software. This however only masks the problem for the short term.
ROI to Replace a Legacy System
In many cases, the ROI to rebuild the database just isn’t there. A brand new database design can be costly, require a much longer deployment and outages. The database is the central piece and will usually require changes in internal and other systems and processes, requiring changes for both consumers and users.
Client Challenges
Recently a large e-commerce company came to us with a challenge. The client had a 16 year old website running a classic ASP website on Microsoft SQL Server that was terribly slow. They asked us to identify quick and easy ways to make it faster. Their 80 person development teams had already tried optimizing the processes and identified the database as the culprit. They even built an extensive caching mechanism that generated HTML files multiple times per week. In spite of the efforts, the e-commerce processes were still extremely slow with average page load times of 30 or more seconds. With their busiest shopping season of the year approaching they needed results quickly. The client requested a complete review with a recommendations presentation within two weeks.
Vimware Approach
We approached the problem by looking at the code and the database. The fundamental issue was that the developers had tried to put as little stress as possible on the database and that resulted in a less than optimal design. In this post, I will highlight the suggestions we made for the database, and will also include a link to our detailed Recommendations-SQL-Server-Database-Optimization with all company information redacted.
Vimware Solutions
The short-term solutions focused on the following:
- Incorrect indexing strategy
- Incorrect or missing clustered indexes
- Too many reads
- Incorrect TempDB design
- Irrelevant or unnecessary data being returned
- Inefficient and poorly written SQL Server queries.
- Incorrect SQL Server settings.
In every case, there were improvements that could have been made.
Medium to long-term solutions were to point out:
- Re-design sections of the database
- Remove SQL Queries embedded in code
- Modify the process of database changes
- Add senior database engineer(s)
Download our recommendation here:
Vimware-Whitepaper-Recommendations-SQL-Server-Database-Optimization
Vimware provides IT strategy and software development services in the Greater Los Angeles Area. We specialize in helping organizations transition and reinvent themselves into the tech world. Our clients are across many industries including academia, consumer goods, entertainment, and healthcare. Expertise covers iOS, Android, Windows, LAMP and MEAN technology stacks. Vimware is a Select Partner within Amazon’s AWS partner network.