Dynamic sql vs stored procedures - pros and cons?

Question

I have read many strong views (both for and against) SPs or DS.

I am writing a query engine in C++ (mySQL backend for now, though I may decide to go with a C++ ORM). I cant decide whether to write a SP, or to dynamically creat the SQL and send the query to the db engine.#

Any tips on how to decide?

Answer 1

You have more control over the mechanisms outside the database. The biggest win for taking care of this outside the database is simply maintenance (in my mind). It'd be slightly hard to version control the SP vs the code you generate outside the database. One more thing to keep track of.

While we're on the topic, it's similar to handling data/schema migrations. It's annoyingly complex to version/handle schema migrations, if you don't already have a mechanism for this, you will have yet another thing you'll need to manage. It comes down to simply being easier to manage/version these things outside the database.

Consider the scenario where you have a bug in your SP. Now it needs to be changed, but then you hop over to another developers database/sandbox. What version is the sandbox and the SP? Now you have to track multiple versions.

Answer 2

One of the main differentiators is whether you are writing the "one true front end" or whether the database is the central piece of your application.

If you are going to have multiple front ends stored procedures make a lot of sense because you reduce your maintenance overhead. If you are writing only one interface, stored procedures are a pain, because you lose a lot of flexibility in changing your data set as your front end needs change, plus you now have to do code maintenance, version control, etc. in two places. Databases are a real pain to keep in sync with code repositories.

Finally, if you are coding for multiple databases (Oracle and SQL compatible code, for example), I'd avoid stored procedures completely.

You may in certain rare circumstances, after profiling, determine that some limited stored procedures are useful to you. This situation comes up way less than people think it does.

Answer 3

The main scenarios when you MUST have the SP is:

1) When you have very complex set of queries with heavy compile overhead and data drift low enough that recompiling is not needed on a regular basis.

2) When the "Only True" logic for accessing the specific data set is VERY complicated, needs to be accessed from several different codebases on different platforms (so writing multiple APIs in code is much more expensive).

Any other scenario, it's debatable, and can be decided one way or another.

I must also say that the other posters' arguments about versioning are not really such a big deal in my experience - having your SPs in version control is as easy as creating a "sql/db_name" directory structure and having easy basic "database release" script which releases the SP code from the version control location to the database. Every company I worked for had some kind of setup like this, central one run by DBAs or departmental one run by developers.

Answer 4

The one thing you want to avoid is to have your business logic spread across multiple tiers of your application. Database DDL and DML are difficult enough to keep in sync with an application code base as it is.

My recommendation is to create a good relational schema, but all your constraints and triggers so that the data retains integrity even if somebody goes to the database and tries to do something through some command line SQL.

Put all your business logic in an application or service that calls (static/dynamic) SQL then wraps the business functionality you are are trying to expose.

Stored-procedures have two purposes that I can think of.

1. An aid to simplifying data access. The Stored Procedure does not have any business logic in it, it just knows about the structure of the data and exposes an interface to isolate accessing three tables and a view just to get a single piece of information.
2. Mapping the Domain Model to the Data Model, Stored Procedures can assist in making the Data Model look like a given Domain Model.

After the program has been completed and has been profiled there are often performance issues with the pre 1.0 release. Stored procedures do offer batching of SQL without traffic needing to go back and forth between the DBMS and the Application. That being said in rare and extreme cases due to performance a few business rules might need to be migrated to the Stored-Procedure side. Make sure to document any exceptions to the architectural philosophy in multiple prominent places.

Answer 5

Here's the simple answer:

If your programmers do both database and coding work, keep the SQL with the app. It's easier to maintain that way. Otherwise, let the DB guys handle it in SPs.

Answer 6

DS is more flexible. SP approach makes your system more manageable.

Answer 7

Stored Procedures are ideal for:

• Creating reusable abstractions over complex queries;
• Enforcing specific types of insertions/updates to tables (if you also deny permissions to the table);
• Performing privileged operations that the logged-in user wouldn't normally be allowed to do;
• Guaranteeing a consistent execution plan;
• Extending the capabilities of an ORM (batch updates, hierarchy queries, etc.)

Dynamic SQL is ideal for:

• Variable search arguments or output columns:
  • Optional search conditions
  • Pivot tables
  • IN clauses with user-specified values
• ORM implementations (most can use SPs, but can't be built entirely on them);
• DDL and administrative scripts.

They solve different problems, really. Use whichever one is more appropriate to the task at hand, and don't restrict yourself to just one or the other. After you work on database code for a while you'll start to get a more intuitive feel for these things; you'll find yourself banging together some rat's nest of strings for a query and think, "this should really go in a stored procedure."

Final note: Because this question implies a certain level of inexperience with SQL, I feel obliged to say, don't forget that you still need to parameterize your queries when you write dynamic SQL. Parameters aren't just for stored procedures.