Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET
Article

Analyze performance from your .NET code through to SQL

17 Jul 2012CPOL3 min read 37.8K   12   1
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.

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.

Walkthrough

Let’s put ANTS Performance Profiler to the test by profiling a sample ASP.NET MVC application called NerdDinner. For the purpose of this walkthrough, Jeremiah Peschka has modified the original NerdDinner application from CodePlex, so it now contains performance issues. You can download our modified version here. The NerdDinner site includes several pages that rely heavily on database queries, as well as some static HTML.

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’.

While we use the site, ANTS Performance Profiler’s timeline shows the application’s CPU usage:

app_nerddinner_timeline

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.

app_nerddinner_results

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:

app_nerddinner_slowmethod

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:

app_nerddinner_processing

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:

app_nerddinner_dbcalls

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Red Gate Software Ltd.
United Kingdom United Kingdom
Redgate makes ingeniously simple software used by 804,745 IT professionals and counting, and is the leading Microsoft SQL Server tools vendor. Our philosophy is to design highly usable, reliable tools which elegantly solve the problems developers and DBAs face every day, and help them adopt database DevOps. As a result, more than 100,000 companies use products in the Redgate SQL Toolbelt, including 91% of those in the Fortune 100.
This is a Organisation

1 members

Comments and Discussions

 
GeneralExcellent... Pin
Abinash Bishoyi5-Nov-12 11:26
Abinash Bishoyi5-Nov-12 11:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.