Can I improve performance by refactoring SQL commands into C# classes?

Question

Currently, my entire website does updating from SQL parameterized queries. It works, we've had no problems with it, but it can occasionally be very slow.

I was wondering if it makes sense to refactor some of these SQL commands into classes so that we would not have to hit the database so often. I understand hitting the database is generally the slowest part of any web application For example, say we have a class structure like this:

Project (comprised of) Tasks (comprised of) Assignments

Where Project, Task, and Assignment are classes.

At certain points in the site you are only working on one project at a time, and so creating a Project class and passing it among pages (using Session, Profile, something else) might make sense. I imagine this class would have a Save() method to save value changes.

Does it make sense to invest the time into doing this? Under what conditions might it be worth it?

Answer 1

Serializing objects into the session can be costly in itself, but most likley faster than just hitting the database every single time. You are benefiting now from Execution Plan caching in SQL Server so it's very likely that what you're getting is optimal performance out of your stored procedure.

One option you might consider doing to increase performance is to astract your data into objects via LINQ to SQL (against your sprocs) and then use AppFabric to cache the objects.

http://msdn.microsoft.com/en-us/windowsserver/ee695849.aspx

As for your updates, you should do that directly against the sprocs, but you will also need to clear our the Cache in AppFabric for objects that are affected by the Insert/Update/Delete.

You could also do the same thing simply using the standard Cache as well, but AppFabric has some added benefits.

Answer 2

Caching is almost always underutilized in ASP .NET applications. Any time you hit your database, you should look for ways to cache the results.

Answer 3

If you have look up data only, you can store it in Cache object. This will avoid the hits to DB. Only data that can be used globally should be stored in Cache. If this data requires filtering, you can restore it from Cache, and filter the data before rendering.

Session can be used to store user specific data. But care must be taken that too much of session variables can easily cause performance problems.

Answer 4

This book may be helpful.

http://www.amazon.com/Ultra-Fast-ASP-NET-Build-Ultra-Scalable-Server/dp/1430223839

Answer 5

If your site is slow, you need to figure out what the bottleneck is before you randomly start optimizing things.

Caching is certainly a good idea, but you shouldn't assume that this will solve the problem.

Answer 6

Use the SQL Profiler to identify your slowest queries, and see if you can improve them with some simple index changes (removing unused indexes, adding missing indexes).

You could very easily improve your application performance by an order of magnitude without changing your front-end app at all.

See http://sqlserverpedia.com/wiki/Find_Missing_Indexes