Comment On Stored Procedures, The Porn Guy, and Non-returnable Email

Stored Procedures (from Liz) There was an article posted a little while back (Does Not Compute) that featured confusion of fairly-common tech-isms like Cookies or the Recycle Bin. It was fun, but not terribly surprising considering that everyone is not technically inclined. [expand full text]
« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6Next »

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 15:40 • by Nagesh Kukunoor (unregistered)
336063 in reply to 336058
boog:
hoodaticus:
Who uses only one or the other anyway?
Exactly.

I love how so many people on this site take an all-or-nothing approach. They either use stored procedures for any access to the database whatsoever or they ditch them completely.

Stored procedures are not for everything. If you're using them to reinvent standard RDBMS features, you're doing it wrong. Stored procedures are subroutines, plain and simple: encapsulated code, each performing a single, specific task. So if you have logic that appears in more than one place and it's not in a subroutine, you're doing it wrong too.


In our multi-layered application, we rely on Hibernate to do all CRUD work. The business logic is kept in a remote service hosted on a Unix box. The front-end is browser based. I personally am not sold on stored procedures being useful. This approach allows us to change tiers or layers as we want. The DB is simply a data source only.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 15:55 • by luis.espinal
336068 in reply to 336042
Jaime:
luis.espinal:

Indeed. Some people fail to grasp what Turing Completeness actually means. Barring an external failure on the back-end or a badly made nested relationship, a pure SQL SELECT/INSERT/UPDATE/DELETE statement operates on a finite set of rows. It is a (somewhat poor) implementation of an algebra on finite sets of tuples. It is a language for writing deciders, programs that always halt solely on their finite input. SQL can never have the same expressive power in a stored procedure language like PL/SQL (based on Ada) or T-SQL. No way, no how.


The only way this statement makes sense is if you believe that ad-hoc SQL has no control statements. That would be incorrect. Both T-SQL and PL/SQL can be run outside of procedures.


I know that T-SQL and PL/SQL can run outside of procedures. I've never done it with T-SQL, but I've done it with PL/SQL. It's an old trick of the trade, with its valid uses (and many abuses).

That does not make them SQL. SQL is SQL, a declarative language for writing deciders on finite input and with limited control structures for transactions and data definitions.

The programming constructs, including control structures, found on T-SQL are not SQL. Furthermore, an Ada-like, module capable language like PL-SQL cannot qualify as SQL, either. How about a store procedure written in Java? Would that be magically be SQL just because?

That you can piggy-bag a vendor-specific stored procedure written in a Turing complete language like PL/SQL through a SQL statement, that does not make the whole thing SQL, either.

A database engine can receive an string encoding either a SQL statement, or a program construct written in a vendor's specific stored procedure language, or a combination of both (a combination ruled by a SQL standard from trampolining into a stored procedure call AND vendor specific syntax for doing so). And it can execute it if the construct if valid. But that doesn't turn whatever you put into that string into SQL.

SQL is not defined as a capability to send a string to a possibly remote relational database engine and have it execute there.

Let me give you an example: This would be akin to me pasing control to a a library written in C from Java via JNI and claim that the library itself is Java. By virtue of it be written in C, I can do things within the library that are impossible with Java. And if I don't preserve certain invariants, I can break the calling Java program. This is an example of crossing domain boundaries - from one trusted domain into an untrusted one, hoping that the untrusted one preserve certain invariants.

Likewise, I can do things within a stored procedure that makes it impossible for a calling SQL statement to halt, to act as a decider. Again, crossing domain boundaries, from a declarative, set-oriented one (SQL) into one that is vendor specific and that has never been fully defined in SQL (because it is pretty much impractical if not impossible.)


Now, if you are using the term "SQL" to refer to

1. a vendor's implementation of SQL IN COMBINATION TO
2. vendor's specific stored procedural programming languages IN COMBINATION TO
3. the ability to declare an ad-hoc string containing a SQL statement trampolining into vendor specific procedural program language constructs IN COMBINATION TO
4. the ability to send that ad-hoc string from a client to a database engine for execution

then

by YOUR (really, yours) definition of "SQL" you would be right in saying you can use SQL to implement anything that can be implemented in a stored procedure.

The problem is that it is not the standard definition of SQL; that is not what people universally refer to as "SQL", either academically or in the real world. I've never seen anyone (in academia or industry) referring to vendor-specific stored procedure program language constructs when referring to (or as part of) SQL.

If that's how you define and see SQL and that works well for you, that's great. Just understand then that people in general will disagree with you (or misunderstand you) because you are using "oranges" to describe "apples and oranges" (and the fruit salad made thereof) when everyone else is using "apples" and "oranges" (and "orange and apple fruit salad") in a manner that is commonly understood.

Peace.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 16:08 • by Jaime
336069 in reply to 336068
luis.espinal:
Jaime:
luis.espinal:

Indeed. Some people fail to grasp what Turing Completeness actually means. Barring an external failure on the back-end or a badly made nested relationship, a pure SQL SELECT/INSERT/UPDATE/DELETE statement operates on a finite set of rows. It is a (somewhat poor) implementation of an algebra on finite sets of tuples. It is a language for writing deciders, programs that always halt solely on their finite input. SQL can never have the same expressive power in a stored procedure language like PL/SQL (based on Ada) or T-SQL. No way, no how.


The only way this statement makes sense is if you believe that ad-hoc SQL has no control statements. That would be incorrect. Both T-SQL and PL/SQL can be run outside of procedures.


I know that T-SQL and PL/SQL can run outside of procedures. I've never done it with T-SQL, but I've done it with PL/SQL. It's an old trick of the trade, with its valid uses (and many abuses).

That does not make them SQL. SQL is SQL, a declarative language for writing deciders on finite input and with limited control structures for transactions and data definitions.

The programming constructs, including control structures, found on T-SQL are not SQL. Furthermore, an Ada-like, module capable language like PL-SQL can qualify as SQL, either. How about a store procedure written in Java? Would that be magically be SQL just because?

That you can piggy-bag a vendor-specific stored procedure written in a Turing complete language like PL/SQL through a SQL statement, that does not make the whole thing SQL, either.

A database engine can receive an string encoding either a SQL statement, or a program construct written in a vendor's specific stored procedure language, or a combination of both (a combination ruled by a SQL standard from trampolining into a stored procedure call AND vendor specific syntax for doing so). And it can execute it if the construct if valid. But that doesn't turn whatever you put into that string into SQL.

Let me give you an example: This would be akin to me pasing control to a a library written in C from Java via JNI and claim that the library itself is Java. By virtue of it be written in C, I can do things within the library that are impossible with Java. And if I don't preserve certain invariants, I can break the calling Java program. This is an example of crossing domain boundaries - from one trusted domain into an untrusted one, hoping that the untrusted one preserve certain invariants.

Likewise, I can do things within a stored procedure that makes it impossible for a calling SQL statement to halt, to act as a decider. Again, crossing domain boundaries, from a declarative, set-oriented one (SQL) into one that is vendor specific and that has never been fully defined in SQL (because it is pretty much impractical if not impossible.)


Now, if you are using the term "SQL" to refer to

1. a vendor's implementation of SQL IN COMBINATION TO
2. vendor's specific stored procedural programming languages IN COMBINATION TO
3. the ability to declare an ad-hoc string containing a SQL statement trampolining into vendor specific procedural program language constructs IN COMBINATION TO
4. the ability to send that ad-hoc string from a client to a database engine for execution

then

by YOUR (really, yours) definition of "SQL" you would be right in saying you can use SQL to implement anything that can be implemented in a stored procedure.

The problem is that it is not the standard definition of SQL; that is not what people universally refer to as "SQL", either academically or in the real world. I've never seen anyone (in academia or industry) referring to vendor-specific stored procedure program language constructs when referring to (or as part of) SQL.

If that's how you define and see SQL and that works well for you, that's great. Just understand then that people in general will disagree with you (or misunderstand you) because you are using "oranges" to describe "apples and oranges" (and the fruit salad made thereof) when everyone else is using "apples" and "oranges" (and "orange and apple fruit salad") in a manner that is commonly understood.

Peace.
So this entire rant was about confusion of terminology when the context made it very clear that ad-hoc SQL was being used to mean "any blob of text sent to the database server" instead of "blob of text that meets the ANSI SQL specification"? Did you purposely ignore the context in order to launch the rant or did you miss it entirely?

Regardless of my loose use of the term, my posts would have made no sense with the strict definition of SQL. Wouldn't it have been easier to start with a criticism of the terminology rather than attack the conclusions?

This is the problem with any discussion of stored procedures. Those who believe that stored procedures are the only solution will do anything to avoid listening to the truth that they are only one of several technologies to accomplish the goal. I can build an application that is fast, manageable and secure both with or without stored procedures. I use them when they make sense and ignore them when they are counter-productive. There is absolutely no goal that can only be attained throug a stored procedure. I take all of these attempts to derail the conversation as an admission of defeat.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 16:13 • by boog
336070 in reply to 336063
Nagesh Kukunoor:
In our multi-layered application, we rely on Hibernate to do all CRUD work. The business logic is kept in a remote service hosted on a Unix box. The front-end is browser based. I personally am not sold on stored procedures being useful. This approach allows us to change tiers or layers as we want. The DB is simply a data source only.

If using the DB as a data source only and maintaining all data integrity on another layer works for you, that's fine. You may think your setup doesn't benefit from using stored procedures, but others' setups do.

So you advised people to ditch stored procedures completely. You provided no evidence that your solution is better; you didn't even establish any form of measurement by which to compare the two solutions. That's why so many people jumped down your throat about it. "My experience tells me X is better than Y" is not a convincing argument. It is flamebait.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 16:18 • by Jaime
336071 in reply to 336055
boog:
Jaime:
boog:
Jaime:
Who ever said that ad-hoc SQL was limited to queries? Name one SQL statement (other than RETURN) that can be used in a stored procedure, but not in an ad-hoc batch.
You do realize that stored procedures typically have their own procedural language, don't you? There is a lot more you can do in a stored procedure than just run SQL statements.
I do. Do you realize that the entire procedural language is available outside of stored procedures in both MSSQL and Oracle?

I do. So why would you need to tweak it? It was that comment that originally threw me off. It implies that the code would have to be different enough to need tweaking.

Next time, just make your claim clear and concise: Since the same code can be executed inside or outside a stored procedure, stored procedures are not inherently faster. Period. Not once did I disagree with you on that point.
So, if you agree with me, then why did you say "You do realize that stored procedures typically have their own procedural language, don't you?". Saying that stored procedures have their "own" procedural language implies that you are unaware that those language elements can be used outside of stored procedures, which would imply that you think that things like loops and conditionals are limited to stored procedures. That amounts to disagreeing with me since without loops and conditionals, ad-hoc batches would be at a significant disadvantage to stored procedures.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 16:28 • by Jaime
336073 in reply to 336070
boog:
Nagesh Kukunoor:
In our multi-layered application, we rely on Hibernate to do all CRUD work. The business logic is kept in a remote service hosted on a Unix box. The front-end is browser based. I personally am not sold on stored procedures being useful. This approach allows us to change tiers or layers as we want. The DB is simply a data source only.

If using the DB as a data source only and maintaining all data integrity on another layer works for you, that's fine. You may think your setup doesn't benefit from using stored procedures, but others' setups do.

So you advised people to ditch stored procedures completely. You provided no evidence that your solution is better; you didn't even establish any form of measurement by which to compare the two solutions. That's why so many people jumped down your throat about it. "My experience tells me X is better than Y" is not a convincing argument. It is flamebait.
People jumped down his throat because stored procedures are a religious topic in which many people hold beliefs that haven't been true for twenty years. Even if Nagesh had presented complete and reasonable arguments, he would have been treated the same.

BTW, I think he's wrong about a DB only being a data source. I also think that writing vendor agnostic SQL virtually gaurantees less than stellar results. But I am more than happy to use his statements as a launching point to speak to those who think stored procedure are the only way to go. My biggest argument for moving as much logic as possible out of the physical data tier is that databases are hard to scale while middle tier servers are easy to scale.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 16:39 • by boog
336075 in reply to 336069
Jaime:
So this entire rant was about confusion of terminology when the context made it very clear that ad-hoc SQL was being used to mean "any blob of text sent to the database server" instead of "blob of text that meets the ANSI SQL specification"?
I wouldn't say the context made it very clear; several people (myself included) assumed you were referring to DML statements, and not the procedural language.

Jaime:
This is the problem with any discussion of stored procedures. Those who believe that stored procedures are the only solution will do anything to avoid listening to the truth blah blah soapbox blah...
Sorry, you're breaking up. All I hear is a whining sound.

Before I lost you it sounded like you were saying your opponents' collective belief is that stored procedures are the only solution. But it seems to me that few of the people with whom you've actually been arguing have made that case. In fact, luis.espinal and I have been arguing with you this whole time simply because of a misunderstanding over your terminology.

I'm just saying...

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 16:45 • by boog
336079 in reply to 336071
Jaime:
So, if you agree with me, then why did you say "You do realize that stored procedures typically have their own procedural language, don't you?". Saying that stored procedures have their "own" procedural language implies that you are unaware that those language elements can be used outside of stored procedures, which would imply that you think that things like loops and conditionals are limited to stored procedures. That amounts to disagreeing with me since without loops and conditionals, ad-hoc batches would be at a significant disadvantage to stored procedures.

Fair enough; poor choice of working on my part:
boog:
You do realize that stored procedures typically have their own use a procedural language, don't you?
Better?

Addendum (2011-01-28 16:52):
*wording, not working

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 16:50 • by boog
336081 in reply to 336073
Jaime:
I am more than happy to use his statements as a launching point to speak to those who think stored procedure are the only way to go.

Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 17:43 • by luis.espinal
336083 in reply to 336069
Jaime:
So this entire rant was about confusion of terminology when the context made it very clear that ad-hoc SQL was being used to mean "any blob of text sent to the database server" instead of "blob of text that meets the ANSI SQL specification"?


Clear to you maybe.

At the end of the day "ad-hoc" means something you can make on the fly. SQL means... well, SQL. And "ad-hoc SQL" means... well, a SQL statement created on the fly. And nobody, nobody uses SQL to mean either ANSI SQL (or vendor specific SQL) in addition and combination to vendor specific store procedure language constructs.


The only thing unclear until now was the origin of your atypical usage of the term "SQL" and "ad-hoc SQL" and the confusion about whether that atypical usage is done 1) for a purpose unbeknown to us, 2) a new technical trend, 3) because of a posting accident (we all do that), or 4) you don't know what you are talking about.

Even if it is clear that you are using SQL to mean "anything" including the kitchen sink, it is not clear why you (or anyone) would use such an obtuse definition in lieu of a definition that has universally been used for effective communication of what SQL is for the last 4 decades.

That's why nobody understand what you are talking about, and why anybody (not just me) is ranting. It makes no sense and serves no useful or practical purpose beyond your need to redefine an industrial term to make your proposition satisfiable. That which you did is a WTF.


Jaime:
Did you purposely ignore the context in order to launch the rant or did you miss it entirely?


The existence of a context does not imply its validity or usefulness. An invalid or illogical context does not warrant attention. You don't like that response from readers of your post, then build logically valid contexts. That's akin to building an improbably edge case to attack the validity of a general purpose solution to a particular problem. Gee, you win, here have a cookie.

Jaime:
Regardless of my loose use of the term,


Not loose. Incorrect. Non-standard. Confusing. Without apparent useful communicating purpose.

Jaime:
my posts would have made no sense with the strict definition of SQL.


Not just strict, but standard and universally accepted in technical discussions, ergo correct.

Jaime:
Wouldn't it have been easier to start with a criticism of the terminology



Gee, I thought I did (see below)


me:

That does not make them SQL. SQL is SQL, a declarative language for writing deciders on finite input and with limited control structures for transactions and data definitions.

....

The programming constructs, including control structures, found on T-SQL are not SQL. Furthermore, an Ada-like, module capable language like PL-SQL cannot qualify as SQL, either.

....

That you can piggy-bag a vendor-specific stored procedure written in a Turing complete language like PL/SQL through a SQL statement, that does not make the whole thing SQL, either.

....
A database engine can receive an string encoding either a SQL statement, or a program construct written in a vendor's specific stored procedure language, or a combination of both .... But that doesn't turn whatever you put into that string into SQL.



Jaime:
rather than attack the conclusions?


Attacking a conclusion is perfectly valid when the terminology is unclear (and in your case obtuse, non-standard... and wrong when it comes to standards.) Not to mention that I did criticize your terminology (see above.)

Jaime:
This is the problem with any discussion of stored procedures.


I'm sorry, but I never engaged in a discussion on the validity or invalidity of stored procedures as my original post was about ORMs making stored procedures obsolete and unnecessary. If I did engage in a discussion on the validity or invalidity of stored procedures point out the post where I did so.

As it is, the discussion between you and me is about your continuous and extensive usage of the term "SQL" and "ad-hoc SQL" in a manner distinct from what has been typically been used for the last four decades.

Jaime:
Those who believe that stored procedures are the only solution will do anything to avoid listening to the truth that they are only one of several technologies to accomplish the goal.


Nice strawman (possibly crossbred with an Ad hominen). I challenge you to quote me anywhere where I've said or stated that stored procedures are the only solution, in this thread or in any thread. I challenge you. If that statement is not being directed at me, then that statement is out of place with respect to the discussion we are having (your usage of the terms "SQL" and "ad-hoc SQL")... it would be again another statement out of context (or with an unclear/invalid/malicious/fallacious/superfluous context.)

Jaime:
I can build an application that is fast, manageable and secure both with or without stored procedures. I use them when they make sense and ignore them when they are counter-productive.


That's a fine statement, so fine it would have brought me to tears of joy had it been the topic of discussion. It is not.

It is a red herring. It is not what is being argued about, and it is not an argument that I've made any reference of (in pro or in con) in this specific discussion (or in this thread.)

Jaime:
There is absolutely no goal that can only be attained throug a stored procedure.


Fine statement. Fine red herring. Certainly a fine oxymoron, a redundant statement of the obvious. Point to where I've made any argument for or against that in this thread to you or anyone and you'll win a fine kindergarten shinny star sticky for being such a good boyscout.

Jaime:
I take all of these attempts to derail the conversation as an admission of defeat.


You have made a series of strawmen and red herrings thorough your reply, so to talk about derailments (and to weaselly imply that questions to the validity of your statements amount to derailments) feels a little bit like a black pot and a black kettle feverishly discussing about their shared ability to absorb all frequencies of light.

Perhaps you might feel like redefining the statements in my reply as a "derailment" in the same way you redefined "SQL" and "ad-hoc SQL" mistakenly (or purposely or ignorantly, only know).

And since the interpretation of such statements... I mean "derailments" can only be taken in a melodramatic way as an admission of defeat at your sole, solemn, imperious and infallible discretion and interpretation (however illogical that might be), who am I to argue against your glorious triumph?

You have teh winx0r by last man standing. Congratulations.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 18:02 • by NC (unregistered)
I actually worked in the Adult Entertainment Industry as a Web Developer for almost 3 years, it's on my resume. Every damn job i go to the people ask me "So i've never known anyone who worked in the porn industry." or "Tell me about the porn industry!!!". First time I went job hunting after that job, i got 9 offers out of 10 interviews I sent to.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 19:14 • by Jaime
336089 in reply to 336081
boog:
Jaime:
I am more than happy to use his statements as a launching point to speak to those who think stored procedure are the only way to go.

Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.
Most?

CnC
Dirge
BigJim
sql guy
M
will
Design Pattern

Not spam, not spam, not spam.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 19:28 • by Jaime
336090 in reply to 336083
luis.espinal:
...stuff...
Here is the problem with holding the term SQL as such a non-flexible term -- When on the client side, we have to submit text to the server. We have to call what we submit something. Ad-hoc stuff doesn't work. Ad-hoc T-SQL sounds stupid and is only accurate for Micosoft SQL Server. So we all call it ad-hoc SQL when we aren't calling a stored procedure. It's a common term that I hear used this way a lot and doesn't imply that the content is only SQL, but rather implies that the it is the text of batch that will be submitted to a database server. I'm sorry if that terminology threw you off, but it is real terminology. Jeff Atwood seems to agree with me. So does Frans Bouma. If you Google "ad-hoc SQL", most of the resulting pages that are specific enough to infer a definition don't limit it to DML.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 20:01 • by boog
336095 in reply to 336089
Jaime:
boog:
Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.
Most?

CnC
Dirge
BigJim
sql guy
M
will
Design Pattern

Not spam, not spam, not spam.

So... how many of those guys said that stored procedures are the only way to go? One? Maybe two?

Wait, what were you trying to prove with the above comment?

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 20:34 • by Jaime
336096 in reply to 336095
boog:
Jaime:
boog:
Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.
Most?

CnC
Dirge
BigJim
sql guy
M
will
Design Pattern

Not spam, not spam, not spam.

So... how many of those guys said that stored procedures are the only way to go? One? Maybe two?

Wait, what were you trying to prove with the above comment?
So, let's stop talking to each other and go get 'em. As long as one person still believes any of the following I won't rest:

1. Stored Procedures are generally faster.
2. Stored Procedures protect from SQL Injection.
3. Stored Procedures are the only viable way to organize all database access for an application.
4. Stored Procedures make it easier to performance tune an application.
5. Stored Procedures are the only way (or even the best way) to make it so database access bugs can be addressed without re-deploying client code.
6. The security benefits of Stored Procedures can't be duplicated with less effort using a different technology.
7. Stored Procedures are easier to maintain than other sensible alternatives.

Every poster above seems to hold at least one of these beliefs.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 21:02 • by hoodaticus
336101 in reply to 336052
Esa-Pekka :
hoodaticus:

He can be forgiven for citing American law, since all the benighted, primitive barbarians outside this great nation basically sit around all day wallowing in their own feces.
Oikeasti, joka ei ole tehnyt jotain tällaista?
Or, I suppose, speaking in Feces.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 21:13 • by hoodaticus
336102 in reply to 336058
boog:
Stored procedures are subroutines, plain and simple: encapsulated code, each performing a single, specific task. So if you have logic that appears in more than one place and it's not in a subroutine, you're doing it wrong too.
I was going to mention that, but then I thought about my codebase and realized that I virtually never have the same type of query being run by more than one class, and when I do, it's in the queue for refactoring because those classes are all doing the same thing and should be merged.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 21:36 • by hoodaticus
336103 in reply to 336090
Jaime:
luis.espinal:
...stuff...
Here is the problem with holding the term SQL as such a non-flexible term -- When on the client side, we have to submit text to the server. We have to call what we submit something. Ad-hoc stuff doesn't work. Ad-hoc T-SQL sounds stupid and is only accurate for Micosoft SQL Server. So we all call it ad-hoc SQL when we aren't calling a stored procedure. It's a common term that I hear used this way a lot and doesn't imply that the content is only SQL, but rather implies that the it is the text of batch that will be submitted to a database server. I'm sorry if that terminology threw you off, but it is real terminology. Jeff Atwood seems to agree with me. So does Frans Bouma. If you Google "ad-hoc SQL", most of the resulting pages that are specific enough to infer a definition don't limit it to DML.
Explaining the obvious gets tiresome at times, doesn't it?

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 22:26 • by luis.espinal
336105 in reply to 336096
Jaime:
boog:
Jaime:
boog:
Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.
Most?

CnC
Dirge
BigJim
sql guy
M
will
Design Pattern

Not spam, not spam, not spam.

So... how many of those guys said that stored procedures are the only way to go? One? Maybe two?

Wait, what were you trying to prove with the above comment?
So, let's stop talking to each other and go get 'em. As long as one person still believes any of the following I won't rest:

1. Stored Procedures are generally faster.
2. Stored Procedures protect from SQL Injection.
3. Stored Procedures are the only viable way to organize all database access for an application.
4. Stored Procedures make it easier to performance tune an application.
5. Stored Procedures are the only way (or even the best way) to make it so database access bugs can be addressed without re-deploying client code.
6. The security benefits of Stored Procedures can't be duplicated with less effort using a different technology.
7. Stored Procedures are easier to maintain than other sensible alternatives.

Every poster above seems to hold at least one of these beliefs.


For the second time in a row I'm asking you to quote the post I've made where I've stated any the beliefs that you just listed. I'm waiting.

You listed a bunch of people who made those arguments. Having said you also mentioned a same veiled accusation when you replied to me (in the second to the last of my posts that you replied to.)

Jaime:
This is the problem with any discussion of stored procedures.

Those who believe that stored procedures are the only solution will do anything to avoid listening to the truth that they are only one of several technologies to accomplish the goal.



That was in your reply that you address to me, as a reply to a post I addressed to you. Explain to me what post I've made that states that which you are pointing at. I'm waiting.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 22:46 • by luis.espinal
336108 in reply to 336103
hoodaticus:
Jaime:
luis.espinal:
...stuff...
Here is the problem with holding the term SQL as such a non-flexible term -- When on the client side, we have to submit text to the server. We have to call what we submit something. Ad-hoc stuff doesn't work. Ad-hoc T-SQL sounds stupid and is only accurate for Micosoft SQL Server. So we all call it ad-hoc SQL when we aren't calling a stored procedure. It's a common term that I hear used this way a lot and doesn't imply that the content is only SQL, but rather implies that the it is the text of batch that will be submitted to a database server. I'm sorry if that terminology threw you off, but it is real terminology. Jeff Atwood seems to agree with me. So does Frans Bouma. If you Google "ad-hoc SQL", most of the resulting pages that are specific enough to infer a definition don't limit it to DML.
Explaining the obvious gets tiresome at times, doesn't it?


If you call that explaining.

As to Jamie:

The link you point to Atwoods' post only discusses a balanced between ad-hoc SQL (which is not defined anywhere as you do) and stored procedures.

Quote the text in Atwood's post where he defines ad-hoc sql as you did.

Where does Bouma in his post defines ad-hoc sql as you did? All he does in his post is provide the same balanced view of using ad-hoc SQL (SQL that you can execute verbatin off to a remote database engine) vs stored procedures. No arguments there.

I've not argued anything against Atwood's or Bouma's POV on the subject. It is not an argument I've made, nor one that is in contention.

Doing a google on ad-hoc sql and linking and misquoting programming personalities is simply an appeal to authority (and a poorly made attempt at that.) Again, the ability to piggy-bag vendor-specific constructs that are not part of SQL in any liberal interpretation of the dialect does not make it SQL, the declarative language that goes by that name.

You did not make you context clear at first. And you stuck by it as the argument went by without stopping until many posts later to explicitly define it. Only later you introduced the term ad-hoc SQL. And ad-hoc SQL in the general sense of the world, as applied to all RDBMS, not just MSSQL simply means what I told you before: SQL run ad-hoc.

And people jumped on it because we don't know whether you use that liberal interpretation of what SQL is (the term you previously said was as powerful as any stored procedure language) because you made a mistake in typing or don't know what you are talking about.

Nothing on that is predicated in your false, red-herring argument that we (whoever 'we' that is) believe stored procedures are the alpha and omega of it all.

Couple that with your infantile "I take all of these attempts to derail the conversation as an admission of defeat" (on an argument of SP being supreme, something I never made), it paints a very silly picture.

If you still don't get that, that's on you, not me or anybody else.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 22:50 • by Jaime
336109 in reply to 336105
luis.espinal:
Jaime:
boog:
Jaime:
boog:
Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.
Most?

CnC
Dirge
BigJim
sql guy
M
will
Design Pattern

Not spam, not spam, not spam.

So... how many of those guys said that stored procedures are the only way to go? One? Maybe two?

Wait, what were you trying to prove with the above comment?
So, let's stop talking to each other and go get 'em. As long as one person still believes any of the following I won't rest:

1. Stored Procedures are generally faster.
2. Stored Procedures protect from SQL Injection.
3. Stored Procedures are the only viable way to organize all database access for an application.
4. Stored Procedures make it easier to performance tune an application.
5. Stored Procedures are the only way (or even the best way) to make it so database access bugs can be addressed without re-deploying client code.
6. The security benefits of Stored Procedures can't be duplicated with less effort using a different technology.
7. Stored Procedures are easier to maintain than other sensible alternatives.

Every poster above seems to hold at least one of these beliefs.


For the second time in a row I'm asking you to quote the post I've made where I've stated any the beliefs that you just listed. I'm waiting.

You listed a bunch of people who made those arguments. Having said you also mentioned a same veiled accusation when you replied to me (in the second to the last of my posts that you replied to.)

Jaime:
This is the problem with any discussion of stored procedures.

Those who believe that stored procedures are the only solution will do anything to avoid listening to the truth that they are only one of several technologies to accomplish the goal.



That was in your reply that you address to me, as a reply to a post I addressed to you. Explain to me what post I've made that states that which you are pointing at. I'm waiting.
Is your name in that list? If I meant you, I would have listed you. The "Those who" in the response to you was addressed to the seven list above. If I meant you, I wouldn't have said "Those who". The only problem I have with you is that our exchanges have roots in differences that are trivial. I would rather help someone discover that the guy at work was wrong when he told them that SPs protect against SQL Injection than argue with you about whether SQL means ANSI SQL in the strict sense, or if it is a generic technology, of which T-SQL is a vendor specific implementation.

You simply sidelined the conversation into SQL vs. T-SQL, a sideline based on a simple misunderstanding. Let it go. BTW, if you don't hold any of those seven beliefs, then you have the same opinion as me. Why call me out, but not call out any of those who have actual substantially differing opinions?

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-28 23:06 • by Jaime
336110 in reply to 336108
luis.espinal:
hoodaticus:
Jaime:
luis.espinal:
...stuff...
Here is the problem with holding the term SQL as such a non-flexible term -- When on the client side, we have to submit text to the server. We have to call what we submit something. Ad-hoc stuff doesn't work. Ad-hoc T-SQL sounds stupid and is only accurate for Micosoft SQL Server. So we all call it ad-hoc SQL when we aren't calling a stored procedure. It's a common term that I hear used this way a lot and doesn't imply that the content is only SQL, but rather implies that the it is the text of batch that will be submitted to a database server. I'm sorry if that terminology threw you off, but it is real terminology. Jeff Atwood seems to agree with me. So does Frans Bouma. If you Google "ad-hoc SQL", most of the resulting pages that are specific enough to infer a definition don't limit it to DML.
Explaining the obvious gets tiresome at times, doesn't it?


If you call that explaining.

As to Jamie:

The link you point to Atwoods' post only discusses a balanced between ad-hoc SQL (which is not defined anywhere as you do) and stored procedures.

Quote the text in Atwood's post where he defines ad-hoc sql as you did.

Where does Bouma in his post defines ad-hoc sql as you did? All he does in his post is provide the same balanced view of using ad-hoc SQL (SQL that you can execute verbatin off to a remote database engine) vs stored procedures. No arguments there.

I've not argued anything against Atwood's or Bouma's POV on the subject. It is not an argument I've made, nor one that is in contention.

Doing a google on ad-hoc sql and linking and misquoting programming personalities is simply an appeal to authority (and a poorly made attempt at that.) Again, the ability to piggy-bag vendor-specific constructs that are not part of SQL in any liberal interpretation of the dialect does not make it SQL, the declarative language that goes by that name.

You did not make you context clear at first. And you stuck by it as the argument went by without stopping until many posts later to explicitly define it. Only later you introduced the term ad-hoc SQL. And ad-hoc SQL in the general sense of the world, as applied to all RDBMS, not just MSSQL simply means what I told you before: SQL run ad-hoc.

And people jumped on it because we don't know whether you use that liberal interpretation of what SQL is (the term you previously said was as powerful as any stored procedure language) because you made a mistake in typing or don't know what you are talking about.

Nothing on that is predicated in your false, red-herring argument that we (whoever 'we' that is) believe stored procedures are the alpha and omega of it all.

Couple that with your infantile "I take all of these attempts to derail the conversation as an admission of defeat" (on an argument of SP being supreme, something I never made), it paints a very silly picture.

If you still don't get that, that's on you, not me or anybody else.
Read the comments in both linked pages. In both cases, the concept of submitting a batch of statements comes up and neither author rejects the concept as not being ad-hoc SQL.

Also, stop getting offended that my rants were in responses to your posts. We are conversing in public, not private. It is tiresome to break each thought into a separate response post, so I sometimes tack these little rants onto the end of specific responses. I do use language to attempt to clarify the audience, such as "Those who". In order to feel that comment is directed at you, you must first self-select as one of "Those who". Are you?

I have had the stored procedure discussion many times and it always seems like I'm aiming at a moving target. That specific statement was an attempt to get the discussion back on focus. This whole SQL vs. T-SQL thing is like focussing on the bacteria on a flea on an angry lion. Drop it. We didn't connect, but it is resolved now.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-29 03:23 • by Sudo (unregistered)
336114 in reply to 335753
Paco:
It's sad that people are such prudes. If it were me interviewing that guy would have moved to the top of the list.

Why? Because his experience as a photographer in the adult entertainment industry shows he is obviously a good programmer?

I admit, it's crazy to turn the guy down on that basis, but it would be just as crazy to hire him because of it.

I think most of us would rather work with competent bores than incompetent "characters"...

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-29 08:22 • by SQLDave
336119 in reply to 335822
The Corrector:
goob:
Ok. Let's review:

1) Len gets job and goes to college.
2) Len interviews for new job, putting past job on resume.
3) Company blacklists Len based purely on the answer to a seemingly-benign interview question.
3.1) Len gets sex change operation, becoming Liz.
4) Liz gets a new job.
5) Liz interviews candidate.
6) Liz blacklists candidate based purely on the answer to a seemingly-benign interview question.

FTFY


FTFY

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-29 08:32 • by SQLDave
336120 in reply to 335889
Bub:
Should I keep my tale of college summers spent manually extracting bull semen quiet?

Or just bring a bottle of hand-sanitizer along to offer them?

Was "Bull" the nickname of your roommate?

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-29 09:54 • by Iain Collins (unregistered)
336123 in reply to 335789
dolor:

Your the reason why I have to work with incompitant numbskulls. Try boning up on your interview skills.

Karma's a bitch.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-29 20:24 • by Ilya Ehrenburg
336133 in reply to 336089
Jaime:
boog:
Jaime:
I am more than happy to use his statements as a launching point to speak to those who think stored procedure are the only way to go.

Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.
Most?

CnC
Dirge
BigJim
sql guy
M
will
Design Pattern

Not spam, not spam, not spam.

Umm, Jaime, please re-read the posts you linked. Most were pretty explicit that stored procedures are one way among others; superior for some tasks but not all. And will was even pretty critical of stored procedures.

Altogether, in this altercation, you came off as the guy with the religious view that stored procedures are evil (yes, I know, you never said that, it's just the impression your tone generated) and the reality-distortion field.

Calm down a little and when somebody calls you out on your wrong terminology, say "Oops, my bad" and don't claim to be the winner when they finally make it clear that their issue with you is terminological.

Peace.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-30 02:56 • by Farkisaurus (unregistered)
336135 in reply to 335813
stibbons:
together with a non-returnable photograph to careers@-------.com


So what happens if your email bounces?


Well, hopefully, the recipient will have arranged e-mail forwarding with the Post Office so that doesn't happen.

CAPTCHA: validus: the sword used by a Roman validator.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-30 04:16 • by Sylver (unregistered)
336137 in reply to 335763
dpm:
While I've never read up on actual discrimination laws, it sounds actionable to refuse to consider hiring someone because of a completely legal job in their past. That would be like me showing someone the door because he admitted to being "Barney" on television years ago. It _feels_ justified but it ain't ethical.


Bull. When recruiting, you are trying to find someone who will be an asset and who will get along fine with the rest of the team. A previous job in the pornographic industry would tend to indicate fairly low moral standards, and bringing this experience in a recruitment interview for a programmer position demonstrate a lack of common sense.

It's not relevant, and for *** sake's, if that was his most interesting job and his past programming jobs were boring to him, what is he doing applying for a job at a software company?

So, if low moral standards and lack of common sense are not valid grounds for rejecting a candidate, what are?

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-30 04:17 • by Farkisaurus (unregistered)
336138 in reply to 335891
Capt. Obvious:
trwtf:
Capt. Obvious:
OldPeter:
Well, it's fairly common to send photographs with a resume over here in Germany. Ok, that's normally a very formal, decent photo as for a passport, but some people also send more "nice" photos to present themselves in a hopefully positive way. This is a funny issue that in USA people are fearing discrimination by giving away their photo on this occasion, normally it's us here in Europe who have bigger fears of privacy violations, see all these StreetView and Facebook troubles recently.

Germany doesn't have a large group of visually distinctive people who are often discriminated against... at least not like the US does.



Unless, of course, you count the Turks.

At 3% of the population, I considered the Turks. But that's only 3%. It's not like the US with its substantial black and latino populations (12.5% and 10%). The US has a higher percentage Asian people (4.5%) and multiracial people than Germany does Turks. Hell, the US has more Native Americans than the Germany has Turks, in absolute numbers.

It's hard to talk about Germany's racial uniformity without skating the Godwin line. However, looking at France, it's only slightly more diverse (with a 5.25% of the population coming from Northern Africa and 1.75% from the rest of Africa... although only about 3% of France is black.) Turks only make up 0.7% of the French population.


That's because France was part of Germany when Germany was in the process of becoming more racially homogenous.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-30 08:49 • by Kuba
336142 in reply to 335829
boog:
[...]
Nagesh Kukunoor:
ORM has pretty much obviated the need to write a stored procedure. Only old FUDDY DUDDY's are writing them.
Well, I'm not an "old FUDDY DUDDY" and I write stored procedures. I guess I just proved you wrong. Generalizations suck, don't they?
Troll feeding day it must be.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-30 12:36 • by Luiz Felipe (unregistered)
336147 in reply to 335943
M:
I found stored procedures are very useful to getting performance back where Linq to SQL just dies on its arse.

One example is searching a set of products based on name, code, manufacturer etc. These are all "OR" operations, building it in linq was painful and took a few seconds to execute. Writing it in a stored procedure reduced this to 60 odd milliseconds!

So optimise where necessary I guess.



I solved this with linq by creating the tree manually using the linq.expression namespace.
Not so cool in contrast with using linq sintatic sugar, but it will do the job.
And the sql are generated only to filters set istead of superduper non-optimizable convoluted logic expression.



Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-30 14:46 • by Jaime
336149 in reply to 336133
Ilya Ehrenburg:
Jaime:
boog:
Jaime:
I am more than happy to use his statements as a launching point to speak to those who think stored procedure are the only way to go.

Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.
Most?

CnC
Dirge
BigJim
sql guy
M
will
Design Pattern

Not spam, not spam, not spam.

Umm, Jaime, please re-read the posts you linked. Most were pretty explicit that stored procedures are one way among others; superior for some tasks but not all. And will was even pretty critical of stored procedures.

Altogether, in this altercation, you came off as the guy with the religious view that stored procedures are evil (yes, I know, you never said that, it's just the impression your tone generated) and the reality-distortion field.

Calm down a little and when somebody calls you out on your wrong terminology, say "Oops, my bad" and don't claim to be the winner when they finally make it clear that their issue with you is terminological.

Peace.
I said "Oops, my bad" several times. However, my terminology errors didn't influence the outcome of the core discussion. All they did was lead into an aside that should have died quickly, but didn't. Also, if "ad-hoc SQL" isn't the proper terminology for a batch sent to a database server, then what is? It's not just my terminology that's imprecise, it's the entire industry. Heck, Microsoft trademarked the term "Microsoft SQL Server" for a product that wasn't ANSI SQL compliant for years after being named that.

will's error was that he thinks not using stored procedures creates extra round-trips. That is false. I'm certain that this means that he will recommend stored procedures are the best answer to a category of problems where it is simply not true. This is why his post is included in the list. I admit that only one or two on the list are completely on the "always use procs" bandwagon, but that's still one or two. The others are recommending procs in specific situations where they have zero benefit. I'll bet 100 people who read this thread work in shops where not using stored procedures will get code rejected.

Unfortunately, most of the bandwidth in this discussion has been spent on the definition of the acronym "SQL" (and in one case "falsifiable" vs "disproven"). I tried to get off it by admitting defeat several times, but it still won't go away. In truth, I don't really think I was wrong. SQL can be a specific term that is applied only to those topics addressed in ANSI SQL (SELECT/INSERT/UPDATE/DELETE), or it can mean database languages that have grown from it, like T-SQL or PL/SQL. It's hard to argue that Transact-SQL isn't a variant of SQL, given its name.

As for my tone, I'm actually hoping it will cause someone to take up the challenge. I've specifically called out at least seven people who hold pretty widely held beliefs. Unfortunately, all I got out of it was a bunch of people who fundamentally agree with me nit-picking terminology.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-30 15:55 • by Tore Sinding Bekkedal (unregistered)
336150 in reply to 335796
Ken B.:
too_many_usernames:
Henning Makholm:
too_many_usernames:
We are a primarily embedded systems company, so one question on the test is: "What is an atomic operation?"
Perhaps I'm ignorant, but what's the causal connection between embedded systems and atomic operations?
I suppose I should have qualified "embedded" as "real-time embedded controls." Data incoherency is *not* your friend!
But atoms can be broken down into smaller components, making the term a misnomer. Perhaps we should start using "quarkic operation"[tm] instead?


"Atomic" in this context doesn't refer to the physical concept of atoms, but rather the name itself, given due to the same attribute as was once believed physical atoms posessed; "atomos" is Greek for "cannot be cut".

Re: Stored Procedures

2011-01-30 20:21 • by ik (unregistered)
336152 in reply to 336038
Jaime:
???:
Jaime:
Drak:
Jaime:


Let's try a little challenge... you post a stored procedure and I'll post ad-hoc SQL that runs nearly identically. Spoiler: I'm simply going to remove the CREATE PROC from your post, turn the arguments into batch variables and re-post it.


CREATE PROCEDURE Something
@intI AS INTEGER
AS
BEGIN
SET @intI = @intI + 1
-- do someting actually useful here, can't be arsed for this example. Obviously just adding 1 to a variable doesn't require recursion, but some things seem to be more easily programmed recursively.
IF @intI < 600
EXEC Something @intI
END
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = @"

WHILE @intI < 600
BEGIN
SET @intI = @intI + 1
-- do someting actually useful here. Recursion is handy, but algorithms can often be written in a non-recursive manner.
-- When recursion is actually desired, use a temporary procedure or implement this one bit of functionality as a stored procedure.
END";

cmd.Parameters.AddWithValue("@intI", 6);
cmd.ExecuteNonQuery();



I'm not against stored procedures, I simply think they aren't the only solution, and the alternatives are often better.


That sure looks like:

Jaime: 0, Drak: 1
OK, then rather than going the sane direction and state that ad-hoc SQL can do it, but I think it's not the best idea in this case, I'll show that it can be done.

SqlCommand cmd = cn.CreateCommand();

cmd.CommandText = @"

CREATE PROC #payload(@i int OUTPUT)
AS
BEGIN
SET @i = @i + 1
END";

cmd.ExecuteNonQuery();

cmd.CommandText = @"

WHILE @intI < 600
BEGIN
EXEC #payload @intI OUTPUT
END";

cmd.Parameters.AddWithValue("@intI", 6);
cmd.ExecuteNonQuery();

cmd.CommandText = @"DROP PROC #payload";
cmd.ExecuteNonQuery();


So you are not against the "procedure" part, you are against the "stored" part. So everything's fine if you drop the procedure afterwards? Wow.

Re: Stored Procedures

2011-01-30 20:49 • by Jaime
336154 in reply to 336152
ik:
Jaime:
???:
Jaime:
Drak:
Jaime:


Let's try a little challenge... you post a stored procedure and I'll post ad-hoc SQL that runs nearly identically. Spoiler: I'm simply going to remove the CREATE PROC from your post, turn the arguments into batch variables and re-post it.


CREATE PROCEDURE Something
@intI AS INTEGER
AS
BEGIN
SET @intI = @intI + 1
-- do someting actually useful here, can't be arsed for this example. Obviously just adding 1 to a variable doesn't require recursion, but some things seem to be more easily programmed recursively.
IF @intI < 600
EXEC Something @intI
END
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = @"

WHILE @intI < 600
BEGIN
SET @intI = @intI + 1
-- do someting actually useful here. Recursion is handy, but algorithms can often be written in a non-recursive manner.
-- When recursion is actually desired, use a temporary procedure or implement this one bit of functionality as a stored procedure.
END";

cmd.Parameters.AddWithValue("@intI", 6);
cmd.ExecuteNonQuery();



I'm not against stored procedures, I simply think they aren't the only solution, and the alternatives are often better.


That sure looks like:

Jaime: 0, Drak: 1
OK, then rather than going the sane direction and state that ad-hoc SQL can do it, but I think it's not the best idea in this case, I'll show that it can be done.

SqlCommand cmd = cn.CreateCommand();

cmd.CommandText = @"

CREATE PROC #payload(@i int OUTPUT)
AS
BEGIN
SET @i = @i + 1
END";

cmd.ExecuteNonQuery();

cmd.CommandText = @"

WHILE @intI < 600
BEGIN
EXEC #payload @intI OUTPUT
END";

cmd.Parameters.AddWithValue("@intI", 6);
cmd.ExecuteNonQuery();

cmd.CommandText = @"DROP PROC #payload";
cmd.ExecuteNonQuery();


So you are not against the "procedure" part, you are against the "stored" part. So everything's fine if you drop the procedure afterwards? Wow.
So, this is the back-and-forth game now? First I reply that this is one of those edge cases where stored procedures actually makes sense and I decline to give a full working example. Somebody calls it in favor of the other guy. So, I submit a working example to prove that it can be done, and now it's called against me for being a bad idea!!! I already said that. How could you possible read that post and think I'm against using a stored procedure for this one?

This post was about "is it possible", not "is it a good idea".

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-31 07:57 • by chorlton (unregistered)
336158 in reply to 335767
too_many_usernames:
"What is an atomic operation?"

We have in our archives the hand-written response: "it involves the nucleus of atoms."


Similarly, when interviewing for a graduate trainee:

Question
"How would you describe polymorphism?"

Answer (in a broad Scots accent)
"Aw aye, that's when it kind ae like spreads oot aw ower the page like, eh?"

It does sound like he might actually have seen The Enterprise Dependency but we were looking for something a bit more technical.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-31 11:47 • by boog
336220 in reply to 336149
Jaime:
Unfortunately, most of the bandwidth in this discussion has been spent on the definition of the acronym "SQL" (and in one case "falsifiable" vs "disproven").

About the "falsifiable" vs. "disproven" discussion:

1. I was only taking a friendly jab at you (at the time) for implying that falsifiability was a bad thing. It was in no way previously an extensive debate as you seem to characterize it in the comment above.

2. Non-falsifiable statements are a detriment to intelligent discussion; implying that the difference between "falsifiable" and "disproven" is trivial or even "nit-picking" demonstrates a real misunderstanding of proper argument on your part, and should have indicated to me early on that I'd better back out of the discussion altogether.

I strongly urge you to read up on falsifiability and it's importance in scientific discussion and debate. I sincerely believe it could improve your arguing abilities, and maybe even help you to avoid some of these side arguments you frequently tend to be a part of.

As for the discussion on SQL, I have already backed out; it isn't going anywhere, and I don't have time for it any more.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-01-31 12:35 • by Nagesh
I started a discussion here

Can I not start a discussion?

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-01 05:48 • by sql guy (unregistered)
336300 in reply to 336096
Jaime:
So, let's stop talking to each other and go get 'em. As long as one person still believes any of the following I won't rest:

1. Stored Procedures are generally faster.

In general they are similar in speed, assuming identical SQL statements, although caching of ad-hoc queries might not always be as good as for procedures. In practice though, queries usually grow more complex over time and optimization is better done on the database side by database experts.

Another win for stored procedures is over things like views that have nested subqueries. Sometimes the internal optimiser can't pass a WHERE clause through to the inner query, so the view runs very slowly. With a stored procedure you can filter the inner query directly with a parameter which can massively speed things up.

I assume you aren't against views as well?

2. Stored Procedures protect from SQL Injection.

As long as you don't have dynamic SQL in your procedures they should be immune to SQL injection. But then so will ad-hoc SQL as long as you use parameters and don't make the newbie mistake of building SQL strings by simply concatenating variables. But as a DBA do you want to put the security of your database at the mercy of application developers who might not understand SQL security issues so well?

3. Stored Procedures are the only viable way to organize all database access for an application.

Clearly it can be done in other ways, but I would argue they aren't as good. Encapsulation of your database (to borrow an OO term) is always a good idea.

4. Stored Procedures make it easier to performance tune an application.

This is very true in my experience. If you have a fixed set of ways to access the database you only have to go through them and check performance and indexing is all set up OK for them. If you have ad-hoc SQL statements hitting the server that are possibly changing over time without your knowledge, the only way to do it is to put on a SQL trace recording every query hitting the server (maybe over a certain time threshold) and trawl through them looking for problems.

5. Stored Procedures are the only way (or even the best way) to make it so database access bugs can be addressed without re-deploying client code.

This seems obvious - how would you fix a data access bug that was in client code without re-deploying it?

6. The security benefits of Stored Procedures can't be duplicated with less effort using a different technology.

Very true in my experience. Simply give EXEC permissions to the application user on a fixed set of stored procedures. Much easier than setting various SELECT, UPDATE, DELETE, INSERT permissions on hundreds of individual tables. Especially if the database schema is evolving over time.

7. Stored Procedures are easier to maintain than other sensible alternatives.

Again, true in my experience. Especially if you have two teams working together, one for database and one for front-end code. Decoupling your data access and your presentation front-end is a good idea, with a fixed and clearly defined interface of stored procedures between the two.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-01 05:53 • by sql guy (unregistered)
336301 in reply to 336081
boog:
Jaime:
I am more than happy to use his statements as a launching point to speak to those who think stored procedure are the only way to go.

Again, it's funny that most of the people with whom you've been arguing this whole time don't think stored procedures are the only way to go.

I can't really think of a situation where you wouldn't want to use stored procedures for a front-end application.

Possibly when your application has some kind of caching going on and needs to know when tables have changed content? I'm sure there are ways round that problem though.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-01 20:45 • by badla (unregistered)
asdglkm al;kwe gakwle g
badla@mail.md

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-07 06:08 • by itsmo (unregistered)
336894 in reply to 335787
<>:
jger:
OldPeter:
Well, it's fairly common to send photographs with a resume over here in Germany.


It´s not about sending a non-returnable photograph at all but about sending a non-returnable photograph by email.

Yor from Germany? Good, I have a question about my German automobile. I drive a Prius, and I heard that diesels get better gas mileage. So I filled it with diesel and now it's making funny noises. Can you help?

FTFY

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-07 08:57 • by itsmo (unregistered)
336900 in reply to 336073
Jaime:
boog:
Nagesh Kukunoor:
In our multi-layered application, we rely on Hibernate to do all CRUD work. The business logic is kept in a remote service hosted on a Unix box. The front-end is browser based. I personally am not sold on stored procedures being useful. This approach allows us to change tiers or layers as we want. The DB is simply a data source only.

If using the DB as a data source only and maintaining all data integrity on another layer works for you, that's fine. You may think your setup doesn't benefit from using stored procedures, but others' setups do.

So you advised people to ditch stored procedures completely. You provided no evidence that your solution is better; you didn't even establish any form of measurement by which to compare the two solutions. That's why so many people jumped down your throat about it. "My experience tells me X is better than Y" is not a convincing argument. It is flamebait.
People jumped down his throat because stored procedures are a religious topic in which many people hold beliefs that haven't been true for twenty years. Even if Nagesh had presented complete and reasonable arguments, he would have been treated the same.

BTW, I think he's wrong about a DB only being a data source. I also think that writing vendor agnostic SQL virtually gaurantees less than stellar results. But I am more than happy to use his statements as a launching point to speak to those who think stored procedure are the only way to go. My biggest argument for moving as much logic as possible out of the physical data tier is that databases are hard to scale while middle tier servers are easy to scale.


Nagesh=Troll (do not feed)

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-09 14:45 • by NonReturnable (unregistered)
337381 in reply to 335756
pjt33:
backForMore:
Don't bother posting without a non-returnable photograph.

Does it have to be a photograph of me?


No. We will also accept a picture of a spider.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-15 04:36 • by something (unregistered)
337722 in reply to 335942
eric76:

They had narrowed it down to two of us. Since everyone there had a beard, I had a beard, and the other applicant didn't, I got the job.


And when they informed the other applicant, is that what they told her... ?

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-18 19:48 • by tgape
338410 in reply to 336085
NC:
I actually worked in the Adult Entertainment Industry as a Web Developer for almost 3 years, it's on my resume. Every damn job i go to the people ask me "So i've never known anyone who worked in the porn industry." or "Tell me about the porn industry!!!". First time I went job hunting after that job, i got 9 offers out of 10 interviews I sent to.


Selection bias. By putting it on your resume, you only heard from people who either were not opposed to the porn industry or were so vehement in their opposition to the porn industry they called you just to rant at you. However, since I assume we're talking about businesses here, rather than churches, it's unlikely you got many of the latter.

Also, your experience is completely relevant to IT: for three years, you worked in what is perceived by many to be the most demanding of all web developer jobs. (I've looked at a few porn sites - for a time I helped administer a friend's website on a server that also served some porn, and I got to see what crap code and crap security they can spew. So I'd agree that a porn server administrator is probably going to be reasonable to high quality. But a porn web developer may be stellar, or may be crap, and you can only tell by checking out the work product.)

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-18 20:07 • by tgape
338412 in reply to 335888
Rob White:
smxlong:

When I mentioned that a person's race could often be guessed just by what their name is, they shrugged and said "Doesn't make any sense, but those are the rules."


OK smart guy, what race am I?


Dat's easy. You're human.

See, the vast majority of people posting stuff on the Internet are either computers or humans. The computers usually do their best to not stand out, so they wouldn't ask others what race they are.

The dogs and cats (and occasional monkey) posting aren't *nearly* as adept with English and typing{1} as you apparently are. The dwarves, elves, gnomes, sylphs, kzinti, nymphs, demons, mermaids, dragons, dopplegangers, tauren, kobolds, goblins, gremlins, xorn, hooloovoos, elementals, kraken, and pretty much all other fantasy creatures that you see online aren't really real - they're generally computers or people pretending to be fantasy creatures.

{1} Not to imply that all humans or computers are adept with English. Most humans or computers who are active on the Internet are adept in at least one language, but it is not always English. And some seem to not be ept in any language.

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-02-24 14:09 • by Newbie (unregistered)
339042 in reply to 335752
minkey:
For the stored procedures one I think I would have made sure he heard me correctly. If he's been out of the DB world for a while his brain might have just gone to what he's used to first or he could have misheard. To dismiss someone that quickly because someone might be hard of hearing is kind of a dick move.



I agree, I was in a similar situation, but luckily the interviewers re-phrased the sentence, and I caught on and changed my answer rather quickly. I got the job!!! Glad they didn't pull a dick move too, otherwise I may be in the same boat as him......

Re: Stored Procedures, The Porn Guy, and Non-returnable Email

2011-03-09 11:58 • by legutko@ztas.cz (unregistered)
jezisku, napis to tam vole
« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6Next »

Add Comment