Ad hoc queries vs stored procedures vs Dynamic SQL [closed]

Question

Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons?

Vikkivikky

Answer 1

Stored Procedures

• Pro: Good for short, simple queries (aka OLTP--i.e. add, update, delete, view records)
• Pro: Keeps database logic separate from business logic
• Pro: Easy to troubleshoot
• Pro: Easy to maintain
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Compiled in database
• Pro: Better security (users don't need direct table access)
• Pro: Excellent query plan caching (good for OLTP queries--benefits from plan reuse)
• Con: Excellent query plan caching (bad for OLAP queries--benefits from unique plans)
• Con: Makes you tied to that SQL vendor

Dynamic SQL (i.e. uses exec command within a stored procedure)

• Pro: Good for short, simple queries (aka OLTP)
• Pro: Keeps database logic separate from business logic
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Allows any table, database, or column to be referenced
• Pro: Allows predicates (in WHERE clause) to be added/removed based on parameters
• Pro: Good query plan caching (mediocre-to-good for both OLTP and OLAP queries)
• Con: Only the static elements of the proc can be compiled
• Con: Makes you tied to that SQL vendor
• Con: More difficult to troubleshoot
• Con: More vulnerable to SQL injection attacks

Ad Hoc SQL (i.e. created in your business code)

• Pro: Good for long, complex quieres (aka OLAP--i.e. reporting or analysis)
• Pro: Flexible data access
• Pro: ORM usage is possible; can be compiled/tested in code (i.e. Linq-to-Sql or SqlAlchemy)
• Pro: Poor query plan caching (good for OLAP queries--benefits from unique plans)
• Con: Poor query plan caching (bad for OLTP queries--benefits from plan reuse)
• Con: More bits transferred over network (i.e. the whole query and params)
• Con: More difficult to maintain, if you don't use an ORM
• Con: More difficult to troubleshoot, if you don't use an ORM
• Con: More vulnerable to SQL injection attacks

Note: Always parameterize your ad hoc SQL.

For OLAP ad hoc SQL: only parameterize string data. This satisfies two conditions. It prevents SQL injection attack. And it makes the queries look more unique to the database. Yes, you'll get a poor query plan cache hit ratio. But that's desirable for OLAP queries. They benefit from unique plan generation, since their datasets and most efficient plans vary greatly among given parameters.

Illiterate

+1 Also I would add to the list effect on Network traffic, plan caching. – Surfboard

@Martin -- I just added network and plan caching info. Thanks for the suggestion. – Illiterate

Also another big plus for stored proc: your users don't need direct table access! Big plus in terms of security (and safety) – Execrative

I'm pretty sure you meant "More bits transferred over network" to be a Con for Ad Hoc SQL. (Although, with OLAP you'd expect to be dealing with larger data sets where the size of data transmitted TO the server is insignificant.) – Trader

@marc_s: Implementing all that security on the DB side is nice, but it also just ties you even more tightly to the DB vendor. If you have a separate data layer you can implement your security there, and then set up the DB so it will only take writes from the data layer's user. You might find some arguments for why doing it on the DB is safer, but I'm not sure it's worth the sacrifices you make. – Trader

@Nate C-K: if you do your security only in the application middle-tier, I guarantee, someone will find a way around this and then that intruder has an "open" unrestricted database to mess around with. I wouldn't want to be responsible for such a data disaster..... – Execrative

@marc_s: You seem to imply that using stored procedures is the only viable scheme in existence for securing your database. I disagree, although there are probably some DB vendors out there who would like to promote this view. I never proposed doing security ONLY in the middle tier. I mentioned making the app's data layer user the only one allowed to make updates. I'd also want the database firewalled in such a way that it's not reachable at all from the outside world, only from the data tier server. – Trader

@Nate good catch on the "bits transferred" point. I changed it to a con. – Illiterate

@Execrative thanks. Added security pro point. – Illiterate

You say complex queries in datawarehouses/olap, but sometimes because of business and businessman you have to put complex queries in OLTP and generally its better not to use procs for that. Most of the time, because of param sniffing. Stored procs are not even close to good to mantain and troubleshoot. First because you cannot unit test it by mocking in memory, you are tied to database, Second because you lack state of the tools for refactoring, like IDES, compilers, visual studio, etc. And troubleshooting, oh boy, thats pain! – Theaterintheround

Besides, if the query text is the same, sql will in fact reuse same plan. So this compiled pro point also partially suits dynamic and ad hoc queries generated by tools that standardize output queries. The overhead will be parsing and validation, that is small compared to plan generation – Theaterintheround

Also, you cannot easily scale database server horizontally. So processing should not be in db. Should be in app server, cause you can easily throw another machine and increase processing power. Im talking about this because people who likes procedure tends to like put them to process data in db servers. Ok, thats generalization, but its true for my experience – Theaterintheround

Also, parsing and validation will also ocurr to the exec proc query text – Theaterintheround

I dont understand what you mean by database logic. What is this? – Theaterintheround

Answer 2

Stored procedures PROs:

• Compiled. This means that it's faster to run and has positive impact on your database server's CPU due to bypassing optimization/compilation stage for all but first execution.
• Allow clean permissioning control over complex read and write queries.
• Provide for reusable API allowing one GOOD efficient implementation, instead of a bunch of Yahoos on a variety of platforms from a variety of apps re-implementing the samke queries and risking getting inefficient implementations
• Like any API, provide abstraction layer. You can change underlying implementation (schema) without changing any code calling the SP. That's an extremely big plus when there's 100s of apps across all platforms which use the query.

Stored procedures CONs:

• Hard to code flexible logic compared with dynamic SQL
• Having pre-compiled version can lead to less efficient execution as your data drifts and optimizer choices change. This is easy to ameliorate by re-compiling once in a while.

Emelinaemeline

Answer 3

Stored procedures

• Pro: Permissioning of actions without needing to grant more fundamental rights at the table level.
• Pro: Discrete and versionable
• Pro: Allows you to isolate your schema from your data access code.
• Con: Can be tedious to code CRUD procedures
• Con: Need to be kept in line with underlying schema

Ad hoc and dynamic - see Bill Paetzke's answers and comments.

Also, don't forget patterns such as bulk insert for SQL which isn't in your list but should still be considered.

Inactivate

Answer 4

RDBMS? This answer is specific to older oracle

In older oracle version < 11, dynamic sql does not reuse existing SGA sqltext plans, it creates a new entry for every execution plan the parser needs. With a lot a dynamic sql calls the sqltext area gets flushed fast enough that query reuse goes way down and peformance follows it on down.

Fatwitted

Answer 5

One additional advantage is easier "No downtime upgrades" (for major upgrades you still might incure some downtime).

if all data access is done through stored procedures, you can easily have v1 and v2 of the stored procedures sit side by side.

now you can have binaries/application logic from v1 and v2 running side by side, each calling their own version of stored procedures.

no down time is achieved through 1, locking down v1 app into readonly mode (if applicable), 2, deploying db changes. 3, reenabling normal access to v1 app, 4, deploying v2 app side by side, tell new users to use new binaries. 6. shut down old binaries when no more users are using the old binaries.

Kreiker

Answer 6

IMHO stored procedures should be avoided like the plague. Here's ten good reasons off the top of my head for why you should never use them (applies to all databases):

1. The PL/SQL language is geared towards handling data in tables, rows and columns. It is a poor choice for expressing business logic. You can code anything in any language - that doesn't mean you should
2. Most databases lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
3. Human resources are harder to find to write and maintain stored procedures - they are simply much rarer and therefore more expensive
4. Stored procedures are not portable between databases, because a) there is no industry standard for PL/SQL b) even if there was a standard, you usually end up using database-specific functionality/sql inside your stored procs. If you ever have to move dbs, you're looking at a full re-write of your business logic
5. Most databases don't offer any support for stored procedure debugging - you're left to insert rows into a log table or similar to achieve logging for debugging - very ugly
6. To test the stored procedure you need a real database instance. This makes unit testing stored procs difficult (you have to deploy them to a dev db to run them)
7. To deploy stored procs, you have to update the database (drop then create the stored procedure). If a bug is discovered, you can not simply roll back to a previous binary release like you can with app code. Instead, you must find the old code, drop the new stored proc and (re)create the old one. This is a change on top of a change, not a roll back
8. You are increasing the database server's processing demands, rather than distributing the business logic to other (app) servers. Since the database is usually a singleton, this is very bad, because the only way to increase capacity is to buy better hardware (not buy more hardware or use the cloud)
9. They are not that much faster than well written queries using prepared statements, because there's a trade off between increasing processing demand on the database server and the efficiencies of using them. Besides speed isn't everything (as long as it's acceptable): Maintainability, debuggability, suitability of PL/SQL, etc are just as important if not more so
10. Stored proc languages have limited (if any) libraries to draw upon, so you end up writing lots of low-value code. This is unlike app languages, which have loads of libraries you can use to all the things that business logic needs

There is only one place I would sanction their use: For very specific database functionality - perhaps a key check or a data type conversion or something similar, maybe within a trigger, that is so important it justifies its existence and that probably won't ever change once written.

In general, you should run screaming from stored procedures!

Farmland

Unbelievably incorrect!!! – Keratitis

@Keratitis I have been there - I have built a couple of production servers based on stored procedures. Experience has taught me that this is absolutely correct. Stored procedures are total crap for all these reasons and more! Which points exactly do you disagree with? – Farmland

I believe most of what you state here could be argued with but 3 that won't that much time to dispute are: (2) MS SQL Server Management Studio offers an IDE that is more than helpful IMO. (3) Here in Britain good database developers are not hard to find. (7) You do not have to drop and create the database to deploy a stored procedure. Stored Procedure can be help in source control just like app code can. If you find a bug you can rollback to a previous version very simply. If you are using AD Hoc SQL you have to reploy the whole application to fix a SQL bug rather than one stored procedure – Keratitis

I guess as its all a matter of personal preference but some of the arguements you have put across here are factually incorrect. – Keratitis

I didn't say you have to drop and recreate the database to deploy a stored procedure, I meant you have to drop and recreate the stored procedure itself. Anyway, I migrated from stored procs to pure app code and have never looked back. Maybe one day you'll see the light as I did. – Farmland

You have edited it now. It did say "you have to update the database (drop/create)" but even so you do not have to drop and recreate anything you can simply ALTER. Never say never but I find it more efficient to release a stored procedure that I do a an application. Especically when the need to redeploy a forms app to 1600 clients in 160 different pysical locations is involved just to fix a small bug in a bit of SQL code. – Keratitis

@Keratitis the old text meant "drop/create the procedure", not the whole db (obviously). Anyway... alter is logically the same an atomic drop/create. The point is it's a database change. I agree that releasing is "easier" (hackier), because you can release a tiny chunk of code at a time, but a) have you tested that chunk in the context of the versions of all other code currently released (no), and b) if you want small component size, there are technologies such as OSGi for that. Re updating many locations - a single app server can serve them just like a db can – Farmland

I would like to expand on point 9. Aren't ad-hoc/inline queries cached the same way as stored procedures? The only possible performance impeder would be network traffic, of which is usually via LAN so in decent data centre's network traffic isn't really an argument. – Variable

@Variable no. All queries ultimately are prepared, but for ad hoc ones the prepared statement (PS) exists only in the db engine and is thrown away after the call. Some connection pool libraries do cache PSs for you, but it's still a client side responsibility, because you have to keep the connection open to cache a PS. – Farmland

You forgot to mention parameter sniffing problems, that is the most common issue for stored procs, very hard to tune, most of the time there is simply no silver bullet, the problem with the same plan, is that you always have the same plan. Sometimes the resultset can vary a lot based on the value of the parameter. Dynamic queries simply dont have these issues – Theaterintheround

I think your 9th point just perfectly summarizes your "lack of knowledge" (to put it nicely) – Gervais

@Vaibhav_M Having coded many megabytes worth of queries and stored procs myself, I assure you that point 9 is true, especially under load of many concurrent callers. Databases primarily handle data efficiently, but when servicing high concurrent demand for executing SP's they are required to behave more like an app server, which they were not designed to be. In my experience, well written queries coupled with well designed application code out-performs SP's by many orders of magnitude (I've seen 1 million times faster), largely due to points 1, 8 and 10. – Farmland