April 29, 2013 at 11:43 am
I recently had a conversation with a friend of mine who is an Oracle developer. I was discussing how we develop in MS SQL and that we create stored procedures to handle all of our data manipulation. He immediately made a face at me and stated "Why in the world would you do that?!?" His claim is that it is far better to pass SQL statements directly from the code reducing the MS SQL server workload.
I stopped for a minute to think, could this be true? Have I been developing the wrong way for all of these years? Unfortunately, I am not fast enough to respond but after some thought I am not sure I would change a thing since I do not want my UI developers to have to understand the structure of my database. In addition, since I have customer sites hosting their own copy of our software. If I make a SQL change, I just send a script and all is good. My friend's way, I would have to send out a completely new UI to all of my customers and go through a full implementation process.
Can anyone shed some light on best practices for coding a UI that interacts with MS SQL and whether or not one should use stored procedures for the SELECT/INSERT/UPDATE/DELETE statements? I just can't believe that it is good practice to code your SQL code in application and not use the database. After all the database engine is going to have to run the statement that is passed to it and their is no precompiled execution plan for that SQL string.
Owen White
April 29, 2013 at 12:49 pm
fsuoj (4/29/2013)
their is no precompiled execution plan for that SQL string.
Ad-hoc SQL has exactly the same plan caching and reuse as stored procedures, the matching is just done on a hash of the statement text rather than the procedure's object id. Depending on whether or not you parameterise those ad-hoc queries or embed literal values, the cached plans may be reusable or may not.
Stored procs - encapsulation, isolation, separation of concerns, API to the database, can be tuned without touching the front end code.
Ad-hoc queries - easier to write, can be generated from app or ORM
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 1:07 pm
Would you consider it bad programming to use SQL Stored Procs vs. Adhoc queries? His argument is that when you are hitting a large data set, the stored procedures on the server cause extra overhead. I do not believe it but I can't say for sure.
Owen White
April 29, 2013 at 1:12 pm
I disagree with your friend. I do not believe that stored procedures put additional overhead on the server when compared to ad hoc queries.
April 29, 2013 at 1:32 pm
fsuoj (4/29/2013)
Would you consider it bad programming to use SQL Stored Procs vs. Adhoc queries?
No, absolutely not. Other way around.
His argument is that when you are hitting a large data set, the stored procedures on the server cause extra overhead.
And how exactly does he claim that works?
So, a specific select statement passed across the network to the server has less impact than the same select statement run from a stored procedure in the database? I'd love to hear the logic behind that belief.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 2:06 pm
fsuoj (4/29/2013)
His claim is that it is far better to pass SQL statements directly from the code reducing the MS SQL server workload.
I personally think that his claim is bold and is certainly not true for all circumstances.
If I make a SQL change, I just send a script and all is good. My friend's way, I would have to send out a completely new UI to all of my customers and go through a full implementation process.
Here you use the stored procedure as an abstraction layer. This has the advantages that you describe.
Can anyone shed some light on best practices for coding a UI that interacts with MS SQL and whether or not one should use stored procedures for the SELECT/INSERT/UPDATE/DELETE statements? I just can't believe that it is good practice to code your SQL code in application and not use the database. After all the database engine is going to have to run the statement that is passed to it and their is no precompiled execution plan for that SQL string.
Using a large and complex stored procedure does take some CPU of the server and this is a disadvantage. The amount of the CPU it takes is generally not a lot. And the advantages can be large. Generally the processing of the query will take far more of the resources. And the advantages next to the ones you describe can be reduced number of roundtrips between the application server and the database server, reduced number of rows send towards the application server.
So there probably can be circumstances that application code can be better than SQL-server code performing the same function. But certainly not all the time. And often programmers working close to the server within a Stored procedure are more familiar and better with set based coding which very often is better than procedural code.
But the world of procedural coding is large compared to set based code so many people claim that using an abstraction layer outside of the database is superiour. Personaly I do not agree with that, but I would not like to put this to the vote, because I expect to loose.
Good luck the next time you meet your friend.
Ben
April 29, 2013 at 3:16 pm
He did claim that he reserved procedures for complex items like a cascading delete of records that needed to be encapsulated in a transaction. However, he was insistent that single record actions were better handled from the application side. I actually have done both methods but have moved solely to the server side so that I can call the same stored procedure from any application and if I need to make a change to the stored procedure, I can just email a script or multiple scripts to the customer for implementation.
His scenario may best suit his needs since he is only hosting his own application and he is doing both the development and the database work. I still feel that the SQL server is built for transactions and cleaner when it comes to hanling security.
Thank you for your insight. I appreciate it. Like I said, I did not take the offensive as I was trying to figure out why his method would be better or worse than what I am use to doing. He did concede that since I have customers and had to make changes to 70 + applications that sending a script is much easier. None the less, I felt helpless.
Owen White
April 29, 2013 at 3:19 pm
GillaMonster,
Our argument was not over SELECT statements but more over DML. Also if you were performing more than just an UPDATE or INSERT or DELETE he stated he would use a procedure. But if you were updating a single row for an UPDATE or INSERT or DELETE then it was far better to use the application code to perform the action.
Owen White
April 29, 2013 at 3:23 pm
/vent on
No offense to your friend but has he been living under a rock for the last 10 -20 years? Using ad hoc queries directly in an application is awful. You have no separation of duties. Developers are forced to write their own queries. For some this is not a bad thing but for others they can't write a decent query to save their lives. The mindset he is describing also suggests that we shouldn't use classes in programming because it might cost an extra cycle or two. Stored procs are the database version of a method in OOP. Code reuse? Don't get that when you hard code your queries into your application. Has your friend ever heard of the super advanced new theory in software development commonly referred to as N-tier? I guess in his world 1 tier is still best practice.
/vent off
It is difficult when somebody you know in the industry basically tells you are doing it all wrong and you have a hard time defending your position.
I disagree with his position entirely. Why do a delete in the application? Let the application call a CRUD sproc. That way if there is some additional logic that need to happen or whatever, the DB can take care of it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2013 at 3:40 pm
fsuoj (4/29/2013)
GillaMonster,Our argument was not over SELECT statements but more over DML.
SELECT is a DML statement.
Exactly the same for insert, update, delete, why would a statement sent from across the network, parsed, optimise and executed be faster than a statement fetched from a stored procedure, parsed, optimised and executed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 4:18 pm
This is why you are the MASTER and I am the one seeking wisdom. When I stated DML i was thinking in terms of data manipulation but you are correct that a SELECT is indeed a DML statement. Just a case of ignorance I guess. You all have givin me some ammo to go back to him although a day late. I work in a bubble and am not exposed to industry best practices which is why I am so impressionable to other opinions. Although I did argue with him I did not have the backing to really rebutt what he was saying.
Owen White
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply