0

could people please give their opinion on and give advantages and disadvantages on the use of sql queries directly within an application as opposed to creating procedures or functions within the database and using them.

1 Answer 1

3

Raw SQL

  • A good inline SQL query, if made correctly using parameters is as
    good as a stored procedure.

  • Raw SQL is more flexible to deal with.


Stored procedures

  • Stored procedures have the benefits of security , but just raw performance isn't one of their major plus points.

  • Stored procedures are more maintainable.

  • Stored procedures are precompiled

  • The security and data layer separation benefits and increased ease of performance tuning provided by strictly using stored procs generally make them a better solution than ad-hoc queries regardless of any relative performance enhancement that may or may not come with the pre-compilation of the procs.

  • scalability and modularity

Conclusion

I'd personally like to utilize only Stored Procs for database access.
The only thing I see SQL doing that stored procs can't do (easily) is dynamically choosing the sort order. Other than that, it's about knowing how to code the stored procs the way you need.

3
  • 1
    I would add that dynamically choosing the sort order could be handled in the presentation layer. Read all your data into a variable (DataTable or List<T> in C# is how i handle that) and then use the built in controls to sort the data. Unless there is a specific reason to return data in a specific order I find the presentation layer is faster at multiple sorts. Mar 7, 2014 at 21:33
  • @Wind Raven ,Good point.
    – Up_One
    Mar 7, 2014 at 21:34
  • Raw SQL / prepared statements in the application are written in your favorite programming language: that familiarity gives you increased speed of development and access to your normal debugging tools. Stored procedures are generally written in proprietary languages (PL/SQL, TSQL, etc.) so they are slower to develop and maintain (not always a bad thing, though). It can be difficult to track down performance problems because the SQL is "wrapped away" inside the procedure (Oracle Database is particularly bad for this).
    – Phill W.
    Oct 30, 2018 at 11:19

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