As part of my Share Your PurePath and Performance Clinic initiatives, I get to see a lot of application performance problems where I help people like you figure out what’s wrong. Last month I gave insight into how users Russell and Joe optimized their Java and Web applications for size and speed. In this blog, I will share lessons learned from a C# application fellow user Ram (works as a performance engineer for a large financial organization) is responsible for.

Chatting with Ram, he explained that most of their critical applications are built on Microsoft’s .NET Platform. They experienced intermittent problems in their production environment on two of their critical end user transactions: Query Available Withdrawal Balance and View Transaction History. Both of these transactions periodically experience long response times (> 2 minutes), sometimes failing with HTTP 5xx and often heavily impacted by high Garbage Collection activity (87% of Execution time spent in GC).

He installed the Dynatrace Free Trial on the environment and by looking at the data we identified some “classical” performance problem patterns which they can finally address to solve this problem:

  • Wrong sizing of available worker threads
  • Loading too much data from database
  • Excessive use of Database Connections
  • Expensive String Concats instead of StringBuilder Usage
  • High Memory Usage due to bad SQL Patterns and String Concats

Let’s dig into our findings – and please remember – if you don’t feel comfortable doing this type of analysis check out Share Your PurePath

Finding #1: Long Wait for Busy Backend Worker Threads

Looking at the Elapsed Time column in the PurePath dashlet makes it easy to understand how a distributed multi-threaded transaction gets executed. Out of the 101s it took to query the available withdrawal balance the main ASP.NET Thread with the ID 32 waited 87s to pass the control over to the backend worker thread with ID 30.

Looking at Elapsed Time makes it easy to understand where your bottlenecks in a distributed, multi-threaded applications are
Looking at Elapsed Time makes it easy to understand where your bottlenecks in a distributed, multi-threaded applications are

Next Steps: They need to analyze peak load on their frontend and figure out how many backend worker threads they need to handle the load. The architecture also allows them to put the worker thread on a separate machine and load-balance a cluster of these backend job processors. Optimizing the execution of these worker threads will also greatly improve the situation based on the additional findings we made.

Finding #2: 600 SQL Executions each on separate DB Connection

Our next finding came from when we took a closer look at the backend worker thread that gets called from Query Withdrawal Balance: It is a best practice to return the DB Connection to the DB Connection Pool as soon as you are done with executing your SQL Statements. But executing each individual SQL Statement on a separate connection from the pool is not a good practice – especially when you are executing 600 Statements and each of them requires you to take a connection out of the pool and putting it back.

Transaction Flow shows that there are 600 SQL Statements. In the PurePath Tree we see that for each SQL a connection is taken from the Connection Pool.
Transaction Flow shows that there are 600 SQL Statements. In the PurePath Tree we see that for each SQL a connection is taken from the Connection Pool.

Next Steps: Optimizing database access will improve overall performance by having:
a) fewer roundtrips to the database when offloading some of that logic into a stored procedure;
b) less overhead and more available connections when executing fewer SQL statements and not additionally executing those that logically belong together on the same connection
This will speed up the backend worker thread which means they can handle more incoming frontend requests much faster.

Finding #3: N+1 Query Problem

There is a well-known database access pattern called “N+1 Query Problem.” It means that you have one SQL Statement to query e.g: the list of AccountIDs. Then you iterate through every single AccountID and execute another SQL to retrieve the account details. This results in a lot of SQL Statements of the same type. Just as we can see here where individual statements are executed up to 67! times. As every SQL is executed on its own DB Connection it is not even possible to prepare the statement:

Looks like a classical N+1 Query Problem with the additional problem of not being able to prepare the statement as every SQL is executed on a separate connection.
Looks like a classical N+1 Query Problem with the additional problem of not being able to prepare the statement as every SQL is executed on a separate connection.

Next Steps: Many of these SQL Statements can be avoided because they query the same data that is already available (e.g.: use client side caching) or by moving certain data processing logic into a stored procedure.

Finding #4: Extensive use of String.Concat leads to high GC Time

Users of their financial platform can download transaction history as a CSV file. This CSV file is built by using the very memory intensive String.Concat function. That and other memory intensive implementations such as loading that much data with using lots of connections will ultimately trigger the Garbage Collector to kick in – impacting the overall execution time of critical transactions.

The best practice when building large strings is to use helper classes such as StringBuilder which doesn’t need to copy large blocks of memory whenever you are adding new characters to the buffer.

Building large output strings can be very memory intensive when using String.Concat instead of classes such as StringBuilder which are memory and performance optimized for that use case.
Building large output strings can be very memory intensive when using String.Concat instead of classes such as StringBuilder which are memory and performance optimized for that use case.

Next Steps: For building large output strings never use String.Concat. Instead, rely on classes such as StringBuilder which are optimized for these scenarios. This will keep memory overhead low and reduces the GC impact on all transactions in the application.

If you want to learn more about memory diagnostics check out my Hands-On Tutorial for Identifying Memory Leaks in .NET and Java based applications.

Let me Help you with your Performance Problems!

Are you dealing with application performance issues? It doesn’t matter if you are a developer, a tester, an architect, responsible for running an application or a business owner, feel free to contact me if you want any help and be sure to check out the following links:

Let’s work together to get your apps faster and let’s give back to the larger performance community by sharing some of our findings with the world.