Stored procedures or inline queries?

Question

First of all, there is a partial question in this question , but this is not quite what I ask, so bear with me and go for it.

My question is: after seeing what SubSonic makes and great videos from Rob Connery, I need to ask: Should we use such a tool and make inline queries or make queries by calling a stored procedure ?

I don’t want to minimize any work from Rob (which I think is awesome), but I just want your opinion for this reason I need to start a new project and I am in the middle of the line; should I use SubSonic (or another similar tool like NHibernate), or will I just continue my method, which always calls the stored procedure, even if it's just like

Select this, that from myTable where myStuff = StackOverflow; 

Answer 1

No need to be this or that. If this is a simple query, use the SubSonic query tool. If this is more complicated, use the stored procedure and load the collection or create a data set from the results.

See here: What are the pros and cons of storing SQL in Stored Procs versus code and here are SubSonic and stored procedures

Answer 2

See the answers here and here . I use sprocs whenever I can, except when the red ribbon means it takes a week to get into the database.

Answer 3

Stored procedures are golden if you have several applications that depend on the same database. This allows you to define and maintain query logic once, rather than multiple places.

On the other hand, it is very easy for the stored procedures themselves to turn into a large messy mess in the database, since most systems do not have a good method for constructing them logically. And they can be more difficult to version and track changes.

Answer 4

I personally will not follow strict rules. Of course, at the development stages, you want to quickly change your queries so that I can embed them.

I will move on to stored procedures later, because they offer the following two benefits. I am sure that there are more of them, but these two will defeat me.

1 / Stored procedures group data and code to process / retrieve this data at one point. This greatly simplifies the life of your database administrator (provided that your application is significant enough to guarantee DBAs), as they can be optimized based on known factors.

One of the big DBA errors is special queries (especially clowns who don’t know what a full table scan is). Database administrators prefer to have good consistent queries that can tune the database.

2 / Stored procedures may contain the logic that is best left in the database. I saw stored procs in DB2 / z with many dozens of lines, but all the client needs to encode is a single line, for example "give me this list."

Since the logic for "this list" is stored in the database, database administrators can change the way they are stored and retrieved as they wish without compromising or changing the client code. This is similar to encapsulation, which has made object-oriented languages ​​more "clean" than the previous ones.

Answer 5

I performed a number of built-in queries and stored procedures. I prefer to use the stored procedure / view method more, as it gets a nice place to make changes if necessary. When you have inline queries, you always need to go and change the code to change the inline query and then restart the application. You may also have an inline query in several places, so you will have to change a lot more code than with a single stored procedure.

Then, if you need to add a parameter to the stored procedure, you still change a lot of code.

One more note: how often the data changes behind the stored procedure where I work, we have third-party tables that can be split into normalized tables or the table becomes obsolete. In this case, the stored procedure / view can minimize the impact that you have on this change.

I also wrote a complete application without stored procedures. He had three classes and 10 pages, not worth it at all. I think that there comes a time when it is excessive or can be justified, but it also comes down to your personal opinion and preference.

Answer 6

Are you going to access your database from just one application?

If not, then you are probably better off using stored procedures so that you can have a consistent interface with your database.

Is there a substantial cost to distributing your application if you need to make changes?

If so, then you are probably better off using stored procedures that can be modified on the server, and these changes will not need to be propagated.

Are you generally worried about the security of your database?

If so, then you probably want to use stored procedures so that you do not have to provide direct access to tables for the user.

If you are writing a small application that does not have a wide audience for a system that will not be used or accessible outside of your application, then embedded SQL might be fine.

Answer 7

I prefer embedded sql, unless the stored procedure has real logic (variables, cursors, etc.). I have been using LINQ to SQL recently and take the generated classes and add partial classes that have predefined common linq queries. I feel this speeds up development.

Edit: I know that I am going to surpass this. If you ever talk about foreign keys or stored procedures, you will get a demotion. I think database administrators need security ...

Answer 8

With Subsonic, you'll use inline, views, and stored procedures. Subsonic simplifies data access, but you cannot do anything in a subsonic request. Although the latest version 2.1 is improving.

For basic CRUD operations, inline SQL will be straightforward. For more complex data needs, you will need to create a view, and then you will make a Subsonic query in the view.

Stored procedures are good for more complex data calculations and data extraction. Custom search is usually always faster than procedural processing.

The current Subsonic app uses all three options with excellent results.

Answer 9

• Stored procedures group data and code to process / retrieve this data at one point. This greatly simplifies the life of your database administrator (provided that your application is significant enough to guarantee DBAs), as they can be optimized based on known factors.

• Stored procedures can contain the logic that best stays in the database. I saw stored procs in DB2 / z with many dozens of lines, but all the client needs to encode is a single line, for example "give me this list."

• the best advantage of using stored procs that I discovered is that when we want to change the logic, in the case of an embedded request, we need to go to everyone and change it and cancel each application, but in the case of a saved change, proc is required in only one place.

So use inline queries when you have clear logic; otherwise, they prefer stored procedures.

Answer 10

The benefits of a stored procedure (in my opinion)

• SQL is in one place
• You can get query plans.
• If necessary, you can change the structure of the database to improve performance.
• They are compiled and thus these query plans should not be created on the fly.
• If you use permissions, you can be sure of the requests that the application will make.