Otis (or anyone qualified, but especially Otis)--

This might be a stretch; but, I am going to ask for your help anyway.

If you have a moment, I think that your help could be used in this thread about "to use SPs or not to use SPs"...

http://forums.commercestarterkit.org/forums/thread/4992.aspx

...as I have already tried to talk some sense to them, to little or no avail.

Anyway, I was thinking that if you could just make a cameo appearance, a few sentences or so off the top of your head, it might go a long way.

I know that you give a lot and I really shouldn't ask for more; but, you are so damn good at this stuff, I thought you might have some fun.

Anyway, whether or not you post there, thanks at least for thinking about it.

(There is other stuff in that thread-- but, I think one of the central and most important points is the stuff about how "SPs are bad", and the reasons why.)

Thank you.

--Mark Kamoski

mkamoski wrote:

Otis (or anyone qualified, but especially Otis)--

This might be a stretch; but, I am going to ask for your help anyway.

If you have a moment, I think that your help could be used in this thread about "to use SPs or not to use SPs"...

http://forums.commercestarterkit.org/forums/thread/4992.aspx

...as I have already tried to talk some sense to them, to little or no avail.

Anyway, I was thinking that if you could just make a cameo appearance, a few sentences or so off the top of your head, it might go a long way.

I know that you give a lot and I really shouldn't ask for more; but, you are so damn good at this stuff, I thought you might have some fun.

Anyway, whether or not you post there, thanks at least for thinking about it.

(There is other stuff in that thread-- but, I think one of the central and most important points is the stuff about how "SPs are bad", and the reasons why.)

Thank you.

--Mark Kamoski

If you search around the internet, Frans has already waged that battle.. and even with his great wisdom, I do not believe a resolution was ever made. My take on it was that, 99% of the time it doesn't matter what you use.

alexdresko wrote:

mkamoski wrote:

Otis (or anyone qualified, but especially Otis)--

This might be a stretch; but, I am going to ask for your help anyway.

If you have a moment, I think that your help could be used in this thread about "to use SPs or not to use SPs"...

http://forums.commercestarterkit.org/forums/thread/4992.aspx

...as I have already tried to talk some sense to them, to little or no avail.

Anyway, I was thinking that if you could just make a cameo appearance, a few sentences or so off the top of your head, it might go a long way.

I know that you give a lot and I really shouldn't ask for more; but, you are so damn good at this stuff, I thought you might have some fun.

Anyway, whether or not you post there, thanks at least for thinking about it.

(There is other stuff in that thread-- but, I think one of the central and most important points is the stuff about how "SPs are bad", and the reasons why.)

Thank you.

--Mark Kamoski

If you search around the internet, Frans has already waged that battle.. and even with his great wisdom, I do not believe a resolution was ever made. My take on it was that, 99% of the time it doesn't matter what you use.

If both are being generated, I don't think it really matters which you use. SPs seem a better fit for reporting just because they can potentially be faster if things like table variables (or temp tables) are used/needed. But, for entity persistence, I don't know why you wouldn't use Dynamic Query generation as long as you have a code generator that syncronizes your db schema with your entity persistence information....

I guess you might use Stored Procedures on complex queries that needs parameters But not for complex computations.

Frans (Otis) blog post: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

I once was asked by theserverside.net to be the first replier to a pro-proc post. That thread's here: http://www.theserverside.net/news/thread.tss?thread_id=31953 and reply is here: http://www.theserverside.net/news/thread.tss?thread_id=31953#158113

As you'll see in that thread, it goes downhill pretty quickly so I quit that discussion. The blogpost Walaa links to is referred to pretty often, though I like my theserverside post more as it is IMHO better formulated.

One thing I've learned in the past 3 years is that I'll never ever participate in this debate again. It has calmed down now but there were times when I received one or two hatemails a week just because of that anti-proc posting. I mean... huh? Is this about world peace? No, it's about darn procedures...

So my advice to you is: leave these discussions alone: you will NEVER convince the pro-proc people, and you will only get a bad taste in your mouth and lose valuable time.

Once in a while we receive a request from someone who has an argument with his/her DBA over this issue and if we can help that person out to settle the argument in his/her favor. I stopped doing that too. It's not worth the time or effort. Life's too short to fight over such a stupid thing like pro/con procedures.

I might sound bitter and that's because I am bitter about this. Don't waste time on this, you'll see the arguments they use are always the same and no matter how many times people have disproven these arguments, they keep popping up. I mean... how many times do people have to explain procedures aren't stored in compiled form? ...

Otis--

Yes, yes, well that's what I thought. I just wanted to let you know about it, partly because I did link to your article and express the fact that you are right.

:-)

Anyway, you will always be, (for me)-- "the man who killed stored procedures".

Here is a little history. I was a pro-procedure person myself, a while ago. I was pretty strong on the idea of SPs for the usual reasons and I liked them (but, I must admit that I never had more than a mild liking for the SQL language itself). Anyway, one day, I read your seminal post "Stored Procedures Are Bad, M'Kay". I brushed it off, thinking "that guy is out-there". But, the camel's nose was under the tent. I started thinking about it. I could not get it out of my mind. I thought: "What if he is right?". Over the next couple of weeks, I did some more research (reading more of your stuff and the couter-argument and others) and finally I said-- "I'll give it a try". I have not looked back or regretted it ever since. I am SO happy to be done with that SQL crap-- you don't even know! Ug, if I never write another line of SQL it will be too soon! Every once in a while I get a contract that requires the use of SPs and, if I fail to convince them that it is a mistake (and I do try because it is my blood, sweat, and tears if we DO have to use SPs), then I am instantly and clearly reminded that SPs are crazy (excepting the exceptional use cases).

BTW, since I am contractor, I have the opportunity to evangelize a bit. I cannot be dictatorial; but, I do steer when I can. As such, guys like you, Otis, (and scads of others) are very important to guys like me in the trenches. We need answers. We need fast and simple solutions. We need them yesterday. As I have moved along, since LLBLGen Version 1, I have been using your stuff and convincing others. I have been able to turn-around not a few people to LLBLGen and to the "SPs are not as good as you think they are and, in fact, they are a bad development choice in many cases" way of thinking. You make it easy because I know your stuff works because you have done the homework-- I thank you for that.

So, it only took a couple of weeks to change my mind. I guess miracles do happen. You have made some changes, Otis, and for that, I thank you.

That said, I do realize that it does make sense to stop banging one's head against a wall. Whether it is actually using SPs or just trying to convince kool-aid drinkers to stop using SPs, it is clear that banging one's head against a wall yeilds nothing more than a bloody forehead. Ah well. C'est la vie.

BTW, if I might suggest/request-- please make sure your great posts are permanent links somewhere. I point people to the current links a lot and, if the links ever go dead so will I, and I am sure that I am not the only one.

(While I do understand your point, Otis-- please know that if you ever DO change your mind and decide to pick up the gloves again to bop a few lunkheads or save a few lemmings, you are ALWAYS welcome and, BTW, it is terribly entertaining, as well as instructive, for the rest of us.)

Thanks again.

--Mark Kamoski

Otis wrote:

...One thing I've learned in the past 3 years is that I'll never ever participate in this debate again...

OK, I understand that.

However, I want to ask a (friendly) question, that is a new twist on the topic given the advance in technologies in .NET 2.0, so, if you will indulge me...

How does the new SQL Server 2005 and .NET 2.0 functionality that, (basically), allows one to reference a .NET Assembly from with SQL Server 2005 and (basically) use SPs as pass-through wrappers to call the Assembly?

This, it seems to me, is one more nail in the SQL's coffin (at least for most use cases).

(Caveat-- I am not too experienced in using SQL Server 2005 in this way; but, at least on the surface, it looks like the technique may have a lot of potential.)

What do you think?

Please advise.

Thank you.

--Mark Kamoski

mkamoski wrote:

Otis wrote:

...One thing I've learned in the past 3 years is that I'll never ever participate in this debate again...

OK, I understand that.

However, I want to ask a (friendly) question, that is a new twist on the topic given the advance in technologies in .NET 2.0, so, if you will indulge me...

How does the new SQL Server 2005 and .NET 2.0 functionality that, (basically), allows one to reference a .NET Assembly from with SQL Server 2005 and (basically) use SPs as pass-through wrappers to call the Assembly?

This, it seems to me, is one more nail in the SQL's coffin (at least for most use cases).

(Caveat-- I am not too experienced in using SQL Server 2005 in this way; but, at least on the surface, it looks like the technique may have a lot of potential.)

What do you think?

Please advise.

Thank you.

--Mark Kamoski

I know you asked Otis (Frans) but I thought I'd chime in a bit. I see SQL as sort of like assembler language. It does its job and it does its job well, and it is a horrible pain to deal with

DQE's are sort of like a higher level language for SQL. SQL will not go away any time soon, but the ability to write it in its most basic form is becoming less and less important to application developers that previously had to know its ins and outs...much like most application developers today do not need much knowledge of assembler to make their applications.

Imbedding .NET calls inside stored procedures is just a bridge to get people out of SPs for the types of problems that SPs are not good at solving (anything except set-based problems).

mikeg22 wrote:

...I know you asked Otis (Frans) but I thought I'd chime in a bit...

Actually, I am hoping that a lot of people will add some thoughts.

Might as well go into some forum and start arguing about some poster's religion...

JimFoye wrote:

Might as well go into some forum and start arguing about some poster's religion...

Exactly! this sums it up quite nicely.

mkamoski wrote:

Otis-- Yes, yes, well that's what I thought. I just wanted to let you know about it, partly because I did link to your article and express the fact that you are right. :-)

No problem

Anyway, you will always be, (for me)-- "the man who killed stored procedures".

Heh. Well, let me state that I'm not against stored procedures in general. This forum still uses some of them (basicly because I wrote it with LLBLGen 1.x, so a couple of routines still use that old code, and it works so why rewrite it with other code), though I moved a lot to llblgenpro code over the years.

There are 2 kinds of procedures: CRUD procs and data processing procs. The first group is IMHO not that useful, the latter can be really useful, because it can (but doesn't have to) be more efficient, as it keeps all data in the server, so you save the roundtripping of the data to the client.

That said, I do realize that it does make sense to stop banging one's head against a wall. Whether it is actually using SPs or just trying to convince kool-aid drinkers to stop using SPs, it is clear that banging one's head against a wall yeilds nothing more than a bloody forehead. Ah well. C'est la vie.

True. Of course there are lurkers who will read the thread and think "hmm, I didnt know that", however there are already so many versions of this same discussion that I think doing it all over again is redundant and a waste of time.

I was pro proc myself in 2002, I wrote almost all the app's BL in proc code as well most of the time. LLBLGen Pro also started as a project to make a tool which has entity classes and a stored procedure designer where you could design your procedure in meta-data and generate the proc in the native sql of your database's choice.

After 3 months of development I realized... this isn't going to work: the proc designer would be non-productive (it's hard to beat code typing with visual stuff) and it also would be inflexible, as for each context the entity was used in, you had to design up front the proc to use in that context. The typical problem with procs which is often denied by pro-proc fanatics (basicly because they don't have to write that code ). This core reason was my turning point: it never would be productive to design all those procs up front as you don't know all the contexts in which you need data up front. You thus had to go into the designer alot, change procs, add new ones etc. which would be cumbersome because changing a proc would break code which uses the proc as well and adding procs leads to a gazillion of procs no-one knows if these are actually used or not.

So I messed with it for another month but then had to decide what to do: quit, drop the stuff I had and start over or continue with something that would never work. I decided to drop the proc stuff and start over.

You can still see some leftovers in the designer: the catalog explorer detail screens. Those were important in the very early versions, where you could select a set of tables and rightclick them and then select which procs you wanted to create for these tables. This might still sound like a 'good idea', but that's only 'geek-talk', as it's not a good idea: it's what MS would release, but which wouldn't make you more productive. (hint: you can better write a set of templates, select the entities to participate and generate all the procs in one go).

This was a hard lesson for us: we funded everything ourselves, which means that I lost roughly 3 months of hard work because of this. This is also a reason why I stopped participating in these discussions: some corporate DBA tries to tell me what's better based on false arguments while he can go home at 5 PM and doesn't have to worry about anything, though I've learned the hard way what he claims isn't true. (disclaimer: I've nothing against corporate or any other DBA, just against the attitude that they run the show and decide what a developer has to do).

BTW, if I might suggest/request-- please make sure your great posts are permanent links somewhere. I point people to the current links a lot and, if the links ever go dead so will I, and I am sure that I am not the only one.

Well, the blogpost will be there, and the thread on theserverside will be there . But actually, I don't want to be remembered as the guy from 'stored procedures are bad, m'kay?', or as an anchorman from the anti-proc-movement.

About the CLR stuff inside sqlserver 2005: Once I talked to an Oracle DB developer (a developer who worked on Oracle's db engine). He said: they added Java support to the engine because some key customers asked for it. However it turns out, not a lot of people use it.

That was a bit of a surprise for me at first, but thinking about it, it makes sense. Working with data is set oriented. Java, C#, VB.NET etc. aren't set oriented languages, they're OO languages and execute code sequentially. SQL is set oriented. The statements in SQL offer you to write large set processing code with a few statements (relatively), which otherwise would take a lot of lines with forloops/cursors and other goo in C#.

So does it make sense to write SQL-esk statements in C#? No, it doesnt. It only makes sense in the areas where you'd need processing inside the query and you have to fall back on SQL for that. You can now do that in a function written in C# which can/is more efficient as C# is compiled and SQL is interpreted.

THough what I find stupid is that in SqlServer 2005, the assemblies you create aren't first-class citizens of the database engine. Maintenance of clr code is tedious. I'm not so sure a lot of people will use C# code inside the DB because of this.

Otis wrote:

...Well, the blogpost will be there, and the thread on theserverside will be there . But actually, I don't want to be remembered as the guy from 'stored procedures are bad, m'kay?', or as an anchorman from the anti-proc-movement...

Regarding how you will be remembered...

Well, I am sorry to have to break it to you, but someone should-- fame is derived/earned, not chosen. But, we DO choose our own words and actions; so, the matter is not deterministic. (I am talking about "real" fame here; not that Hollywood imitation-fame.)

:-)

For you, you deserve it and should not be ashamed of it. You made some significant, vaild (albeit controversial) points. That's what it takes to be a catalyst.

Also, note that in not a few trenches, that post is referenced and accepted as de facto evidence. Tested and re-tested, yes. But, still standing true.

Anyway, I do take your meaning and understand. No one (certainly not me) is saying "SPs are evil". But, let me put it this way-- in many cases they are a far less than optimal solution.

Regarding .NET language interation in SQL Server 2005, I follow what you are saying. I think that I want to guess that such integration will (eventually) be quite prevalent/ common/ desired/ recommended-- but, that is just a wild guess (and a wish).

Stay in touch.

Thank you.

--Mark Kamoski

Thanks for your kind words

Btw, the stored procs are bad m'kay blogpost is now viewed 129926 times. ack