Can I improve performance by refactoring SQL commands to C # classes?
Currently my entire site is updating data from SQL queries. It works, we didn't have any problem with it, but it can be very slow sometimes.
I was wondering if it makes sense to refactor some of these SQL commands into classes so that we don't have to hit the database a lot. I understand that hitting the database is generally the slowest part of any web application. For example, let's say we have a class structure like this:
Project (consisting of) Tasks (consisting of) Assignments
If Project, Task and Assignment are classes.
At certain points in the site, you only work one project at a time, so creating a Project class and passing it between pages (using Session, Profile, something else) might make sense. I assume this class will have a Save () method to save changes to values.
Does it make sense to invest time in this? Under what conditions might it cost?
a source to share
Serializing objects in a session can be costly on its own, but most are faster than just hitting the database every time. You are now benefiting from execution plan caching in SQL Server, so it is very likely that what you are getting is optimal performance from your stored procedure.
One option that you can use to improve performance is animating your data into objects via LINQ to SQL (against your sprocs) and then using AppFabric to cache the objects.
http://msdn.microsoft.com/en-us/windowsserver/ee695849.aspx
As for your updates, you have to do it directly against the sprocs, but you also need to clear our cache in AppFabric for objects that are affected by Insert / Update / Delete.
You can also do the same just by using the standard cache, but AppFabric has some additional benefits.
a source to share
Use SQL Profiler to identify your slowest queries and see if you can improve them with some simple index changes (drop unused indexes, add missing indexes).
You could very easily improve the performance of your application by an order of magnitude without changing your external application.
a source to share
If you are only viewing data, you can store it in a Cache object. This will avoid getting into the database. Only data that can be used globally should be stored in the cache. If this data needs filtering, you can restore it from the cache and filter the data before rendering.
A session can be used to store user data. However, care should be taken to ensure that too many session variables can easily cause performance problems.
a source to share
This book may be helpful.
http://www.amazon.com/Ultra-Fast-ASP-NET-Build-Ultra-Scalable-Server/dp/1430223839
a source to share