Vivek Thakur

Chaotically Complex
  Home  |   Contact  |   Syndication    |   Login
  102 Posts | 1 Stories | 317 Comments | 66 Trackbacks

News



Archives

ASP.NET Ventures

Inline SQL queries or Stored Procedures: Yet-another-(healthy) Debate

Which is better: SQL queries in code or SPs? There is ofcourse no general answer but there are scenarios when one appraoch can be better than the other, or when a mixed approach can be employed.

Let's say we have a an app which does standard CRUD operations and has no data intensive methods. In this case I would recommend using SQL queries because:

1. The code would be more maintainable, each developer can write/edit his/her own module related CRUD methods independently.

2. No source control related issues. If SPs are used, then the DB script would need to be locked and updated for each build. Also, script related errors can also be avoided (which can prevent developers from running successfully executing their "clean" code against the DB).

3. There can be network related issues with developers needing to access a central DB for their development work and updating the DDL scripts. Also, it can be a bit difficult to work from home in such scenarios.

4. Database independence is lost in some sense. Inline queries are more or less standard across all DBs, but SPs vary a lot syntactically.

So using SPs blindly thinking that they always offer perfomance benefits is not wise.

I personally advocate a mixed approach: using inline SQL for CRUD and SPs for data intensive batch operations. So for a DB intensive app, I might use SQL queries for CRUD operations and SPs for data crunching or long processing tasks. This will have the best of both worlds.

Note that the SQL queries I am talking about are not huge concatenated string version of an SP (which could be huge maintenance problem!).

posted on Wednesday, November 08, 2006 12:00 AM

Feedback

# re: Inline SQL queries or Stored Procedures: Yet-another-(healthy) Debate 11/8/2006 5:20 AM Francois
Aye, to continue our discussion from the forums:

In my opinion, the most effective approach, with the best maintenance::performance ratio is the following:

Use an O/R Mapper. Use that for 95% of your queries. Have the O/R mapper as part of a 2 tier Data layer architecture, wrapped in a Service layer.

For the remaining 5%: Use stored procedures. However, do so separately from the normal DAL. The stored procedures should be treated as "external datasource", and be part of the "service data layer". Where your web services, EDIs, emails, excel sheets, etc are transfered to your lightweights DTOs to your service layer (the same service layer that reads the normal ORM DAL).

That way you really get the best of both worlds, and its -very- easy to manage.

# re: Inline SQL queries or Stored Procedures: Yet-another-(healthy) Debate 11/8/2006 9:27 AM Bruce Dunwiddie
Everyone that I've ran into on the job that wastes the breath in saying that sometimes inline sql is ok, are the ones that NEVER use sp's because their situation is ALWAYS the exception. Quit making random excuses for using inline sql and try to come up with solutions for how to make sp's work out better in your problematic situations. You're only viewing things from the perspective of a developer and the issues that sp's cause you. Do me a favor sometime and kick on sql profiler on the database and try to figure out what programs are calling what sql statements. Most programmers are not nice enough to dba's as to actually put identifiers in their connection strings so as to be able to tell programs apart. So when you're running top 10 worst performing queries reports on your database, it hardly does any good because even if you can see the query running, a lot of times you can't backtrack it to the developer in charge of fixing it. Make sp's the rule, and inline sql the unspoken evil.

# re: Inline SQL queries or Stored Procedures: Yet-another-(healthy) Debate 11/8/2006 10:06 AM Francois
Get used to it, with the massive adoption of Hibernate in the java world, and LINQ coming in the .NET world, it will soon be the norm (hell, except for legacy java apps, it already -is- the norm in the java world).

And I'm a DBA myself, by the way :)

That being said, finding which parts of the system is bugging down the server shouldn't be the DBA's primary task. Catching when there are issues, maybe, but checking which queries are slow should be done at the level of the integration testing.

And don't worrie, I don't think my situation is an exception, I think its quickly becoming the rule. The ability to have any change to a database ripple to the entire app and be caught by the compiler through changes in the scripted DTOs can save thousands of man hours of checking for damage mitigation in large ERP systems, for example. That outweights any other benifits :)

Right tool for the right job, and some situations definately work better with SPs. They are just becoming fewer as more tools are introduced to tap into dynamic SQL.

# re: Inline SQL queries or Stored Procedures: Yet-another-(healthy) Debate 11/8/2006 10:44 AM Vivek
Bruce,

I agree with Francois, finding out queries slowing down the server is not that big an issue here. We have optimized many apps having only inline queries with no trouble at all. The potential benefits outweigh such "drawbacks".

If there is any other strong reason to support the "only SPs" case, do let me know.

Vivek

Post Feedback

Title:
Name:
Email: (never displayed)
Url:
Comments: 
Please add 8 and 2 and type the answer here: