SQL Stored Procedures vs. InCode TSQL
-
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.
Given the facts you provided, the question should have been "How to approach an architect who does not know his business?" (unless you have no requirement whatsoever on performance)
Never underestimate the difference U can make in the lives of others.
∫(Edo)dx = Tzumer ∑k(this.Kid)k = this.♥
-
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.
I prefer using SPs whenever it's possible. but in some work environments, you are required to attach condition to existing SQL statements or constructing SQL statements based on program flow. it would be slower but this is can't be done using SPs except when you use
exec
and in such case you will lose the value of SPs or you will - and that mostly happen - have to make large number of SPs or put lot of business on them. but if we are talking about parameterized queries, then it differs: when using TSQL with parameterized queries you nearly get the same performance of SPs as it's also cached. and yes SPs are debuggable but debugging parameterized queries is much much easier. again if you are going to ask, I'll definitely choose SPs but with respect to the above issues. shortly, the correct answer is 'it depends'. and the correct question should be 'What scenarios would using SPs is preferred to TSQL?'
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.
Personally, I've had very little use for SP's - there are a few places where I've had implementations in C# that could easily have been done in SP's to perform the necessary logic. The approach I was using however was, write the business logic in an assembly on the client for easy debugging, then once I was happy with it, pull in the assembly on the middle-tier server where it would then permanently live. This avoided the issue of having to push application updates whenever the business logic needed to be tweaked. Also, I'll relate this story - in two companies for which I've done some consulting, the application developers did not know SQL and the DB guru's did not know VB, which sadly as the language chosen for the application implementation because, according to management, VB devs are cheap and plentiful and any language beginning with the letter 'C' is harder to understand. Anyways, the result was that all the transactions, and I mean ALL, including simple CRUD statements, were implemented in SP's, resulting in a monolithic and unwieldy SQL code base - very little re-use, no documentation, huge SP's, because, quite frankly, the DB guru's, while well versed in database architecture, were not developers and wrote spaghetti SQL. That's certainly not to say that SP's are bad--it was more a result of the way management worked with both groups of people, making communication and a cross-training of skills almost impossible. Marc
Testers Wanted!
Latest Article: User Authentication on Ruby on Rails - the definitive how to
My Blog -
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.
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 -
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
-
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
-
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
-
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.
