I have an MVC3 project that uses SQL Server.
I use data from the SQL database all the time and I often find that I’m reusing/duplicating some SQL queries. I thought I’d solve this problem by creating a few static “helper” classes that just contain a bunch of static methods for retrieving common things.
GetAllUsers(database As MyEntity)
GetAllNonActivatedUsers(database As MyEntity)
GetUser(database As MyEntity, userId As Integer)
The problem is that this made things slightly worse. Now instead of having SQL queries all over my controller actions, there are large numbers of them in these helper classes. The names of these methods are becoming silly.
GetPendingUserApplicationByApplicationId(database as MyEntity,
applicationId As Integer, userId As Integer)
At this stage I’m thinking of scrapping the helper classes and going back to just random SQL queries throughout my controller actions.
Where have I gone wrong and how do people manage their SQL queries?
Is it ok to duplicate SQL queries?
4
Well, first of course, you are going to have lots of queries because you expect the application to do lots of things.
Databases have a couple of things that can help, but you can make things worse by using them badly.
ORMs are one tool that will help write the queries for you. But you will still have a lot of queries if you have a lot of database work that needs to be done.
Next, you can use Views to create some of the main things you will want to use over and over. Views are good for complex things that you will need to make sure have the same calculations in multiple situations. For instance, we have views that collect all the data from various table for financials which ensures that all financial data is based on the same set of business logic. Do not, however, use views to call views.
You can use stored procedures and then just call them in multiple places. (Make sure you put them in source control though, SPs are code!)
However, you may have to learn to live with the fact that there is lots of code. Our database has well over a thousand stored procedures. Organizing them by schema has helped. If your database doesn’t have schemas, organizing by using a systematic naming convention helps. That way, you know all the things that relate to finance have the word finance in them and those relating to users have user in them. That helps you find the one you are looking for when you need to reuse it.
Code reuse can be a wonderful thing, but there is a very strong caveat when querying databases. If the queries are slightly different, then they must be two separate queries rather than one that sends more data than the application needs at that point. Adding fields you don’t need to queries so you can use them elsewhere can cause horrible performance issues that are very hard to correct (I just spent several days trying to performance tune such a mess and ended up eliminating over 400 lines of unnecessary SQL! I also improved performance by cutting the time to execute more than 60%). So don’t go on that path just to have fewer queries. More specific queries is generally better than fewer general ones for database performance.
0
A lot of people these days (based purely on my professional exposure) are now using ORM tools for the benefits they provide.
Benefits include:
- Lazy loading
- Multiple database targets
- For some frameworks, use of LINQ to select records (i.e. Linq-to-SQL, Entity Framework)
- Not having to change multiple queries when you add a field
The primary negative as far as I am concerned is a steep learning curve however Linq-to-SQL is fairly simple.
Since you are using ASP.NET MVC, I’d suggest looking at the Entity Framework tutorial Microsoft provides on the ASP.NET portal.
Where have I gone wrong and how do people manage their SQL queries?
I’ve been using ORM frameworks for years which minimises the amount of SQL I need to use. For times when I have to write SQL (which is not often) I ensure I write standards compliant code that works on all database targets, I embed them in the assembly under a SQL folder and load the resource stream for execution. Personally, I don’t ever write SQL queries inline.
Is it ok to duplicate SQL queries?
I would say it is a code smell but that’s because I consider any duplication to be so.
6