April 4, 2012 at 9:58 am
Hi Everyone!
Thanks for reading my question.
I was in a meeting yesterday for our new app rewrite.
I insisted in that meeting that the application should access the database through stored procedures only..there should not be any direct SQL queries.
The Dev team as usual opposed saying they are not writing SQL Code by themselves...instead, using the Entity Frame Work (Hibernate) generates the code for them and they don't want to go into the route of stored procedures...
The argument did not end..
Can you guys provide me some insights...!
Thanks again..
-Siva.
April 4, 2012 at 10:04 am
I have found that the Entity Framework can generate some absolutely horrible sql. The argument of not using sprocs is rather silly. The Entity Framework can use sprocs with little effort. Keep in mind that your job is to protect the data and one of doing that is through procs. This will allow you to enforce parameterization and therefore help remove the threat of sql injection. It is up to the developers to deal with that.
_______________________________________________________________
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 4, 2012 at 10:09 am
In addition, you have no control over the code generated by Hibernate. If the code is inefficient, it stays that way. Going to stored procedures allows you to develop high performing, scalable code. Making calls to stored procedures is really not that difficult. It creates an interface to the database that actually allows for changes to the database that can be hidden from the application as long as that interface remains intact.
There are, in my opinion, more good reasons for using stored procedures than not.
April 4, 2012 at 10:11 am
Are they using Entity Framework (part of the .NET Framework) or nHibernate?
I'm a big proponent of limiting data access to using stored procedures for the following reasons:
1. Security - no need to grant any direct table access. Some people argue that the user(s) still can run the SP's from outside the application, but at least they are using the SP's, and can't do a DELETE FROM table.
2. Maintenance - as long as the signature doesn't change on a stored procedure you can change the way the T-SQL is written and the application doesn't need to change so there is no need for application re-compiles or deployment.
3. Tuning - again, it is nearly impossible to tune a query generated by any ORM tool. It can be done, but most of the time the devs either don't know how to do it or don't want to take the time to do it (the reason they want to use an ORM tool anyway.
4. Separation from the schema - using SP's the underlying table structures can change and the application doesn't have to change as long as you don't change the signature of the SP.
Here are a few links that may help you:
http://tinyurl.com/DecouplingPrajdic
http://tinyurl.com/NielsenWhySps
http://tinyurl.com/ProsConsOfSps
http://tinyurl.com/SDLSqlInjection
http://tinyurl.com/OR-MsPanacea
http://tinyurl.com/BeaucheminDataAccess
All that being said, ORM tools have gotten much better at producing adequate SQL. You just need to be very careful when the SQL gets complex.
Edit: you have to remember you are on a site that caters to DBA's and data people, so you are gonig to get anti-ORM repsonses for the most part. If you go to StackOverflow you'll get a lot more arguments FOR ORM tools.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 10:11 am
There are a few different reasons to use stored procs. Plan reuse is one of them. Particularly on more complex queries the fact that it doesn't need to recalculate the execution plan can be a major cost savings.
Another is the fact that stored procs require less chatting with the client and as such result in a performance benefit. This article doesn't go at it from that angle but it does make the point:
SQL injection is another major benefit assuming they're not just calling the stored proc as SQL call in a string. There are other ways to protect against that but used stored proc calls is a pretty solid one.
April 4, 2012 at 10:16 am
cfradenburg (4/4/2012)
There are a few different reasons to use stored procs. Plan reuse is one of them. Particularly on more complex queries the fact that it doesn't need to recalculate the execution plan can be a major cost savings.
This isn't exactly true. The plans are still cached and in more recent releases there have been fixes to reduce cache bloat that was caused when passing string parameters.
Event he SQL Injection argument isn't as valid as it used to be because most ORM tools (LINQ to SQL, EF, nHibernate) use paramterised SQL and call sp_executesql so they do protect pretty well against SQL Injection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 1:49 pm
Thanks everyone for your response.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply