SQL Stored Procedures vs. InCode TSQL
-
CodeBubba wrote:
unless the database is a host for multiple clients
Yes - that is where I am coming from, an environment with one database that serves multiple clients.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
In your case, then, yeah - updating the SP's makes the most sense. Of course you have to watch the API!
-
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
GuyThiebaut wrote:
IF statements within the stored procedure
That's a code smell.
-
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
Jonathan Shields wrote:
being able to make changes to your data access logic without doing a release is handy a bad idea
FTFY
-
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
And network load isn't an issue when the app/service/whatever is running on the same server as the database.
-
Personally, I'm with your architect. The most common reason given for using stored procs is that it provides some performance benefit but I have never seen that proven. The second reason that people like it is because it provides a layer of abstraction. Instead of looking at the SQL in your code, you simply call a descriptive SP like "getListOfThings" or "SetThingName". That does provide a benefit, especially if you have a large team with various experience levels working in the code - it makes the code more readable - but you can get the same benefit using other techniques. I personally have found that having a view of the actual SQL right there in the code is very valuable. It saves you the time of having to write SP's and/or go look up the SP to figure out if the bug you're looking for is in the code or in the SQL. A few years ago there was a lot of interest in Object Relational Models and basically finding a way to get the SQL into the code in a way that would allow the compiler to throw errors when there are mistakes in the SQL itself. I have not heard a lot about it lately But that whole concept sort of proves the point that having visibility of the SQL in your code is desirable. One last "benefit" that is often cited for using SP's is that you can fix a bug without having to do a new build of the app. And while that is true, there is a dark side to that as well. Many times the SQL needs to change from one build to the next. When you use SP's, you sometimes end up with "getListOfThings_1" and "getListOfThings_2" because you need one SP that works for one build and a different version of the SP that works with the next build. If you maintain the actual SQL in with the code, then the correct SQL for each build lives with that build.
ru55r3353 wrote:
"getListOfThings" or "SetThingName"
Which is likely to be the name of the method in the DAL anyway, so what have you gained?
-
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. JimmyRopesJimmyRopes wrote:
- In the majority of cases they execure faster after the first call.
True. But depending on the situation cmd.Prepare() will do almost the same.
JimmyRopes wrote:
- Protection from sql injection.
Heard or Parameters?
JimmyRopes wrote:
- 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.
Cool. If you only have one client or a Centralized system. IF NOT IS HELL ON EARTH DATABASE VERSION CONTROL MAINTAINABILITY.
Paulo Gomes Over and Out :D
-
And network load isn't an issue when the app/service/whatever is running on the same server as the database.
Yes, but don't forget customers will install your software in environments completely different than your development machine. Like when the database is under the dreaded control of the IT department. In a recent example, a simple table refresh triggered by a timer across a slow WAN through a VPN to a database in another city caused noticeable customer discomfort. Something to think about when you architect your code.
-
JimmyRopes wrote:
- In the majority of cases they execure faster after the first call.
True. But depending on the situation cmd.Prepare() will do almost the same.
JimmyRopes wrote:
- Protection from sql injection.
Heard or Parameters?
JimmyRopes wrote:
- 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.
Cool. If you only have one client or a Centralized system. IF NOT IS HELL ON EARTH DATABASE VERSION CONTROL MAINTAINABILITY.
Paulo Gomes Over and Out :D
Paulo_JCG wrote:
JimmyRopes wrote:
- In the majority of cases they execure faster after the first call.
True. But depending on the situation cmd.Prepare() will do almost the same.
If I am not mistaken you would have to prepare the cmd every time incurring the overhead in every iteration. When a stored procedure is executed it is cached and there is no additional overhead for subsequent calls.
Paulo_JCG wrote:
JimmyRopes wrote:
- Protection from sql injection.
Heard or Parameters?
I presume you are trying to say heard of parameters. The answer is yes that is what I pass to a stored procedure. :-D
Paulo_JCG wrote:
- 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.
Cool. If you only have one client or a Centralized system.
IF NOT IS HELL ON EARTH DATABASE VERSION CONTROL MAINTAINABILITY.Maybe I am a bit dim witted but how is that worse than maintaining multiple versions of assemblies to cope with database version control? :confused:
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 -
Yes, but don't forget customers will install your software in environments completely different than your development machine. Like when the database is under the dreaded control of the IT department. In a recent example, a simple table refresh triggered by a timer across a slow WAN through a VPN to a database in another city caused noticeable customer discomfort. Something to think about when you architect your code.
code_junkie wrote:
when the database is under the dreaded control of the IT department
Definitely not a time to have business logic in the database.
-
Paulo_JCG wrote:
JimmyRopes wrote:
- In the majority of cases they execure faster after the first call.
True. But depending on the situation cmd.Prepare() will do almost the same.
If I am not mistaken you would have to prepare the cmd every time incurring the overhead in every iteration. When a stored procedure is executed it is cached and there is no additional overhead for subsequent calls.
Paulo_JCG wrote:
JimmyRopes wrote:
- Protection from sql injection.
Heard or Parameters?
I presume you are trying to say heard of parameters. The answer is yes that is what I pass to a stored procedure. :-D
Paulo_JCG wrote:
- 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.
Cool. If you only have one client or a Centralized system.
IF NOT IS HELL ON EARTH DATABASE VERSION CONTROL MAINTAINABILITY.Maybe I am a bit dim witted but how is that worse than maintaining multiple versions of assemblies to cope with database version control? :confused:
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. JimmyRopesJimmyRopes wrote:
If I am not mistaken you would have to prepare the cmd every time incurring the overhead in every iteration.
When a stored procedure is executed it is cached and there is no additional overhead for subsequent calls.No. A .Prepare() before iterating will cache the instruction with only 1 time overhead. The only difference is in the fact that the SQL instruction has to be parsed at the first execution.
JimmyRopes wrote:
I presume you are trying to say heard of parameters. The answer is yes that is what I pass to a stored procedure. :-D
Keyboards mistake (doesn't understand what i mean). Yes those, you can pass them to TSQL as well.
JimmyRopes wrote:
Maybe I am a bit dim witted but how is that worse than maintaining multiple versions of assemblies to cope with database version control? :confused:
We are talking of different realities. As said before there's place for both Why multiple versions of assemblies? when you deploy a solution to several customers you can't afford having multiple versions of the database or the assembly. Normally you have a base Assembly, a base database, several configurations and pray for the update not to scr*w the database. Imagine you made a SP and adjusted it because a client needed some fancy option. Next general update you have to Update the base SP to add a new parameter/field: - will you remember that you changed it? - did someone else changed the SP for some reason? - How will you feel when an angry call tells you your update just made another application (they had feeding on information provided by the SP) stop working. Final thought If you control the environment easily then go with SP. If not, try to keep Logic where you can control it (the Assembly). Many times i have solved a problem in a client by using database features it came back bitting me in the Future... (it hurts) :D P.S.: Sorry for any syntactic or semantic error.
Paulo Gomes Over and Out :D
-
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.
The problem may be in the comfort level of the "architect". I had a developer get to spend about a year of time building a new CMS. Said developer was scared of SQL and was not fluent in the programming language he was working with; rather he was learning the language as this system was being built. He ended up building a system which relied heavily upon an ORM which is basically dead. What we ended up with was a state of the art system which was slower than pondwater in winter. And all of the systems we had were slower as well; our bandwidth was exponentially larger. A quick analysis via the tools in Sql Management Studio showed that the same basic request when compared from our old to our new CMS generated 25x the SQL calls, which were wide open SELECT * JOIN crap when all I needed was a SELECT [ID] My task was to start tackling the performance issues. Chunks of the CMS were rewritten to get rid of the ORM and replace it with SP executions. That simple request that I made earlier was reduced to only using 40% of the original resources, and the executables themselves are 15% smaller in total; which would probably be much larger if we eliminated all the additional features that have been added. For me to update the existing deployments of the CMS would require me to go into the projects and recompile on a case by case basis due to continual updates to the system. For me to update the SPs in use would be to just run an SP_MsForEachDB command with an Alter command inside. ~Madd Myche from around Milwaukee These comments are based on my opinions which are based on my real world experience. Your mileage may vary.
-
Hi Michael, I'd be very curious if you could expand a little bit on your reply. I'm the architect of the application at my business (inherited the architecture, which is in desperate need of re-architecting because of performance concerns). We have over 2000 stored procedures that implement ALOT of business logic. On the webservice side, we are running 25 application servers, with the 3 threads per server to try and handle the load. According to SQL Server (Quest Performance Analysis), it's handling everything just fine, but the response time in general of the application is very poor. Right now, our only way to try and improve the performance is by adding more application servers into the pool.
I think you would have to qualify your statement "it's handling everything just fine.". Does that mean that SQL Server is not having large request queuing? If SQL is not a bottleneck, you would have to find out what is. In general, it is easier and more cost-effective to scale out application logic tiers of a multi-tier application, than the database server. this is the general rule I think is true, probably for all relational databases. Usually you only get one database server, and it is very very expensive to scale. SQL Server enterprise is $4k / cpu core. Long-running requests that perform business logic could easily cause request queuing and blocking problems for SQL Server. An application will typically just sit there waiting for a response from the db server. The longer it waits, the less performant it will be, or at least seem to be. If you keep the requests to the database shorter-running (more granular, e.g. simple select statements that use indexes and only bring back the data you actually need), then your application servers will spend less time waiting for reponses from SQL, SQL will spend less time performing logic and more time retrieving data which is what it excels at. The queueing will be reduced on the database server, and you can increase the # of application servers and experience a predictable increase in the application's capacity to handle more. another thought just occurred to me, it sounds like your 25 web/app servers are all hitting the database directly. perhaps there is a case for creating a data access tier on a separate (small) set of servers that does all the database communications. using that approach you could implement some caching there. I would do a POC and also see if it makes sense to put the business logic in that same tier. anyways, that's my 2cents, hope that helps