Tagged as

Stats

Analyze performance from your .NET code through to SQL

Understand the relationship between your .NET code, the database, and HTTP requests, with ANTS Performance Profiler from Red Gate. Access all the performance data you need to solve the problem in a single tool.

Editorial Note

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

Understanding why an application performs badly can be a tedious process. With so many possibilities, it’s difficult to know where to start, especially if you’re
working on an ASP.NET application that relies on database queries. The latest version of ANTS Performance Profiler provides you with all the contextual
information you need to identify the bottleneck.

Users have reported that the site's location search functionality is slow. The search functionality is designed to return a list of events near a
certain location. To investigate, let’s launch ANTS Performance Profiler and take a look at what’s going on behind the scenes in NerdDinner.

ANTS Performance Profiler will automatically launch NerdDinner in Internet Explorer. Now that we have the application up, running, and being profiled, we can start investigating
the problem with the search, by typing a place name into the search bar and hitting ‘Search’.

We can see that the CPU usage grows to almost 200% (100% for two CPU cores), and remains high even after the first results were returned. This is a clear indicator of a
performance bottleneck, so let’s explore the results in ANTS Performance Profiler.

At the top of the call stack, ANTS Performance Profiler shows the code that contributes the greatest proportion of CPU time. In this case, we can see that the .NET methods
triggered by the SearchByLocation HTTP request are at the top, accounting for almost 95% of the total time spent during the profiling session.

Looking down the highlighted hot stack, which shows the most time-consuming stack trace in the selected region, we can see that this request
called a .NET method, NerdDinner.Models.DinnerRepository.NearestDinners(double latitude, double longitude). This method was hit nearly 4000 times - as was the SQL
SELECT query it ultimately runs.

If we select the method’s parent, NerdDinner.Controllers.SearchController.SearchByLocation(float latitude, float longitude),
we can view its source code. This shows us that the method retrieves the full list of all recorded dinner events from the database:

The method NerdDinner.Models.DinnerRepository.FindByLocation(float latitude, float longitude) then tries to process the result set in the web
page, to filter by location:

These methods are good candidates for optimization. The same results could be achieved via AJAX calls, and by returning from the database dinners
that meet specified latitude and longitude criteria only.

Switching to the Database Calls view, we can see that the query which returns the full results set was run thousands of times. Altogether
the site spent over 30 seconds just waiting for instances of this query to return their first result:

Again, it's clear that this very broad request is being run repeatedly, contributing to a large total running time. It would be more efficient to run a more precise request fewer times.

After profiling, we now have a clear idea of which HTTP requests are associated with slow performance, and which of our .NET methods contain the source of those slowdowns. We have a
clear understanding of how the .NET code is calling the database, and how the database is performing. Having access to this data in one tool means we can
quickly see which methods to rewrite to remove bottlenecks, and the steps we'll need to reproduce in the application to check that the problem has gone.

Share

About the Author

Red Gate Software makes ingeniously simple tools used by over 650,000 Microsoft technology professionals working with .NET (incl. ASP.NET), SQL Server, Azure, and Oracle. More than 100,000 companies use our products, including 93% of the Fortune 100.

Our philosophy is to design highly usable, reliable tools which elegantly solve the problems that developers and database administrators face every day.