Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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?

share|improve this question
1  
Consider using repository pattern for your business logic -> msdn.microsoft.com/en-us/library/ff649690.aspx. You can also base your further queries on previous one, for example: GetAllUsers (you can cache this one) and GetAllNonActivatedUsers do operation on cached collection returned from GetAllUsers, same with GetUser. In this situation you have one query to get all users and just working on this data set. – evilek Mar 12 at 22:44
1  
I had the same problem, and asked almost the same question over here: programmers.stackexchange.com/questions/143248/… For what it's worth, we ended up implementing something where we can do half-SQL-queries like this: list = MyModel->query(" start_date > X AND end_date < Y AND pending = 1 AND customer_id = Z ") – Keith Palmer Mar 13 at 2:53
Thanks Keith, your question is exactly what was on my mind. – Rowan Freeman Mar 13 at 3:19
You should have a look at the specification pattern - en.wikipedia.org/wiki/Specification_pattern - you can compose different specifications for repeating requirements. – SpaceTrucker Mar 13 at 9:21

2 Answers

up vote 3 down vote accepted

Well, first of course you are going to have lots of queries because you expect the aplication 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 havea 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 wil 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 whcih ensures that all finaicial data is based on the same set of busines 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 procs. Organizing them by schema has helped. If your database doesn't have schemas, organizing by using a systematic naming convention heps. that way you know allthe things that relate to finance have the word finaince in them and those relating to users have user in them. That helps you to find the one you arelooking 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 unnecesary SQL! I also improved performance by cutting the time to excute 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.

share|improve this answer
Sincerely, this one is better than the accepted answer... +1 – Fabricio Araujo Mar 13 at 18:54
up vote 7 down vote

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.

share|improve this answer
4  
Even with an ORM, you still need the moral equivalent to queries (e.g. a bunch of LINQ operations), and they're not necessarily simpler. Better than embedding SQL in strings in the source code, but as the logic doesn't magically become non-trivial, it's still important where you put them. In that sense, you don't really answer the question. – delnan Mar 12 at 22:37
Yes, very true. The one advantage I guess is that it can be easily refactored. – Sam Mar 12 at 22:38
I should have mentioned that I'm using LINQ for all my queries, which is great as I really like LINQ, but it doesn't help cut down the number of queries that I use :\ I find the number of LINQ statements overwhelming (hard to manage, change). – Rowan Freeman Mar 12 at 22:43
1  
@RowanFreeman ensure that you are separating your concerns properly and that your data layer uses overloading where at all possible. – Sam Mar 12 at 22:45
I don't know about Linq, but another advantage of some ORMs is that they are typically more composable than writing plain SQL. You can write an ORM query such that common parts of the query is in one method and the specific parts of the query in another which calls the common method and adds its own specificities; doing the same with raw SQL is generally much more awkward. – Lie Ryan Mar 13 at 14:39
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.