posted by senfo(881) 4 years, 4 months ago 0

Littered with spelling and grammatical errors, this reads like it was written by a high school kid. Knowing that the author is from Germany, however, I'll put that aside. The problem is that ignoring those errors, the arguments against stored procs are ridiculously poor.

I'm not going to bother arguing about the layer of abstraction except to point out that there are code generators that write stored procs for you. That said, when you find yourself a template that you like (or develop your own), writing stored procedures takes minutes.

As far as security goes, you are completely missing the point because you're assuming that the only form of attack comes in the form of *direct contact* to the database server. Since I have entered the world of programming, I have been extremely fortunate in that I have never (that I know of) had an attacker gain direct access to my database server. I have, however, had flaws in my logic that allowed for SQL injection, which had the potential to allow attackers to compromise my system through my own application. By crafting my stored procedures and views in such a way that "hides" sensitive information and limits transactions to selecting, and only allowing updates and deletes when necessary, I greatly reduce the potential for SQL injection attacks that might be missed in application logic. Furthermore, this technique influences code reuse, which has the potential to reduce the level of effort applied to preventing SQL injection in an application.

As far as throwing more hardware at a performance problem, you're thinking linearly. You need to look at the requirements of the project and decide whether development time or performance is more important. There are times when performance can legitimately be sacrificed in favor of faster development time. However, there are certainly situations in which this is not the best approach. Read this article by Paul Nielsen, for example:

http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/12/10-lessons-from-35k-tps.aspx

On a final note, it should be important to note that LINQ can most certainly be used to query stored procs. I don't know much about NHibernate; however, Subsonic also has great built in support for querying stored procedures. The two can live together in harmony. ;)

Reply

posted by jesse(100) 4 years, 4 months ago 0

1. Change the link to the article please, not his blog (currently points to a different article) and 2. Why do a performance comparison when you can't understand the results, O Author?

Reply

posted by theblob(200) 4 years, 4 months ago 0

Senfo,
please read the article following the performance comparison. I've said:

'...if you feel that certain features lack of performance you should only switch those critical parts of your applications to stored procs. For instance you can tell NHibernate to call stored procs for certain queries...'

So I'm not an enemy of stored procedures at all but of the wrong usage. I've experienced that in my company SPs are most often used for CRUDs only. Without code generators.

Actually I'm not arguing against SPs but PRO NHibernate, Linq or any other ORM.

I see your point on security. But if you're a careful programmer and convert all input to the corresponding data types and filter text inputs for SQL statements you can create an applications as safe as if you've used SPs.

I would like to ask you how do you handle the problem of versioning of SPs? I think this is a great problem and I don't know a good solution.

Sorry for my bad grammar and spelling. Shame on me. =)

Reply

posted by theblob(200) 4 years, 4 months ago 0

Jesse,

I've run the comparison in the office using a dedicated SQL Server. The performance was inredible good when using SPs.

I've run the test a second time at home using a SQL Server installed on my PC. This was suprising because the stored proc was inredibly slow on my machine when reading data out of a table with 10 000 records. This must be an installation issue. And that's the only point I don't understand.

Reply

posted by dist0rti0n(125) 4 years, 4 months ago 0

I agree that you should favor an ORM when possible and use procs when speedy or complexity become an issue. Stored procedures are a necessary evil. Using them for every single CRUD operation on the database is folly, especially since a good ORM will use parameterized SQL which prevents injection attacks and will be cached up by SQL Server, exactly the same way a proc would be.

Code re-use is also a myth. In large enterprise applications the chances that developer A will find a proc written by developer B that does exactly what he wants it to is slim, and thats if he even bothers searching at all.

I've been using SubSonic for my latest project and its been a tremendous advantage, especially the strongly typed SP collection is generates for when you do need to call a proc.

I've used NHibernate as well, but only in a very limited capacity. It seems like the best fit for an enterprise environment since you can call procs when you need to and you can map your database to business entities with no external dependances, vs. something like SubSonic where you end up with references to SubSonic.dll everywhere.

Reply

posted by senfo(881) 4 years, 4 months ago 0

I actually meant to hit on versioning stored procs because I have some experience in this. Basically, what I do is script the stored procedure and save it into source control. I've tried maintaining separate files for each proc, as well as combining them all into a single proc. Both ways has its advantages and disadvantages.

One thing that I've been meaning to try out is the database versioning feature of Subsonic. I've heard that it does great job, I just have no experience using it.

By the way, I apologize if I came across as rude with my comment about grammar and spelling. It really wasn't a big deal and like I said, I forgive you since I'm guessing that English is not your native tongue.

Reply

posted by rev4bart(420) 4 years, 4 months ago 0

So....No PLINQ in this article? No indexing/hashing (similar to what SQL does with heaps)? No...in memory query performance test? No...testing direct expression trees?

LINQ like any technology is going to be accepted by a lot of people because of the brevity and ease of coding it provides compared to nesting tons of for loops/predicates etc. However, few will understand it and fewer will dig deeper to try to get a better understanding of what is going on under the covers.

Reply

posted by theblob(200) 4 years, 4 months ago 0

rev4bart,
indeed, no PLINQ or indexing or hashing. I just wanted to compare the SQL- / database features of Linq with NHibernate. Not all features of the Linq extensions.
Please feel free to have a look at my source and to make suggestions on how to improve my Linq-To-SQL usage. You can find the source in my article.

Reply

posted by theblob(200) 4 years, 4 months ago 0

senfo,

scripting the procs and save them into source control is a way I'm aware of but I think it's rather a manual way of versioning. You've mentioned to use code generators for SP creation to speed development time up. Manual versioning will slow it down again and depends on duteous developers.

What I think is most critical to versioning is that project teams use only one development-database-server for all developers. That means that one developer can break the application if he or she changes SPs which leads to changes in the client code. Hm...

Reply

posted by foobar(0) 4 years, 4 months ago 0

The problem with ORMs is that data persist (yes kiddies, the word data is a plural), and applications change. If you decide to store your data in a SQL database, then it behooves you to know SQL, and know it well. Five years from now (or whenever), when you're not using the .NET platform to access that data, having the data retrieval logic stuck in an obsolete platform could become a problem.

I think it's ludicrous to use an ORM for basic CRUD - really, how hard is it to wrie a SELECT statement, versus what it takes to write the scaffolding for Hibernate? It's also ludicrous to use them for advanced querying, since the DSLs that tools like Hibernate provide are much too simple compared to what SQL offers (figures, since SQL was written by computer scientists, and Hibernate was written by Java programmers).

Of all the programming related languages I've learned, SQL, along with C, are the only two I still use on a regular basis after 13 years in the industry. I guess I should put perl in there somewhere.

Reply

posted by malovicn(1590) 4 years, 4 months ago 0

Rico Mariani performance super guru blogged about same subject in 5 part posts

Here's linq to Post No 4

http://blogs.msdn.com/ricom/archive/2007/07/05/dlinq-linq-to-sql-performance-part-4.aspx

Reply