Shout!

Home

Practice: Put your queries outside of your code

Sunday, June 17, 2007

The sprocs vs ad-hoc queries war is still raging and I imagine it will for a while. I think that one of the main reasons that people do stick with sprocs is not due to the fact that they in some way represent the ultimate über performance, but rather the convenience of having the queries in a storage where it is easy to change and maintain it. Since most of the Microsoft community still isn't using some kind of generator to create the query for you (like an ORM for example) it is really a pain to try to maintain something like this:

string specialProductsQuery = "select * from products " + 
                              "inner join categories on products.categoryid = categories.categoryid " + 
                              "inner join categorygroup on categorygroup.id = categories.groupid " +
                              "where "

etc, etc, etc.

It is just so awful to maintain and create queries that are represented in quoted strings.

In the rare cases when I need to create theese kind of queries I don't resort to sprocs though(It is just a principle I follow, If I really don't need them I will not use them). Instead I add them to my project as .sql files containing the query. No quotation, no string concatenation and really easy to maintain.

Theese could then either be sent with the application if you want the flexibility to change the query without recompile. (Now I DON'T recommend that, but people do so anyway, to them I just want to say don't change anything in production for god sake!! But that is another battle to be fought another day.) This might be convenient in the development environment though to quickly change and try stuff. Another more sane approach is to compile the .sql file together with the application as resource files.

Using this practices, it would be possible to maintain the sql files without all the headache and still avoid stored procedures.

I've added a distillation of the practice I use in some projects to the "My Code" section over here http://www.lowendahl.net/shoutCode.aspx as an example project with unit tests and a test client.

It is capable of reading and caching (and flushing the cache) sql queries with two different strategies which are configured from the outside for easy configuration in different environments.

The idea for this manager is to make it easy to load and maintain queries. No more quoted concatenated strings in our code.Just something like this:

public static class Queries {
  public static string GetAllProductsQuery {
    get {
      QueryManager manager = QueryManager.CreateFromConfigFile();
      string query = manager.GetQueryFor("GetAllProducts");
      return query;
    }
  }
}

.
.
.

SqlDataAdapter adapter = new SqlDataAdapter(Queries.GetAllProductsQuery, ConnectionStrings.AdventureWorks);

The current version has the infrastructure to configure different cache strategies but it's not activated.

Comments

	6/18/2007 5:47:00 PM - Bob

"...and still avoid stored procedures." You say this like it's a good thing. I'm all for separating the query from the business layer -- it allows you to make changes to one or the other and, as long as you maintain a static interface, not affect the other. So, why this great interest in avoiding SPs? they serve the exact same purpose as what you advocate in your article without the need to build a "Query Manager". I won't argue over performance issues with SPs; I agree with you that the encapsulation aspect is a huge benefit but one thing that you fail to observe is that an added benefit of SPs is that they provide a centralized, unified view to the data. Anyone can get the same answer from the same source all the time. When business rules change, they only have to change in one place -- the SP. With a file/loader pattern, while the file can be shared, unless the loader is a DLL or service, people working in other languages must rebuild it. Facing that, I suspect that they would rather code it themselves (I know I would) -- and now your reuse and encapsulation is lost. You seem intent on solving a problem that already has a simple solution and that's just bad practice: A waste of your time and hurdle for anyone who must maintain your code.

	6/18/2007 6:25:00 PM - Marcus

I personally avoid stored procedures like the plague. They limit your ability to switch databases too much... Plus, if you're dealing with certain databases (ahem MySQL), then your choices for procedures is quite limited. I've also seen too many instances where stored procedures are drastically overused. For example, I think they should only be used where there is a bit of business logic that must be the same across all methods of accessing the database (primary key generation for example). Anything else should be done in the application. I personally really like the query manager idea.

	6/18/2007 7:11:00 PM http://www.entityspaces.net/ - Mike Griffin

Here's another way, I too avoid procs, but we can let use use procs and still let you do multi-db projects http://www.entityspaces.net/portal/Documentation/QueryAPISamples/tabid/80/Default.aspx

	6/19/2007 1:14:00 AM http://rant.blackapache.net/ - OJ

In avoiding stored procedures you avoid the extra level of security that you gain. If you grant people rights to stored procedures, then not only do you give them a unified view of the data (as Bob has already mentioned), but you also give them a known set of ways to update and delete data. Then at least you can add a better level of control over what is modified by the caller. If you don't, you have to grant them access to the tables directly, which basically gives the caller a free reign.

	6/19/2007 4:58:00 AM http://shitmores.blogspot.com - SeymourCakes

I say, go use iBatis.

	6/19/2007 10:27:00 AM http://www.matshelander.com/wordpress - Mats Helander

re: unified view. The same could be said about the rest of the application. Whatever business logic that remains in code, represents a potentially reusable view on the data. Is putting that code in sprocs the only reasonable way of making that code reusable? I certainly wouldn´t say so. Moreover, it is tempting to consider any sql in your app as something reusable which should go in the db, but reuse does come with dangers, already outlined above in the argument "When business rules change, they only have to change in one place -- the SP". What if doing so breaks an old client expecting the old behavior? We all know that manic cut-n-paste isn´t really the Answer. But the real answer is to use a multitude of tricks and techniques (such as versioning, template method pattern, etc etc) which are well supported by the modern programming languages but not equally much so by sprocs (well, unless you write them in C#, but then the point becomes kinda moot). This indicates, imho, that the more weight you give the reusability/unified view argument, the more you in fact argue for /not/ using sprocs. /Mats

	6/19/2007 3:32:00 PM http://xcskiwinn.org/community/blogs/panmanphil - Philip Nelson

The idea is a good one. SnapDAL's first goal was what you have sketched out here, simply moving sql out of your code and referring to it by name. With caching and some other tweaks to speed up command creation, you can get just as fast as hand coded commands. At that point, there are few advantages to using stored procs. PS, the security advantage mentioned earlier only helps a certain type of app that actually puts users in the database. I've never been in that situation, having built web based apps for most of my career. If you had real users, I suppose you could use Windows groups to accomplish the same thing with sql stored in files.

	6/19/2007 6:01:00 PM http://www.matshelander.com/wordpress - Mats Helander

Hmm, that came off as a bit strange - use modern languages to make sql reusable! Well, I rather meant use modern languages to encapsulate the sql. For example: I create a .Net component that uses Patrik's method to send sql to the db. Then I create a new version of the component with a new version of the sql. I can now use .Net versioning to select component and thereby version of the sql. If I had put both versions of the sql in the db, as sprocs, I would have had to resort to calling them things like my_sproc_v1 and my_sproc_v2. With a couple hundred sprocs in different versions things become tricky fast. Should sproc_A_v3 call sproc_B_v2 or sproc_B_v1? Who knows? Or should sproc_X_v3 always call sproc_Y_v3, so that when we create a v4 of some sproc we have to create a v4 of every other sproc (by copy-pasting)? All the types of questions that arise when your 'versioning' consists of adding version suffixes to your resource names (as the case is for sprocs and COM components for example) Template method pattern could certainly be used in conjunction with this (subclasses supplying different sql) and if we widen the scope to include dynamically generated sql then there's really lots of room for using modern languages to make your sql (generation) more reusable. Hope this made more sense :-)

	6/19/2007 8:54:00 PM http://www.lowendahl.net - Patrik Löwendahl

Bob and OJ, I've elaborated a bit on my reluctance to use sprocs here: http://www.lowendahl.net/showShout.aspx?id=142 Phil, actually my solution is inspired from the talk you and I had in Lillehammer 2005. I guess you where talking about SnapDAL then :) Mike, Seymour, As I understand it, thoose products are about ORM not about handling queries right? Mats, spot on, I referred to you in my follow up.

Comment

Title:
Your name:
Your url:
Text:
Remember me
Please enter the text from the image: