Your Thoughts On In-line SQL

  • Comments posted to this topic are about the item Your Thoughts On In-line SQL

  • I fall into the "stored procedures" group.
    At the moment we are busy with a project to replace all in-line SQL with stored procedures as well as implementing some SQL-injection prevention code.
    The slow DBA-scenario is not really an excuse and rather points to some other organisational problem that should have been fixed earlier!

  • I agree with you 100%. I've been on the front line too many times having to debug an error in production. If you can see the  SQL statements in a stored proc then half the battle is won.

  • Inline sql basically turns the entire database into a massive number of global variables. If that sounds like a good idea to your developers, you need better developers....

  • I'm totally for the use of stored procedures as a general rule. However, I can think of the odd argument for the use of inline sql. Sometimes it's easier as a developer to generate sql for particularly complex reports within a reporting application. For example if you have to get a large number of different values based on a large number of different parameters it may be more practical to generate inline sql than 1 or more monstrous procedures that take lots of arguments and maybe end up looking a bit procedural anyway. This might especially be the case if you only want to return a small amount of aggregated data based on complex requirements rather than return a large volume of data that you then aggregate in the reporting client.

  • I'm another advocate of the "Stored Procedures" only group, however, pragmatism has a place and there may be specific instances where in-line SQL is the answer, however, those instances should be well documented and only used in extremely rare situations. I agree with Andy regarding complex reports, however, I would still want to check if it would still be better performed in a stored procedure first.

  • For simple CRUD operations I would imagine most developers these days would favour an ORM framework over inline SQL or stored procedures, then use SP's for more complex queries/reports (from what I have seen over the past few years anyway).

  • There are definitely occasions where stored procedures really don't cut it because the logic involved in the query is much better handled in an environment that can deal with the logic better. These are fairly rare and it's usually where the query needs to be altered depending on the inputs provided. While it is possible to have multiple stored procedures and for the application to choose which one to use the number of possible permutations can make this a poor option as can query efficiency options. In any case when I come across a dynamically created query and it is not executed as a paramaterised query I will go postal on the developer - there is no excuse for allowing a route for SQL injection attacks into a system when the fix was developed a couple of decades ago.

    An advantage of application defined queries is that all of the application logic is within the same environment and the same version control system. This can be particularly important because version controlling (and just identifying the version of a stored procedure or function) is thoroughly painful in MS-SQL. Expanding version control systems and application deployment systems to include other environments is an important component of the application process but in general is woefully behind requirements without manual intervention added.

    On the other hand, as soon as these stored procedures (and udfs) are considered an API then there are very strong arguments to keeping such logic closer to the database where changes to the database structure can be abstracted away from the API to some extent and the efficiency of the stored procedures and functions can be dealt with closer to the source.

    I'd tend towards the stored procedure and function route where possible even though the application deployment and version control is considerably harder. Like much of life, and development, there is no 100% rule though.

  • I wouldn't advocate in-line SQL, but as funbi says, that's not the real alternative these days. We're using EF and this has many advantages. 

    Essentially it's a simple engineering trade off. We're often writing simple view / filter / edit pages for entities that will not ever number over 100. We need the pages, they don't get used more than once a week / month / whatever and would only be used by one admin at a time say. You don't need to craft procs for that. Or for filling drop downs, checking a flag, or many other simple application tasks we do all the time.

    When a page is used all the time we have a simple decision tree that helps us to decide whether to use EF / paged EF / a simple stored proc / a paged stored proc as appropriate. Yes, if you let an ORM just do what it wants and don't think about it you will get horrible, horrible code and a very slow site (or whatever). With a bit of thought and craft, you can get a really well developed application. Oh and SQL injection is covered, change control is easier than with procs - change a field and the code won't compile, with an error wherever it's used.

    But no - don't use in-line SQL 🙂

  • +1 "Stored Procedures Only".
    I am told, but have not done it yet, that parameterized queries may be an acceptable substitute for "Stored Procedures Only".

    Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

  • In an OLTP setting I would prefer stored procs.
    In a data science/BI scenario it is a much more free-flowing environment so much wider ranging access is required.  I have found it handy to have an explicit /resources/sql/ folder in an Apache Spark program precisely to make the SQL highly visible and also to aid maintainability.

    I think the key issue is the visibility of the cause of the problems.  If you are a DBA you don't want a database performance issue hidden in application code, if you are a developer you don't want an application performance issue hidden in the DB.
    If either party has a "Mine not yours" attitude and enforcement then both deserve the hell they will reap.  I don't want DBAs blocked from seeing application code or developers blocked from seeing DB structure, stored proc code etc.  I would expect both groups to be pro-active in educating each other as to the best practises and anti-patterns in their respective areas.

    If a bug or performance issue occurs I expect either DBA or developers to enter into dialogue, fix the problem promptly, agree any changes required in any SDLC or automated test regime that would prevent the problem occurring again.  In such an environment ORMs become less of an issue because you all work together to allow them to work.

    My beef with ORMs is that they are seen as a quick fix.  The letter "M" stands for Mapping.  If you work out the relational to object mapping correctly they work really well.  If you just install the wretched thing and expect miracles then you will get horrors.

  • I'm a dev, not a DBA, and we're using Dapper.
    https://github.com/StackExchange/Dapper

  • I prefer stored procedures in applications. In ad-hoc situations, queries will do until the script starts evolving into an application.

    ORMs are a special case. I'd prefer not to give up stored procedures just to make the ORM or project manager happy.

    SQLite has no stored procedures, so you can either use a ORM, database access library or carefully structured program that doesn't sprinkle SQL statements throughout the code like bovine ordure in a pasture.

    Once built an app in an archaic language to access an archaic database through ODBC that kept the SQL statements in a table for easy of auditing. Actually made the application manageable.

  • n.ryan - Friday, March 16, 2018 3:32 AM

    There are definitely occasions where stored procedures really don't cut it because the logic involved in the query is much better handled in an environment that can deal with the logic better. These are fairly rare and it's usually where the query needs to be altered depending on the inputs provided. While it is possible to have multiple stored procedures and for the application to choose which one to use the number of possible permutations can make this a poor option as can query efficiency options. In any case when I come across a dynamically created query and it is not executed as a paramaterised query I will go postal on the developer - there is no excuse for allowing a route for SQL injection attacks into a system when the fix was developed a couple of decades ago.

    An advantage of application defined queries is that all of the application logic is within the same environment and the same version control system. This can be particularly important because version controlling (and just identifying the version of a stored procedure or function) is thoroughly painful in MS-SQL. Expanding version control systems and application deployment systems to include other environments is an important component of the application process but in general is woefully behind requirements without manual intervention added.

    On the other hand, as soon as these stored procedures (and udfs) are considered an API then there are very strong arguments to keeping such logic closer to the database where changes to the database structure can be abstracted away from the API to some extent and the efficiency of the stored procedures and functions can be dealt with closer to the source.

    I'd tend towards the stored procedure and function route where possible even though the application deployment and version control is considerably harder. Like much of life, and development, there is no 100% rule though.

    Not sure what you are using for your development environment, but at my company we are using visual studio.  We have encouraged all our developers to use sql database projects, so all the functions and stored procs are part of the solution and then everything is under source control.  It is sort of funny because a lot of people have never even heard of a sql database project in visual studio.  I wrote a little app that will grab all the scripts and put then into one script so they can easily all be executed at once.  Anyway, that is how we handle having everything together in one place instead of in-line sql.

    Ben

  • chrisn-585491 - Friday, March 16, 2018 6:19 AM

    ORMs are a special case. I'd prefer not to give up stored procedures just to make the ORM or project manager happy. 

    ORMs do not necessarily preclude the use of stored procedures. I'm using stored procedures (for CUD) and views exclusively in my Entity Framework projects. Works just fine so far, and gives me better control over things. I'm not sure if it'll work for everything I do, but so far it does.

Viewing 15 posts - 1 through 15 (of 67 total)

You must be logged in to reply to this topic. Login to reply