[ASP.NET, SQLServer] How to fix Performance Problems - guidelines

Scenario/Problem: You have an ASP.NET (framework x.x.) application and some pages are taking forever to load up.

Guidelines: Follow these steps:

1. If using DBs: Are you indexing your DB? If not you should consider it (up to 30% speed increase, depending on you DB design)
2. Check your controls ViewState: are you sending unuseful stuff back and forth? If you're generating/populating dinamically some control you should disable its viewstate. Use ASP.NET trace to check controls viewstate size (DRAMATIC speed increase, depending on your app)
3. Look at your T-SQL stored procedures: are you abusing temporary tables or dynamic SQL execution? If so it's not a good idea, use SQL profiler to check for stored procedures recompilation-execution time. If you don't have stored procedures you probably should use them
4. Look at your code behind: are you round-tripping more than you need? Are you sharing open connections to DB when you can (use SQL Profiler to spot how many connections you are opening and closing)? Are you mis-using some controls (huge drop down list can cause major delays)? Use ASP.NET trace to locate hot-spots and long latencies.

Real World Case: I don't feel much of a butcher these days: I was working on a web app [ASP.NET 1.1, SQLServer2000] developed by someone else and i realized butchery is a fine art and I still got a long way to go.

My main task was to speed things up. It was taking 18 seconds to load a single page, with an average amount of data (nothing major). I first tried to put some indexes on the DB (1), which didn't have any, but not having at the time much experience on DB indexing I put my life in the hands of the Enterprise Manager Index Tuning Wizard (painful experience), knowing it would have butchered my DB, but hopefully just a bit, speeding up things for me in the end. It happened, but from 16-18 seconds the guilty page went down to 12-13, which was something but not what I was hoping. The wizard claimed a 28% speed increase: true, but not enough.

After this delusion, I went on inspecting the trace (enable it setting Trace="true" in the top page declaration, or @ app level from web.config) and I noticed KBytes of useless stuff being sent back and forth as ViewState (2). The app had a coulple of user controls dynamically generated every Page.Load, so I just disabled the ViewState for those (EnableviewState = "false") and the app speed dramatically increased (50% in my case). Good, but still it was taking 6-7 goddamn seconds to load. I also took off the SmartNavigation="true" page property: it might be the pre-AJAX coolest thing but it's deprecated and it saved me almost 2 seconds: down to 5.

I started looking at the code, spotting un-necessary open/close operations for connections that could've been shared, loads of butchery in there, of the finest quality, and I noticed ALL the stored procedures were being recompiled (3) because of a massive use of temporary tables and dynamic SQL, even when non necessary (if the stored procedure is being recompiled there's no gain speed, ergo no point in having it as stored procedure). I didn't really want to change the code, a work of art is a work of art, so I decided to roam a little bit more through the trace looking for hot-spots.

I noticed the page was laoding pretty fast looking at the trace latencies. I put some Trace.Warn("HereandThere") but there was nothing major, the whole page was taking only around 1 sec to process and render, but I was still seeing the content trhough the browser after 5 secs. Then again a look at the ViewState and controls sizes and it happened: I saw a 900KB dropdownlist (4). Turned out that drop down was being populated with more than 10.000 record (yes > 10^4) and the browser was obviously taking forever (around 3 secs) to load it. Took that out (set up a popup with only the dropdown for that selection) and EUREKA: loading time went down to 1-3 secs, which considering shitty server and crappy network is more than acceptable (compare it with 18 seconds if you don't agree).

As I said, there was (and there is) a lot more to change and optimize in this app, as per stored procedures code and code behind, but the art is art and I wanna keep this mess untidy as long as I can, to inspire me and to remind me what butchery truly means.