'When is it better to write "ad hoc sql" vs stored procedures [duplicate]

I have 100% ad hoc sql through out my application. A buddy of mine recommended that I convert to stored procedures for the extra performance and security. This brought up a question in my mind, besides speed and security is there any other reason to stick with ad hoc sql queries?

c++sharpsqlsql-server-2008


Solution 1:[1]

There's nothing about stored procedures that makes them magically speedier or more secure. There are cases where a well-designed stored proc can be faster for certain types of tasks, but the reverse is also true for ad hoc SQL.

Code the way you find most productive.

"Make it right before you make it faster." -- Brian Kernighan

Solution 2:[2]

If you are not writing stored procedures, investigate parameterized queries. If you build the SQL yourself including parameter concatenation, you're inviting a SQL injection attack.

Solution 3:[3]

There are a couple myths related to this topic that you should disabuse yourself of:

Myth 1: Stored procedures are pre-compiled
http://scarydba.wordpress.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/

Myth 2: Ad Hoc SQL queries do not reuse execution plans: http://scarydba.wordpress.com/2009/10/05/ad-hoc-queries-dont-reuse-execution-plans-myth-or-fact/

IMHO procs have the edge when you absolutely need to lock down the database. In these situations, you can use an account that only has rights to execute stored procedures. Additionally, they can provide a layer of abstraction between your app and the database from the DBA perspective.

Likewise, dynamic SQL is better in situations where the query may need to change some and be... well... dynamic. Or if you know you have to port to multiple databases.

Both are just as safe in regards to SQL injection as long as all user inputted values are parameterized.

Solution 4:[4]

A lot of things have been said about performance, caching, and security in this thread already, and I won't repeat those points. There are a few things that I haven't read yet in this thread, which is portability issues and roundtrips.

  • If you are interested in maximum portability of your application across programming languages, then stored procedures is a good idea: the more program logic you store in the database outside the app, the less you have to recode if you're moving to another framework or language. In addition, the code to call a stored procedure is much smaller than the actual raw SQL itself, so the database interface in your application code will have a smaller footprint.
  • If you need the same logic in multiple applications, then stored procedures are a convenient to have a single definition of that logic that may be re-used by other applications. However, this benefit is often exaggerated as you could also isolate that logic in a library that you share across applications. Of course, if the applications are in different languages, then there is true benefit in stored procedures, as it is probably easier to call a procedure through your language's DB interface than to link to a library written in another language.
  • If you are interested in RDBMS portability, then stored procedures are likely to become one of your biggest problems. Core features of all major and minor RDBMSs are quite similar. The largest differences can be found in the syntax and available built-in functionality for stored procedures.

Regarding roundtrips:

  • If you have many multi-statement transactions, or in general, functions in your application that require multiple SQL statements, then performance can improve if you put those multiple statements inside a stored procedure. The reason is that calling the stored procedure (and possibly returning multiple results from it) is just a single roundtrip. With raw SQL, you have (at least) one roundtrip per SQL statement.

Solution 5:[5]

Portability, when you need to switch to a new SQL server, and don't have access to the old one.

I've worked on a few projects where the original developer wouldn't give access to the server, after he was fired, so we had to re-write many queries ourselves because they were locked up in stored procedures that we didn't have permission to view. If all the queries were in the source code, it would have made it a lot easier.

Solution 6:[6]

I have 100% ad hoc sql through out my application. A buddy of mine recommended that I convert to stored procedures for the extra performance and security.

I would not worry about performance until there are actual pain points. For example, someone is using your application and complains that it's slow. Until you reach that point, your time is best spend improving your application.

In security, you have to balance effort versus risk. If your site doesn't store anything of value, even SQL Injection is a perfectly acceptable risk, as proven by a great number of web sites out there :)

Solution 7:[7]

I can't see when Ad-Hoc queries would give any benefits. Discussing with a friend this same question we found the following thing in favour of stored procedures (apart from the obvious caching/SQL Injection issues):

1) Code readability: If you have some hairy SQL Code embedded in your application, it becomes much harder to read/understand. It is much simpler to have a good Naming Convention to Stored Procedures that says exactly what it does, instead of a lot of code. It is more less the same principles that we try to use when refactoring.

2) Ability to improve your application without to redeploy: If you need to tweak your logic because of bugs or poor performance, having the SQL Code embedded in your application implies that you need to redeploy it (even if your dataset doesn't change). If you have it in a stored procedure, the only thing you need to redeploy is the procedure. Also, it gives the changes to a DBA to do its magic improving the overall performance of the query by working with the procedure. This would be much harder to do if you're working with the embedded version.

3) Network Traffic: If you're passing a lot of SQL Code around you're increasing the size of your message (or RPC calls) being transmitted over the network, which can lead to poor performance due to requests. Specially if many users to the same calls everytime.

I hope this helps.

Cheers, Wagner.

Solution 8:[8]

My answer might be slightly off topic, but anyways:

It could be useful when working with views. Hibernate (that you are not using) for instance, has pretty bad support for views. When I need to query a view with it, I always use raw SQL, since that is the only thing that works.

Solution 9:[9]

One advantage of stored procedures is that data does not have to be transferred over the network for intermediate calculations. If a lot of calculation results in a single value, then the stored procedure is going to be faster.

Solution 10:[10]

Speaking from my experience, I would put an advantageous point for each method.

Point for Ad-Hoc SQL:

There is a case when Ad-Hoc SQL is less painful (initially), which involves large (and dynamic) range of search parameters. Say, for example, you have an Account Search, and a companion Advanced Account Search, which contains 3 times more parameters. Some of the fields are easy (Account #); others may be very painful (a substring search on Address Line 1!) And for the worst, most of the parameters are not required.

This makes performance tuning not trivial. In this case, having so many combinations of parameters, caching of execution plan (my experience is on MS SQL) will backfire. The execution plan for, say, providing Account # and Salesman # only, will potentially be very bad for another set of search, providing Address search and the brands they carry. Ad-Hoc SQL will have the side effect of recompiling based on different sets of parameters provided, thus getting around the execution plan caching problem.

Of course, the above can be done by stored procedure as well, but in order to get around the execution plan caching problem, you will have to launch the recompile command within the stored procedure. One can also argue that the dynamic SQL construction can be put within the stored procedure, but this is just moving the Ad-Hoc SQL into another place; still Ad-Hoc SQL.

Point for Stored Procedure:

One can consider Stored Procedures as API. If you have worked on enterprise environments, chances are there will be different applications doing exactly the same thing. For example, the event table may be inserted from different softwares, including Accounting, Sales, Auditing, Customer Relationship Management, etc. These programs may not be maintained by the same group of people (e.g. different subdivisions), but they will ultimately having access to the same underlying database.

In this case, it would be a source code management nightmare for using Ad-Hoc SQL, because this would result in multiple versions of Ad-Hoc SQL performing the same functionality, in which every version may have different side effects. I am currently dealing with this situation, and it is not fun. Stored Procedures in this case can be reused, thus having a central management of database codes.

Solution 11:[11]

Truthfully, SQL injection can be prevented by Parameterising your queries (look into ODBCParameters for instance) and your queries can be constructed such that these parameters cannot be SQL injected. For instance...

DECLARE @param varchar(50)
SELECT @param = ?
SELECT * FROM TABLE WHERE NAME = @param

is a safe method of doing internal queries with ODBC parameters. However, there are some advantages to using Stored Procedures:

  1. Complex SQL with multiple functions or cursors can screw up if used in an ad-hoc manner as some ODBC drivers don't know how to handle multiple query requests
  2. It separates the business logic from the database calls. This allows you (the application developer) to know nothing about the structure of the database and still develop your application while a dedicated DBA or SQL developer can fine tune the SQL.
  3. Stored procedures are pre-compiled, so over many repetitions they will be faster, but ONLY if they are called extremely frequently (i.e. a monitoring program)

When all is said and done, its a design decision. Don't take portability into account, you can just have the SQL dev give you the scripts to apply the store procs and run them on installation of the program anyway :)

Hope this helps

Solution 12:[12]

Ad-hoc queries give you flexibility in your application logic, but you almost always pay a price on performance.

If you are concerned with performance, I'd probably agree with your friend that you should look into stored procs, or some more static form of query, to allow the database to "pre-optimize" the query, or allow the caching layer (if one exists) to potentially cache query results.

If you generate the query on the fly every time, the database will most likely not be able to help you at all with performance.

Solution 13:[13]

  1. You don't need to write a stored procedure
  2. Generally ad-hoc SQL is fast enough. You can use parametrized queries to increase speed.
  3. You can compile string based SQL to "compiled" SQL, and then execute that, which is much faster.
  4. Typically the performance bottleneck for SQL is the queries not any of the above.

Solution 14:[14]

It may depend on who else is using the db. If only one application uses the db, then parametrized queries have the advantage of sitting in the source.

If other applications use the db, then the dba should put common stored procedures in the db.

Solution 15:[15]

There is no "right" answer. It depends on each situation.

Did anyone mention this? Stored procedures typically return every field and it's unfeasible to create one for each variation of fields that you want. Ad-hoc lets you specify only those you want. However, if you are using any sort of entities (custom objects, EF, etc.) you'll probably be returning all fields anyway.

Solution 16:[16]

There probably won't be a performance benefit, but for maintainability you might want to look into using something like LINQ2SQL, so that you don't have syntax errors in your SQL.

Related Questions