ASP.NET MySQL using stored procedures [closed]_问答_开发者

Closed. This question is opinion-based. It开发者_如何学Python is not currently accepting answers.

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 8 years ago.

Improve this question

Someone told me using stored procedures could be bad for database performance, that is better to have the query inside the code and call database.

For me is better to have a stored procedure since if I have to make a change in the database I don't have to recompile the whole web app to change a SELECT statement....

What do you think?

Did the person who told you this have a reason to back up their statement? If they can't explain it, then chances are they don't actually know what they're talking about, they're probably just repeating something they heard or mis-heard somewhere.

Stored procedures in general are considered to perform better, but not for the reason you suggest. Database engines internally parse the queries sent to them to develop an execution plan. (Think of it like compiling the query code.) With a stored procedure, the database saves this execution plan so it doesn't have to compile it later. So that step is skipped when using a stored procedure.

This same thing can apply to parameterized queries, etc. though. Modern database engines are pretty good at optimizing things. That's why I say "in general" above. There are a lot of details about the optimization of code-database interactions. But for the most part the statement holds true.

As for re-compiling your app, that's another story entirely. If you change the shape of the data being returned by the stored procedure, then wouldn't you have to update the app anyway? Changing the shape of a table shouldn't affect the app if the app doesn't need to know about it. (Unless you're using SELECT *, which isn't good practice for primarily this reason.) If, on the other hand, you're talking about using stored procedures as a kind of database API for apps to use which conceals the actual table schema, then that can certainly work. As long as the app isn't bound to a specific schema implementation, I'd consider that a good design decision. In this case I guess the stored procedures would be acting as part of the data access layer itself.

This debate has been running for a long, long time now.

In general, the performance difference between stored procs and embedded SQL is almost never an issue anymore - as David says, database engines are really good at optimizing queries. However, within stored procs, inexperienced developers have been known to write poorly performing code - classic anti-pattern is procedural, rather than set-based thinking - looping through a table record by record and updating that record, rather than an update statement with a where clause.

On the other hand, "manageability" continues to be a really big deal. And then it all depends on the people and processes you already have.

If your developers are not familiar with stored procs, I'd avoid them - you can end up with unmaintainable procedures which nobody wants to touch.

If your client code is tightly coupled with the database code (which is nearly always the case), you need to have a release process for keeping them in sync - and source code control for schema objects (inc. stored procs) is not always straightforward.

There's a risk that you end up with lots of stored procs which don't add value - simple CRUD operations. You could be creating a lot of code (which needs to be versioned, deployed and maintained) without adding any extra value.

Philosophically, most current software architectures are based on object orientation in some way - stored procs inject procedural code into that paradigm (though you can deal with this by creating a database API, as David suggests - data as a service).

So, unless your application is focussed mostly on the database, in general, I recommend against stored procedures - they don't pay their way in terms of maintainability. Exceptions are batch routines - archiving, data cleansing etc. - which are often easier to manage as stored procs, but have almost no external dependencies.