Wednesday, October 14, 2015

SQL – Stored Procedure Vs Ad-Hoc (In-Line)

This would be a valuable decision making between the stored procedures and doing SQL (Ad-Hoc (In-Line) SQL) inside the coding before developing the back end of a database application. 

At that time, you need to decide the best approach to accomplish your tasks (insert, update, delete, view and other SQL statements) by using stored procedures or Ad-Hoc (In-Line) SQL. As performance wise both stored procedure or Ad-Hoc (In-Line) SQL, both stored in cache and hardly any difference in new SQL versions. Before make the right decision, we can see the advantages and disadvantages of stored procedures as shown below:

Advantages of Stored procedures:
  1. They are more easily available for code reviewed. After creating once, you can call them n-number of times from the various sources and no need to write you SQL query again and again. This is the biggest relief for a developer.
  2. Stored procedures are stored in a pre-complied form and stores in our database. That is once a Stored procedure is executed, the compiled code is used in subsequent calls whereas Ad-Hoc (In-Line) SQL codes are compiled each and every time whenever you called in application and they stay in your application code. You can tested your stored procedures easily due to less coupled.
  3. By using Stored procedures we can separate all the queries from the Business logic code and more easily tuned them whenever we want them. Stored procedures prevents SQL Injection Errors and provide the security to our database.
  4. Performance is generally better, from the point of view of network traffic - if you have a cursor, or similar, then there aren't multiple trips to the database but this is not applicable for Ad-Hoc (In-Line) SQL.
  5. By using them, we can protect access to the data more easily, remove direct access to the tables, enforce security through the procs - this also allows you to find relatively quickly any code that updates a table.
  6. If there are other services involved (such as Reporting services), you may find it easier to store all of your logic in a stored procedure, rather than in code, and having to duplicate it.
  7. Developers and database designers can work concurrently using stored procedures. While a programmer writes business logic, another one can create stored procedures at the same time. This is not possible with Ad-Hoc (In-Line) SQL.
  8. One more thing is if we can wrong execute store procedure the transaction may rollback but Ad-Hoc (In-Line) SQL can't for many update statement.
  9. Stored procedures keep coding and debugging simpler by dividing the work up into layers.

Disadvantages of Stored procedures
  1. The real problem with stored procedures is that many programmers that use them are inclined to use procedural programming techniques like cursors, loops, recursion which defeats the purpose of a database engine optimized for set based queries.
  2. The only down side of stored procedures are maintenance and redeployment. It's a little annoying to make an update and redeploy it from development to the production platform.

Now, we will discuss them which is better based on the below points-
Performance : Use inline SQL for CRUD operations and there is no need to write a stored procedure if the select statement is based on a single table then Ad-Hoc (In-Line) SQL will perform this task in more better way but remember security should be part of your design phase. Stored Procedures, especially in MS SQL Server have a major performance benefit.  One of the reason to use Stored Procedure instead of inline SQL in the program is because of CPU consumption and even though the SQL itself is simple, each time you executed it inline, the SQL engine needs to compile it (check statistics, prepare plan, etc.) before it can be run. Once the inline SQL has been parameterized into a Stored Procedure, the plan will be prepared and thus, will not need to be compiled again when it is executed the hundreds/thousands of times / minute. Stored procedures support to use much less network traffic and again improve scalability.

Security: If you used a secured model of using Stored Procedures and only given access to execute stored procedures to the user then you’re adding a new layer of security which might (or might not) save you from that attack. It is resolved by using parameterized SQL which eliminate the SQL injection possibility. Allowing ad-hoc SQL implies that the calling user context (whether integrated or standard security model is used) has access to the tables themselves. Such a model puts all the security control in the hands of the client when it probably ought to live in multiple layers. (Obviously, not all applications need to be particularly secure.) Restricting a user to the stored procedure interfaces not only abstracts the data, it protects the tables from a malicious user.
Maintainability: You can modify a SQL statement without having to compile and redeploy the application. If the application does not use stored procedures exclusively, there will be SQL fragments in several places in the code and every single fragment must be inspected in detail and followed from its origin to the construction of a complete SQL statement and its handoff to the database - to verify that the application is safe from SQL injection. This is a great deal more work. By using stored procedures and views, you abstract the code from the underlying database structure, and enable yourself to make changes to optimize it.
With simple inline queries, dynamic SQL is very easy to create. If your applications have complex queries, then the lack of a visual SQL creation tool makes them very lumbering and error prone.

Development speed: You can modify a SQL statement without having to compile and redeploy the application. Developers and database designers can work concurrently using stored procedures. While a programmer writes business logic, another one can create stored procedures at the same time. This is not possible with Ad-Hoc (In-Line) SQL.

Conclusion
So it is always better to stick to Ad-Hoc (In-Line) SQL for small requirement and for a medium, secured and complex logic depend on stored procedures. Stored procedures have been limited to batch processes where the ability to process large volumes of data as quickly as possible is essential and they are aligned much more closely to the tenets of design-by-contract, test-driven development, verifiable inputs/outputs, and code readability. Stored procedures are cached and reused a lot more efficiently than dynamic SQL. Ad-hoc SQL provides a lot of run-time flexibility. Disallowing direct table access ensures that correctly written stored procedures can validate correct visibility to underlying data.


There is a very simple fundamental fact that most seem to neglect, T-SQL is not a programming language, it’s a relational language driven by an engine which is very dynamic and complex, that requires maintenance, optimization and monitoring in order to ensure scalability and performance. By taking the code out of the database, you are leaving DBA handicapped from controlling the data flow and ensuring uptime. And while this will work in a lot of environments this is not an enterprise approach, and designs such these require complete redesign.
Learn more on another feature of SQL as:
  1. SQL - Stored Procedure
  2. SQL - Create Stored Procedure
  3. SQL - Execute Stored Procedure
  4. SQL - Alter Stored Procedure
  5. SQL - Views
  6. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
  7. SQL - Merge Statement
  8. SQL - Functions
  9. SQL - Cursors

5 comments:

  1. Nice Article !

    Really this will help to people of Database Community.
    I have also prepared small note on this, Should we use stored procedure or Should we use inline query or ad-hoc query.

    http://www.dbrnd.com/2015/12/database-design-use-stored-procedure-do-not-use-inline-or-ad-hoc-sql/

    ReplyDelete
  2. The size of the project is not an excuse to skip on stored procedures.
    If you do and you change your inline SQL, you still have to redeploy your front end app. Deployment of a changed proc is nearly instant, especially if you use SQLCMD mode. Stored procedures from a deployment standpoint still outstrip inline SQL.

    ReplyDelete
  3. It's never be best practice to use in line queries.

    In addition above-mentioned , if you need to modify a SQL, inline queries case you need to deploy the certain project, but in SP, you just need to update the procedure by back, no need to site down.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. While I don't believe in "never" or "always" statements in general, I can say IF you go down the inline route you do so accepting the VERY real probability of untold future PAIN. The excuses for going inline almost always are for convenience sake or lack of knowledge (or experience).

    Let me give you an example:
    Today I have a simple application that has a paltry 1 million records in several tables. I quickly throw together some simple crud support via inline statements. All is well.
    Soon, my metrics logging has grown to 500 million records, or my related history records, or I'm realize I'm beating the db to death in N+1 queries or my relationship tables have grown dramatically meaning my naive inline joins are killing the app. My once sub-second operations are now taking minutes to run! The entire app is suffering.

    Stating that a con for stored procs is because "some developers use cursors" isn't valid. Cursors have their purpose, but ANY programmer in any approach can do things poorly, such a online query loops.

    With zero code changes on the app side (were I to have gone the proc route in the beginning) I change change the entire db model or even datake distribution to be a federated approach, use 3rd (or MORE) form normalization, etc. to return to sub-second response time for my multi-billion record problem.

    THAT SAID...
    JUST because you are using stored procs now doesn't mean your work is done. Some tiny cross reference tables are great for online. But IF they are truely small you will leverage 2nd level caching so the point is mute. Meanwhile you REALLY need to review what is being requested and when and how you can further optimize both memory footprint and in core joins (pseudo inline) for these resultsets.

    Point is: attempting to come up with a rule for the proc or no proc route is almost taboo :) Understand your data, understand your usage, understand your growth, continually monitor and predict and revise those predictions as your world evolves. When prototyping time is of the essence, procs generally make little sense so online away! But always heavily scrutinize inline repository access, track it, segregate it and prepare to convert it but always coded in such a way as to make this transition seamless to the consumers.

    ReplyDelete