In the previous post we presented problems encountered by our client TescaraHats (name changed for commercial reasons), a European market leader in manufacturing customized hats. The company quickly realized that e-commerce performance is critical to the success of an e-commerce platform and that sales will not increase just because you have an application online.
We argued that while improving search engine ranking is important, you should never forget about the performance and usability in an e-commerce application. In this episode of our e-commerce performance series, we will analyze the impact that the backend performance has on your online sales.
Improve Backend Performance
The effort put into building a visually appealing and easy to use e-commerce site might be lost if you do not manage the performance problems at the backend.
The e-commerce site at TescaraHats consisted mainly of two tiers: TescaraHats Front (see Figure 1) and TescaraHats DB (see Figure 2). The APM tool used by the Operations team indicated that both tiers had performance problems. The front-end tier (see Figure 1) had most of the operation time spent on the backend; the DB tier (see Figure 2) indicated that most of the time was spent on the network.
Figure 1. A lot of time spent at the backend processing ↩
Figure 2. Most of the backend time is spent on transferring data with database query results ↩
To understand the cause of the performance problem the Operations team initially analyzed the DB tier and discovered that one of stored procedures was always slow in delivering results (see Figure 3). By looking at KPIs of all DB operations (see Figure 4) the team confirmed that the high network time component reported at the DB tier was, in fact, contributed by this single stored procedure: [Update_GalDisplayedhats].
Figure 3. One of the stored procedures is always slow in rendering results ↩
Figure 4. Only one stored procedure introduces delays on the network ↩
The Operations team wanted to further understand why this stored procedure was so slow. They drilled down to PurePaths representing slow stored procedure to analyze the transaction flow where this stored procedure is executed. The report in Figure 5 shows that most of the time is spent on acquiring connection handle, which usually means that the connection pooling is improperly configured or there are transactions that block the connection.
Figure 5. Transaction flow representing slow stored procedure indicates problems with acquiring connection handles ↩
The team opened another report showing database calls (see Figure 6) and drilled down through the slow stored procedure to a report (see Figure 7) showing PurePaths where this stored procedure was called. With that report the Operations team could determine which transactions were affected, i.e., where this slow stored procedure was called from. This information helped the team to resolve the problem faster.
Figure 6. Report showing the most affected database calls at TescaraHats: the stored procedure [Update_GalDisplayedhats] ranks at the top ↩
Figure 7. Report showing PurePaths affected by the slow stored procedure: by seeing where this query is called from we can speed up the problem resolution ↩
What About the Frontend ?
When it comes to backend performance problems caused by the database calls there are some problem patterns that you should check for:
- When the query execution takes too long you notice higher database time (see Figure 5).
- When the query produces a lot of data that needs to be pushed through the network your APM tool will show large network time contributing to the database operation time.
- When there are connection problems you will see them on the database report (see Figure 6).
- Finally, you will see in the transaction flow report (see Figure 8) when there is a single querycalled multiple times unnecessarily.
Figure 8. For each transaction the same query is called too many times ↩
Once we weeded out all potential causes of problems from the backend, it was time to move closer to the customer and check a number of potential network-related performance problems; continue reading our mini-series on e-commerce performance to learn more.
(This series is based on materials contributed by Pieter Jan Switten, Pieter Van Heck, and Paweł Brzoska based on original customer data. Some screens presented are customized while delivering the same value as out of the box reports.)