SQL Stored Procedures vs. InCode TSQL
-
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.
-
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.
-
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.
-
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.
TSQL is only for INSERT, UPDATE, and DELETE statements, anything else should be avoided. They only exception that should be considered is network load and the fact that you have to share that bandwidth with everyone else. $.02
-
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.
Hi if you are not using EF or similar, stored procedures are vastly preferable except for simple statements which don't use user input. Keeping your data access logic separate from your business logic is good practice and being able to make changes to your data access logic without doing a release is handy. This is in the real world where mistakes do happen, design has to be rethought and changes have to be made in timescales you don't like. If you are in the Entity Framework world, you can still use them but its harder. Personally I prefer accessing SQL stored procedures directly from c# (still creating separation using a DAL project). Jonathan
-
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.
Adam Machanic wrote an excellent article about this about eight years ago. To SP or not to SP in SQL Server: an argument for stored procedures, The database-as-API approach I don't really have much to add to this excellent article other than that if you are making a decision about using stored procedures on SQL Server versus not using stored procedures, don't do it based on "performance" - there is no difference on modern servers. Do it because you want a securable, testable, maintainable API to your database. Your application may not need that. If you don't need it, then you don't need 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.
In my opinion; common re-usable code that manipulates the data and need performance at the database level needs to reside in the database in the form of stored procedure and triggers where the queries can be tweaked. If it is business logic then it should be in the middle tier. Data that needs to be modified and inputs validated resides at the client level avoiding unnecessary network round-trips. There are several other factors the DBA and architect have to take into account like latency, network round trip etc. All this should come with experience. Both Database and application group should work in tandem; The reason one will not agree with other are for reasons listed below: 1. politics 2. job security 3. bragging rights 4. blame game thanks.
-
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.
This is a great answer! thanks Shuqian Ying. old topic though. A database server is a very expensive resource, and typically there's only ever one. stored procedures have a use I am sure, but on large systems where you need high concurrency, they are a killer. it would be like using your relational database server as a database server and an application server--just because you can, doesnt mean you should. if your app logic runs in c# code on an application server, then to scale the app would often just mean throwing in another cheap app server or two. The best illustration i have seen on how to properly scale apps from the db through to the clients is from an albeit quite dated book by Joseph Moniz: "Enterprise Application Architecture with VB, ASP, MTS". the first 100 pages which is on architecture and scaling, are still relevant today, and worth the effort to obtain the book and read. There are other really good reasons not to use stored procs if possible, two that I can think of are: lower all-around development costs (dev, testing, promotion, maintenance, versioning), and database-vendor neutrality (T-SQL wont run on Oracle; but DML SQL is at least very similar across db vendors).
-
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 don't think that debugging has much to do with it, you would normally debug the SQL in SMSS anyway. Personally, I use a mix of parameterized in-line queries and SPs. There are advantages/disadvantages to both, and both have their place. If I'm just doing something like looking up a user's name to display, I'll often use inline SQL to do a quick query. No need to create a SP for every simple task, plus it's more convenient to have the SQL right there. However, if I'm doing something that will be done in other places, like looking up a customer's account info, I'll make that a SP. Why? Because that way you have one place with code to pull that data and you can maintain it there instead of digging into the C# code to find all the snippets of SQL that are doing the same thing (I guess you could do the same thing by creating a class that uses inline SQL, but at that point there's no reason not to make it a SP and every reason to do so). This makes things a hell of a lot easier if you, say, make changes to the accounts table and need to update the SQL accordingly. Performance is an issue as well, but the big problem probably isn't what you're thinking of: the real problem with heavyweight inline SQL is that page processing stops while you wait for the SQL to run. This--waiting for the SQL to run--is the main reason for performance problems on a website. Do you really want a complex query hitting heavy-use tables holding up your page loads? Sometimes you can't help it, but other times you can: if you make the query a SP then you get more than just caching, you can easily run it in a different thread and keep it from slowing down the presentation side. I've done this with things like running complex reports and generating tax forms: execute the SP in a different thread, dump to processing table, then display the data when it's ready. A nice view of the processing table with a status of "pending" is a lot better than staring at a spinning circle wondering what's going on.
-
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
Yeah, but in either case you have to roll it out to all clients. That, unless the database is a host for multiple clients, then there might be an advantage. Whether you change code in the DB (requiring some kind of update script) or patching the executable code, the difference is highly debatable. Either update process can be boilerplated.