SQL Stored Procedures vs. InCode TSQL
-
There are three major reasons I use stored procedures over in code TSQL; speed, safety and maintainability. 1) In the majority of cases they execure faster after the first call. 2) Protection from sql injection. 3) If you need to change anything about the query (table changes, logic changes, etc.) you do it in the stored procedure and do not have to re-release the assembly.
The report of my death was an exaggeration - Mark Twain
Simply Elegant Designs JimmyRopes Designs
Think inside the box! ProActive Secure Systems
I'm on-line therefore I am. JimmyRopes- Bullshit 2) Bullshit 3) That is the main reason not to use stored procedures.
-
In my company we are using Incode TSQL instead of Stored procedure and every time we discuss it with our architect he says that there is no need for stored procedures because we can't debug it (within c#), but TSQL can and the system is not that big(there is no need to get use of SP precompilation). Does anybody know the advantage for Stored procedure upon the inside code transact SQL except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage) but for debugging code for big stored procedures it is good to use InCode TSQL to know what is going wrong.
It really depends on your application, imho. If your system is small and is not expecting growth in near future, then SP might be a good choice for, e.g., performance reasons. If your system is expecting to grow, then you must consider other factors: 1) Databases are performance bottlenecks when the number of concurrent visitor grow. 2) Databases (software and hardware) are expesive to have. In this case you might consider shifting a portion of computation loads to many cheap servers using InCode (TSQL + a kind of in-memory database system, memory chips are not expensive nowadays) and reduce the complexity of the database system (for performance and cost reasons), even when one server does not perform as good as SP on the database for a single user, many of them could beat the SP approach for many users. This is doable using the right tools, for example, the program in the signature is designed according to a relational database schema, but it is then 'virtualized' to has no real database to back it up. Its data are just e-mail files on a user's hard disk:cool:. You know, disk file replication is much easier then a database replication, etc ...
Having way too many emails to deal with? Try our SQLized solution: Email Aggregation Manager[^] which gets your email sorted, found and organized beyond known precision.
-
In my company we are using Incode TSQL instead of Stored procedure and every time we discuss it with our architect he says that there is no need for stored procedures because we can't debug it (within c#), but TSQL can and the system is not that big(there is no need to get use of SP precompilation). Does anybody know the advantage for Stored procedure upon the inside code transact SQL except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage) but for debugging code for big stored procedures it is good to use InCode TSQL to know what is going wrong.
One of the advantages sp have over other methods is security. You can deny access to all of your tables, and allow exec access to stored procs. That way even if someone gains access they can still only execute stored procs. You can also add logging code, security etc. to stored procs
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
In my company we are using Incode TSQL instead of Stored procedure and every time we discuss it with our architect he says that there is no need for stored procedures because we can't debug it (within c#), but TSQL can and the system is not that big(there is no need to get use of SP precompilation). Does anybody know the advantage for Stored procedure upon the inside code transact SQL except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage) but for debugging code for big stored procedures it is good to use InCode TSQL to know what is going wrong.
How about none of the above? That is, an ORM. At my job, we use Entity Framework. We have only needed a few stored procedures.
-
In my company we are using Incode TSQL instead of Stored procedure and every time we discuss it with our architect he says that there is no need for stored procedures because we can't debug it (within c#), but TSQL can and the system is not that big(there is no need to get use of SP precompilation). Does anybody know the advantage for Stored procedure upon the inside code transact SQL except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage) but for debugging code for big stored procedures it is good to use InCode TSQL to know what is going wrong.
At our company we have some SQL guys and some programmers. We have some applications that are used in an environment where products come rolling on a tire, need to be weighed, scanned, etc. For these applications it's often necessary to make real quick adjustments without the people working with it needing to restart. Most of the business logic for these applications go into SP's. What's also a bonus is that during development a SQL guy can easily implement the business logic in an SP while the programmer creates the GUI and simply calls the SP's with the required parameters. After the release of such applications the SP guy can usually give support while our developer can work on other projects (or give support to customers that didn't get business logic in SP's). Personally I am not a big fan of SP's. Whenever I'm debugging software that for some reason doesn't work anymore after a few years I always get bugged by SP's. They take the flow out of your code and debugging them is not as easy as debugging code in Visual Studio. Now with LINQ and all I hardly use SP's anymore at all. The downside to this, of course, is that a change in a query requires the whole (or part of the) application to be re-released and the users need to restart the application (unless you put the business in some middle-tier). All in all I would say the use of SP's depends on the type of application you're making, the people you have available and in some cases speed, safety and other DB perks.
It's an OO world.
public class Naerling : Lazy<Person>{
public void DoWork(){ throw new NotImplementedException(); }
} -
- Bullshit 2) Bullshit 3) That is the main reason not to use stored procedures.
I do not agree with your comments. 1) Depending of the type of projects, SPs can be very usefull because a part of the customer specific business logic can be put in it. The C# code in the application can stay the same. 2) It is easier to debug when a customer complains that his application is not working properly. 3) it gives a performance enhancement by reexecuting the same request. 4) I agree that it can be difficult to maintain. Read some technical article about it. Regards Thierry
-
It is a good idea to separate the business logic from the user interface and this is what stored procedures will allow. Think of it this way - what happens if there is an error with your TSQL that only becomes apparent after a week of running the application, or if you need to change the SQL? To fix it you will have to roll out a new executable. However if you have the SQL in a stored procedure outside of the .Net code you can make a change in seconds and not need to re-issue the executable.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Is disagree strongly. Stored procedures should only contain data logic, not business logic. Business logic belongs in the application the business uses, not the database. Your justification for stored procedures as easy way to bug fix horrifies me
____________________________________________________________ Be brave little warrior, be VERY brave
-
Is disagree strongly. Stored procedures should only contain data logic, not business logic. Business logic belongs in the application the business uses, not the database. Your justification for stored procedures as easy way to bug fix horrifies me
____________________________________________________________ Be brave little warrior, be VERY brave
Adriaan Davel wrote:
Your justification for stored procedures as easy way to bug fix horrifies me
Oh well... :^)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
In my company we are using Incode TSQL instead of Stored procedure and every time we discuss it with our architect he says that there is no need for stored procedures because we can't debug it (within c#), but TSQL can and the system is not that big(there is no need to get use of SP precompilation). Does anybody know the advantage for Stored procedure upon the inside code transact SQL except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage) but for debugging code for big stored procedures it is good to use InCode TSQL to know what is going wrong.
Stored procedures are good when more than 1 operation needs to happen in the database and you can avoid going back to a calling application, that's all I can think of. 1) The do not perform better than parametrised queries, parametrised queries also get compiled and protects against injection 2) They are quite bad with complex IF scenarios (TSQL mostly perform better here), often requiring more than 1 stored proc to be created 3) They are an additional SQL object that needs to be managed 4) Version compatibility matrices can get NASTY with stored procs, with TSQL the application may be able to run on a different version to the database (often not, often true in read scenarios) I used to 'everything in procs', now I do as much as possible in parametrised queries
____________________________________________________________ Be brave little warrior, be VERY brave
-
One of the advantages sp have over other methods is security. You can deny access to all of your tables, and allow exec access to stored procs. That way even if someone gains access they can still only execute stored procs. You can also add logging code, security etc. to stored procs
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
This can work the other way... For BI users you want to give read access to tables and now you have to manage security on stored procs as well
____________________________________________________________ Be brave little warrior, be VERY brave
-
This can work the other way... For BI users you want to give read access to tables and now you have to manage security on stored procs as well
____________________________________________________________ Be brave little warrior, be VERY brave
Use views if you need to give someone access to data using a tool that doesn't support Sp
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
Stored procedures are good when more than 1 operation needs to happen in the database and you can avoid going back to a calling application, that's all I can think of. 1) The do not perform better than parametrised queries, parametrised queries also get compiled and protects against injection 2) They are quite bad with complex IF scenarios (TSQL mostly perform better here), often requiring more than 1 stored proc to be created 3) They are an additional SQL object that needs to be managed 4) Version compatibility matrices can get NASTY with stored procs, with TSQL the application may be able to run on a different version to the database (often not, often true in read scenarios) I used to 'everything in procs', now I do as much as possible in parametrised queries
____________________________________________________________ Be brave little warrior, be VERY brave
-
Stored procedures would also contain BL which is not good for an N-Tier application. and harder to maintain. again,, it depends
plextoR
Yep, I avoid doing Business Logic in a database, also good when using more than 1 DB technology
____________________________________________________________ Be brave little warrior, be VERY brave
-
Yep, I avoid doing Business Logic in a database, also good when using more than 1 DB technology
____________________________________________________________ Be brave little warrior, be VERY brave
This is another scenario where using SPs may not be the best solution. When I'm going to develop an application that works with more than one DB provider. If I go for SPs, I had to make a separate layer (SPs) for each provider and repeat the code even if my queries are standard PLSQL and straight forward.
plextoR
-
In my company we are using Incode TSQL instead of Stored procedure and every time we discuss it with our architect he says that there is no need for stored procedures because we can't debug it (within c#), but TSQL can and the system is not that big(there is no need to get use of SP precompilation). Does anybody know the advantage for Stored procedure upon the inside code transact SQL except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage) but for debugging code for big stored procedures it is good to use InCode TSQL to know what is going wrong.
As others already said, it depends. I used to work for a company where we were accessing the same database through three different programming languages: PHP, Java and C++. As these were all different programmers/teams they were not all on the same proficiency level writing SQL and performance of the database was a huge issue. So we decided to switch over to SPs, properly documented and versioned in CVS as a sort of common interface for all three teams to access the data.
-
- Bullshit 2) Bullshit 3) That is the main reason not to use stored procedures.
Can you please elaborate. Bullsh*t is strong statement and I wonder what made you think that way. Thanks.
Mislim, dakle jeo sam.
-
Stored procedures are good when more than 1 operation needs to happen in the database and you can avoid going back to a calling application, that's all I can think of. 1) The do not perform better than parametrised queries, parametrised queries also get compiled and protects against injection 2) They are quite bad with complex IF scenarios (TSQL mostly perform better here), often requiring more than 1 stored proc to be created 3) They are an additional SQL object that needs to be managed 4) Version compatibility matrices can get NASTY with stored procs, with TSQL the application may be able to run on a different version to the database (often not, often true in read scenarios) I used to 'everything in procs', now I do as much as possible in parametrised queries
____________________________________________________________ Be brave little warrior, be VERY brave
Adriaan Davel wrote:
- The do not perform better than parametrised queries, parametrised queries also get compiled and protects against injection
Use parameterised stored procedures to avoid injections attacks.
Adriaan Davel wrote:
- They are quite bad with complex IF scenarios (TSQL mostly perform better here), often requiring more than 1 stored proc to be created
Stored procedures in SQL Server use TSQL so just use IF statements within the stored procedure.
Adriaan Davel wrote:
- They are an additional SQL object that needs to be managed
Not if the interface that accesses the database is written to take care of this side of things. Calling a stored procedure or running SQL directly via .Net requires a connection the command and parameters - so I can't see what this additional object is that needs managing.
Adriaan Davel wrote:
- Version compatibility matrices can get NASTY with stored procs, with TSQL the application may be able to run on a different version to the database (often not, often true in read scenarios)
What's this thing with comparing TSQL and stored procedures(see my response above - a stored procedure can contain TSQL)? I can only conclude that you may not know what a stored procedure is.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Adriaan Davel wrote:
- The do not perform better than parametrised queries, parametrised queries also get compiled and protects against injection
Use parameterised stored procedures to avoid injections attacks.
Adriaan Davel wrote:
- They are quite bad with complex IF scenarios (TSQL mostly perform better here), often requiring more than 1 stored proc to be created
Stored procedures in SQL Server use TSQL so just use IF statements within the stored procedure.
Adriaan Davel wrote:
- They are an additional SQL object that needs to be managed
Not if the interface that accesses the database is written to take care of this side of things. Calling a stored procedure or running SQL directly via .Net requires a connection the command and parameters - so I can't see what this additional object is that needs managing.
Adriaan Davel wrote:
- Version compatibility matrices can get NASTY with stored procs, with TSQL the application may be able to run on a different version to the database (often not, often true in read scenarios)
What's this thing with comparing TSQL and stored procedures(see my response above - a stored procedure can contain TSQL)? I can only conclude that you may not know what a stored procedure is.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
I have been coding in SQL for 10+ years, I am very aware of what a stored procedure is.
GuyThiebaut wrote:
Stored procedures in SQL Server use TSQL so just use IF statements within the stored procedure.
Ever seen what happens to performance when an IF statement send processing outside of a pre-compiled execution plan? I don't even know what you mean by saying that a stored procedure uses TSQL, we are comparing sending TSQL strings from an application as code versus calling a stored procedure in the database.
GuyThiebaut wrote:
Calling a stored procedure or running SQL directly via .Net requires a connection the command and parameters - so I can't see what this additional object is that needs managing.
- You have a table in SQL to manage (security) 2) You have a stored procedure to manage (security) Has nothing to do with creating connections, are you suggesting that the LOB application manage security to SQL objects?
GuyThiebaut wrote:
What's this thing with comparing TSQL and stored procedures(see my response above - a stored procedure can contain TSQL)?
You don't seem to be understanding the question. If you are referring to the code in the stored procedure, I don't understand what you mean by "can contain TSQL", what else can it contain? If you are referring to calling dynamic SQL in a stored procedure, yes I agree, but that is detail we have not discussed and was not asked.
____________________________________________________________ Be brave little warrior, be VERY brave
-
Adriaan Davel wrote:
Your justification for stored procedures as easy way to bug fix horrifies me
Oh well... :^)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Really can't believe people still advocate logic in stored procedures. Great way to get bugs that are very painful to track down. I wouldn't advocate stored procs or embedded SQL. Look at all the ORMs (NHibernate, entity framework etc). Also get your architecture sorted. My view is that the business logic goes in the middle tier (Web service). Then you have your unit test in that tier.
-
In my company we are using Incode TSQL instead of Stored procedure and every time we discuss it with our architect he says that there is no need for stored procedures because we can't debug it (within c#), but TSQL can and the system is not that big(there is no need to get use of SP precompilation). Does anybody know the advantage for Stored procedure upon the inside code transact SQL except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage) but for debugging code for big stored procedures it is good to use InCode TSQL to know what is going wrong.
These arguments sound a lot like the old debate between writing in C or writing in Assembler. Perhaps it's time for someone to develop a compiler which compiles (insert favorite coding language here) into stored procedures as needed to eliminate the need to write and maintain them ourselves.
