1

What I mean is if I have two ways to write a SQL query:

  1. write it inside SQL as a stored procedure and execute it from C#

  2. build a query in C# then execute the query so it does not exist in my database

Which is better and why?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eslam Soliman
  • 1,276
  • 5
  • 16
  • 42
  • 1
    There is no "better" and there are arguments for (and against) both approaches - in any case (especially if written "in code"), *write it as part of a DAL* and not spread about randomly like a common [PHP] tutorial. I use a *combination* of L2S/LINQ queries, SPs/Triggers, Views, Typed DataSets [gasp!] and a few custom ADO.NET SQL queries. – user2864740 Jan 11 '14 at 08:13

1 Answers1

1

"Better" is opinion-based. But there are hard facts:

Stored procedures can be compiled by the database. That means you have syntax checking. Comma missing or field mistyped? In a stored procedure this will bring up a compile time error, in your program it will be a runtime error.

SQL code that is in the program can not be optimized beforehand. For the database, a statement from the program is random. It may be cached and maybe the database is intelligent enough to see that the statement never changes, but if it's inside a compiled stored procedure, it can be compiled and prepared. Once on compile. Not once per call.

If you change the database, your stored procedures will become invalid. Right there on the spot. It's obvious. If you have code in the program, it may generate a runtime error when the user clicks a button three weeks later.

Having it all in code is easier to handle for the developer.

And there are probably more pros and cons. You will have to weight them all and decide for yourself.

Edit:

This question is tagged and therefore the comments are appropriate. Please note that my answer above was programming language and DBMS agnostic. They describe what can be done. MS-SQL at the time of this post is a pretty good DBMS but there are others that existed for decades and are way more optimized than MS SQL. MS SQL may not support those features I'm talking about.

If you put your SQL in code, from the perspective of the DBMS it's a new query every time. It may use statement caching to optimize it for every call that is inside it's caching mechanism scope. However, it will never be able to tell that your are using the very same statement every time, instead it will need to parse the statement and explicitely check against all it's cached statements. If you put your statement inside the database, the DBMS has the chance to compile the statement and use a mechanism where it does not need to check any statement cache because it knows that this statement is the same. It's the difference between an interpreted language and a compiled language if you will.

nvoigt
  • 75,013
  • 26
  • 93
  • 142
  • 3
    Those are not so "hard" facts. SQL Server is quite capable of compiling and caching query plans (which it does - sometime to chagrin of stale plans!), thank you. Only schema-bound SPs (which are themselves a PITA to deal with) offer any sort of hardening vs a changing schema any more than queries in code. – user2864740 Jan 11 '14 at 08:44
  • Actually, this statement is plain wrong. From a "optimization" and performance point of view, a stored procedure has **NO** benefit over a properly parametrized ad-hoc query. Both will have an execution plan determined when they're first executed, both execution plans will be cached and reused if possible - no benefit for stored procedures here..... – marc_s Jan 11 '14 at 09:39
  • Just for the record: I said *"can be"*. Good database systems *will do so*. I cannot guarantee what Microsoft does. Fact is, having your SQL in C# code makes it impossible for the database to optimize at compile time, while having it in the database makes it possible. Microsoft might not make use of this possibility, *real* database systems do. It's not my fault that people use a sub-par DBMS. – nvoigt Jan 11 '14 at 10:54