The Daily WTF: Curious Perversions in Information Technology

2011-01-28 Reply Admin

Nagesh Kukunoor:
hoodaticus:
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.
If you have ever written a stored procedure in your life, you would not forget what they are.

ORM has pretty much obviated the need to write a stored procedure. Only old FUDDY DUDDY's are writing them.

Think of it like this, in OO terms:

The database is your "object".

Its tables are your object's "fields".

Stored Procedures are your objects "methods".

In general you want to keep your object's field hidden from outside view, and only access them through methods.

I write and maintain many database applications and I can tell you ones that don't use stored procedures are a nightmare to maintain from a database point of view. You have ad-hoc queries hitting the database instead of a fixed list of procedures, and keeping track of what indexes are needed and optimising the data access is made far for difficult.

2011-01-28 Reply Admin

Jaime:
boog:
Jaime:
One of the errors I see people defending stored procedures make all of the time is to make easily falsifiable claims.
When did it become erroneous to make falsifiable claims? Why did no one tell me?
Jaime:
There are plenty of ways to tweak ad-hoc SQL to get within 0.01% of the performance of stored procedures, especially if the server isn't CPU-bound, which most aren't.
Am I the only one who uses stored procedures for more than just SQL queries? I believe database views already cover that area.
OK, so I could have used a better word like "disproven" instead of "falsifiable". Congratulations for winning the vocabulary war. Now back to SQL...
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.

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

2011-01-28 Reply Admin

Jaime:
BigJim:
Here's another argument for stored procs. IF you keep code in a stored proc, then an update can be made by pushing that proc to production. No code change/deployment needed. Depending on the environment, this might be an easier thing to push through all the gate-keepers - dba's, data center, etc. - involved in a "production fix".
So your big plus for stored proces is that they are easy to sneak around the change control process. Are you sure that's a good thing? You could use the same argument to show that it's a good idea for production code to call a script stored in your home directory.

I did not say I didn't go thru the change process, I just mean that the change process for DBA-level changes in all shops I've been in-not everywhere is less fraught with bureaucratic overhead

2011-01-28 Reply Admin

Dirge:
Jaime:
One of the errors I see people defending stored procedures make all of the time is to make easily falsifiable claims. Simply claiming that stored procedures are faster is not good enough. There are plenty of ways to tweak ad-hoc SQL to get within 0.01% of the performance of stored procedures, especially if the server isn't CPU-bound, which most aren't.

Ad-hoc SQL is never going to have the execution plan(s) pre-built on the server side, is it? If not, there's a performance hit right there.

I'm a big fan of stored procedures, but I think SQL Server will try to cache the compiled SQL and query plan for ad-hoc queries if it can. So whilst performance was a big issue at one time, it is less of one now.

Even so, I'd still recommend stored procedures for security, ease of monitoring how the database is accessed, flexibility to change the underlying tables as long as the stored procedure is left unchanged, ability to use several steps to return result sets if this is more efficient, etc.

2011-01-28 Reply Admin

anon:
The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.

I was hired at my first job after college because I had a beard.

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.

2011-01-28 Reply Admin

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.

2011-01-28 Reply Admin

[quote user="Dirge"][quote user="Jaime"] One of the errors I see people defending stored procedures make all of the time is to make easily falsifiable claims. Simply claiming that stored procedures are faster is not good enough. There are plenty of ways to tweak ad-hoc SQL to get within 0.01% of the performance of stored procedures, especially if the server isn't CPU-bound, which most aren't.[/quote]

Ad-hoc SQL is never going to have the execution plan(s) pre-built on the server side, is it? If not, there's a performance hit right there.

I can't speak for Oracle, but on SQL Server, stored procedures are great. They provide all sorts of opportunities to do processing on the DB server, instead of sending masses of data back and forth between the DB server and app server. The people who develop database software specialize in optimizing it for what it does, so why not let that software do that work for you instead of trying to code it yourself?

[quote] Since we are speaking MS-SQL Server, they have not pre-compiled for over 10 years, yes it has been over a decade since that statment that stored procedures are treated different then ad-hoc sql could be truthfully said about a recent software version. Some difference for CLR stored procedures.

As for optimizing database call go read up on high performance tuning and papers from places like amazon or google they all say the same thing ditch stored procedures. The truth is where stored procedures are used for CRUD in most cases they are alot slower then dyanamic parameterized SQL. The reason is they are full of ISNULL, COALEASE and CASE statments any they are slow, slow, slow. 10 years ago this was not the case and your statement is true.

The one main place where do see performance boost of dynamic parameterized SQL is in your example where you are having to process pass a bunch of data back and forth so that you can come up with a simple number. In that case yes stored procedures are probably the best thing to use, but then those cases are rare compared to all the simple CRUD that happens.

2011-01-28 Reply Admin

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?

When a friend of mine was in college in Lousiana in the early 1970s, he was in a dorm room talking to a friend of his and mentioned an upcoming course in AI.

The friends roommate was sitting nearby and got really irate at the mention of the AI course.

It turned out that he was ticked off to find that they had a course in Artificial Insemination in Computer Science when they didn't have one in Agriculture that he could take for his degree.

Tim G · 2011-01-28 Reply Admin

Matthew:
Tim G:
I don't disagree with you, but that's not what Matthew seems to be saying, which is that the fact that talking about one's work in the adult film industry isn't appropriate in some kind of inherent sense. Which is bullshit.
I mean that context matters. Talking about it in passing is fine. I personally don't have a problem with it - but I think mentioning it in an interview shows poor judgement. Do you want to employ people with poor judgement?

Let's look at the scenario again:

and found my way to a fairly casual lunch interview. After asking a slew of technical questions, one of the interviewers asked me what was the most interesting thing I ever worked on. I struggled to think of something. Up to that point I had only done really boring in-house apps and lots of SQL queries. <snip> I’m often hesitant to discuss it, but the guys seemed like open-minded young males who wouldn’t take offense to it.<snip> They seemed intrigued, and things seemed to go well after that."

Taking the OP at face value (which, admittedly, is a shakey proposition), it doesn't look like we're talking about someone with generally poor judgement. It looks like we're talking about someone who, under pressure and feeling nervous, considered the situation and made a judgement call and happened to be wrong. Would I have made the same call? Probably not. But I also wouldn't jump to condemn someone as not having social skills or being inappropriate based on that.

I spent most of the day yesterday and I'm going to spend most of the day today doing phone interviews. If I circular-filed every resume because the candidate was awkward or made some kind of minor social snafu, I wouldn't have anyone left. With respect to appropriate behavior, there's a big gulf between "candidate mentioned during a casual interview that was going well that he/she once had a job in a mildly taboo industry" and "candidate was an argumentative asshole, or candidate whined during the interview that the questions were too hard [this just happened to me], or candidate smelled bad and picked his toes during interview."

If it comes down to you and one other person, and you mentioned something like that and the people you were interviewing with wanted to take it into account, might it cost you the interview? Sure. But there are lots of places to interview with out there.

2011-01-28 Reply Admin

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.

Perhaps you could try reading up on, say, actual laws?

Discrimination is legal on any grounds except the ones that are illegal, such as : -

Age (unless they're too old / young / inexperienced / experienced)

Gender (unless they're the wrong gender)

Sexuality (unless they're just too unsexy or you're in one of those countries where sex is illegal & immoral; Saudi Arabia, Pakistan, America...)

Disability (unless they're, like, a freak?)

;)

2011-01-28 Reply Admin

Nagesh Kukunoor:
My experience tells me that stored procedures are a thing of the past. You should keep away from them as much as possible.
You should rely on ORM tools like Hibernate and that will obviate the need to write any stored procedures.

Death to stored procs, I say.

CAPTCHA: nimis

You're new here, right?

Where I work, we use a farm of SQL servers. Gawd save us from the nonesense that hits the server without any thought of parameterisation, indexing, or plan optimisation... i.e. all the stuff from the sooper-dooper GUI that hangs IIS and SQL a dozen times a day.

Captcha "quibus" - to quibble about the silliest of things. Fancy THAT happening!

galgorah · 2011-01-28 Reply Admin

CnC:
I find stored procedures useful because so often in big companies the application guys aren't database oriented and thus make design decisions that fail to utilize the horsepower of a big database (see Exadata2). And a database developer using something like PL/SQL can leverage the power more effectively by hiding that complexity from the app developers.

I'm a software development lead and an accidental SQL Server DBA at my current job. I would argue that for most developers a basic understanding of SQL and database design is a must. Obviously there are some exceptions such as in industries where skill sets tend to be more specialized (games). If a member of my team is weak or has no skill in this area, I will make it a point to help bring them up to speed. We also have a very close relationship with our Oracle DBA's and the BI team. Both of whom will offer advice.

2011-01-28 Reply Admin

Well, some girls do and some girls don't.

frits · 2011-01-28 Reply Admin

eric76:
anon:
The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.
I was hired at my first job after college because I had a beard.
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.

That's a great point. think about how much better ZZ Top would have been if the drummer had a beard.

luis.espinal · 2011-01-28 Reply Admin

Jim Howard:
I worked in the UK about 15 years ago, so things might have changed, but then age discrimination was very legal and normal.
Applicant age was prominently mentioned in almost every employment advertisement. You'd see something like 'Java programmer with five years experience, should be between 20 and 30 years of age'.

Here in the United States people over 40 are a protected group, along with veterans, felons, and people descended from Spain. Oddly people of Portuguese descent are not a protected group.

Go figure.

Please provide links to legislature (state or federal) that states felons, people from Spain and people over 40 are to be treated as protected groups.

kthxbye.

2011-01-28 Reply Admin

The real WTF is TDWTF accepts to publixh stories from the porn guy. Enough for me. I'm cuuting it with reading TDWTF

2011-01-28 Reply Admin

frits:
eric76:
anon:
The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.
I was hired at my first job after college because I had a beard.
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.

That's a great point. think about how much better ZZ Top would have been if the drummer had a beard.

His name is Frank Beard, which I think would lose it's funniness if he actually had a beard.

galgorah · 2011-01-28 Reply Admin

Dirge:
Ad-hoc SQL is never going to have the execution plan(s) pre-built on the server side, is it? If not, there's a performance hit right there.

The execution plan is not actually prebuilt. SQL Server 2008 generates a Query Plan when the proc is compiled and stores it in the query cache. However Every concurrent execution of the procedure will generate an execution plan as well. Plus if the proc contains a lot of branching each run could produce a drastically different execution plan. So you could end up with multiple execution plans in the query cache for the same proc. Thats why you have the recompile option. It will tell SQL Server to NOT store a query plan in the cache.

Addendum (2011-01-28 10:46): that first sentence should read: The execution plan is prebuilt.

2011-01-28 Reply Admin

Ken B.:
But atoms can be broken down into smaller components, making the term a misnomer.

No. Atoms cannot be broken down into smaller components.

We just made a mistake when we named atoms "atoms".

2011-01-28 Reply Admin

Andrew Brehm:
Ken B.:
But atoms can be broken down into smaller components, making the term a misnomer.

No. Atoms cannot be broken down into smaller components.

We just made a mistake when we named atoms "atoms".

Darn. That Large Hadron Collider is a complete waste of time & money; my electron microscope is running on empty, and the atom bombs are all bang and no rads.

Curse Quark and his pesky tricks (or was that Q in Star Trek?)

2011-01-28 Reply Admin

anon:
I'd say it's abundantly clear you've never read up on employment discrimination laws. The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.

Whew, I'm glad I can still refuse to hire anyone who isn't a conservative Christian by claiming I don't like their necktie!

Loopholes are the only good thing about bad laws.

frits · 2011-01-28 Reply Admin

Jim Howard:
Here in the United States people over 40 are a protected group, along with veterans, felons, and people descended from Spain. Oddly people of Portuguese descent are not a protected group.
Go figure.

That's because we Portuguese run the US through secret societies.

Ever heard of the Knights of Columbus?

2011-01-28 Reply Admin

Rob White:
OK smart guy, what race am I?

Formula One, obviously!

2011-01-28 Reply Admin

Jaime:
Name one SQL statement (other than RETURN) that can be used in a stored procedure, but not in an ad-hoc batch.
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.

Try that on INFORMIX.

You will learn that there is no "if .. then .. else .." and there are no variables outside stored procedures.

(I do not claim that INFORMIX is anything other than TRWTF. But some companies are still running installations).

Besides that stored procedures allow to maintain fine-grained access control on the database server.

With ad-hoc SQL a user either has update permission on the whole table or on nothing. Stored procedures allow to grant access based on rules implemented in the stored procedure.

I know that your kind will re-implement access-control and maintenance of user permissions in the application server, because "Why use what we already have bought with the DBMS? You will be faster when you implement it yourself and finish the job in six weeks."

2011-01-28 Reply Admin

anon:
I'd say it's abundantly clear you've never read up on employment discrimination laws. The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.

Thanks for clarifying that every person in the world is protected by the exact same "employment discrimination laws" (presumably created by Mr. Obama - Nobel peace prize winner and leader of the free world)regardless of which country they live in and what their legal status is..

boog · 2011-01-28 Reply Admin

Jaime:
OK, so I could have used a better word like "disproven" instead of "falsifiable". Congratulations for winning the vocabulary war.

Vocabulary war? No, my problem was not your choice of word, but rather the statement you were making: You said falsifiable claims were a bad thing. That's kind of a big deal in any serious discussion.

But I'm glad we cleared it up.

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.

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.

Before I do, I see that someone else has already taken up your challenge; let's see your response to them first.

2011-01-28 Reply Admin

Some Wonk:
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?
It's atoms all the way down!

Loved the reference, thanks for the chuckle :)

2011-01-28 Reply Admin

The porn guy was a COMPLETE failure in that interview.

"Tell me something interesting you have worked on" in an IT interview should be intepreted as "I would like to know whether you will be interested in the type of work and challenges which we will be giving you."

Giving them a story about an unrelated job has absolutely no merit as a response to this question. He may as well have talked about unicorns.

Jaime · 2011-01-28 Reply Admin

Drak:
Jaime:
boog:
Jaime:
One of the errors I see people defending stored procedures make all of the time is to make easily falsifiable claims.
When did it become erroneous to make falsifiable claims? Why did no one tell me?
Jaime:
There are plenty of ways to tweak ad-hoc SQL to get within 0.01% of the performance of stored procedures, especially if the server isn't CPU-bound, which most aren't.
Am I the only one who uses stored procedures for more than just SQL queries? I believe database views already cover that area.
OK, so I could have used a better word like "disproven" instead of "falsifiable". Congratulations for winning the vocabulary war. Now back to SQL...
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.

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.

Jaime · 2011-01-28 Reply Admin

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?

2011-01-28 Reply Admin

Isn't it usually illegal to ask for a photograph on a job application, what with anti-discrimination laws and all?

2011-01-28 Reply Admin

At first I didn't even catch the non-returnable photo good in that WTF. I was too distracted by the blatantly illegal request for the applicant's age. The photo seemed natural after the setup that they were eager to discriminate based on non-job related things.

luis.espinal · 2011-01-28 Reply Admin

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.
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.
Before I do, I see that someone else has already taken up your challenge; let's see your response to them first.

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.

2011-01-28 Reply Admin

The Flaming Foobar:
amischiefr:
Actually these are the kinds of questions that work best. You get the person talking about a subject and let them either talk themselves into or out of a job.

They work great if you are looking for a lecturer.

If you are looking for a person capable of writing high-quality code, you should probably try to find out what kind of problem-solving skills they have and not what they have memorized from DB101.

There is practically no difference between writing, say, a Perl script, and a stored procedure. Any experienced programmer will need one good example of a stored procedure, and maybe a list of dos and dont's, and they'll be writing there own in like 5 minutes.

Possibly true, but if in an interview you leave out all questions about things that the candidate might be able to learn, well, that wouldn't leave much to ask about, would it? I mean, any candidate could potentially learn any required skills given sufficient time and effort.

What do you expect a company to do in an interview if not ask the candidate about his exerience with tools and techniques that he will need to do the job? OF COURSE it would generally be foolish to reject a candidate for not having one particular skill, as long as he has most of what you need. But you have to ask about a variety of skills to find which ones he has and which ones he doesn't.

2011-01-28 Reply Admin

pjt33:
backForMore:
Don't bother posting without a non-returnable photograph.
Does it have to be a photograph of me?

No. We would prefer if you sent some of Liz's photos.

2011-01-28 Reply Admin

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

2011-01-28 Reply Admin

Warpedcow:
anon:
I'd say it's abundantly clear you've never read up on employment discrimination laws. The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.

Whew, I'm glad I can still refuse to hire anyone who isn't a conservative Christian by claiming I don't like their necktie!

Loopholes are the only good thing about bad laws.

Well, in real life you would end up in court and you would have to convince the judge or jury that you didn't hire him because you didn't like his tie, while he would try to convince them that you didn't hire him because he is a Buddhist or whatever.

In real life, of course, it would be smarter to come up with a more plausible story, like you didn't hire him because he has no experience with Stored Procedures and you believe this to be a crucial job requirement.

Actually proving what the employer was thinking in one particular case is pretty hard. This is why so many discrimination lawsuits bring in statistical evidence, like, 30% of the people in our city are Portugease, but only 5% of this company's employees are Portugease, therefore they must be discriminating.

Of course many employers find this very frustrating because, no matter how much anti-discrimination activists may deny it, in real life there are many jobs that certain categories of people rarely apply for, so it's tough for an employer to meet these implied quotas. Like, relatively few cooks at Chinese restaurants are black people. Is this because Chinese restaurant owners discriminate against blacks? Or could it maybe be that not many blacks apply? Of course rap bands rarely hire Chinese people, so I guess the discrimination works both ways. Few Moslems are bartenders. Few auto mechanics are women. Few aerobics instructors are men. Etc etc. Most people accept that gender, culture, religion, and a host of other factors may influence a person's career choices. But anti-discrimination laws often refuse to recognize this and insist that if all bagpipe players are Scottish, this must be because you discriminated against everyone else. The statistics prove it.

Matt Westwood · 2011-01-28 Reply Admin

Ron:
Jim Howard:
Here in the United States people over 40 are a protected group, along with veterans, felons, and people descended from Spain. Oddly people of Portuguese descent are not a protected group.
Go figure.
It's because we all know the Spaniards "need a little help" while the Ports are just as good as anyone else.

There, see? Segregating people by some arbitrary group and saying "you can't discriminate against these people" is discrimination! And it perpetuates the idea that some people -- not individuals, but groups -- are weaker, and couldn't make it on their own.

I have a dream... that someday people will not be judged for the color of their skin, but for the content of their character. Radical, I know.

That's a silly idea, but then that's what you'd expect from a Ron.

Jaime · 2011-01-28 Reply Admin

???:
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();

Matt Westwood · 2011-01-28 Reply Admin

eric76:
anon:
The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.
I was hired at my first job after college because I had a beard.
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.

Yes, that technique always works, but you can only apply it when both interviewers are bearded. It got me a job when I was desperate for work once. After my first pay packet I could then afford a razor, by which time it was too late for the company that hired me to decide they'd made a mistake, it would have been too much hassle to readvertise the job.

Jaime · 2011-01-28 Reply Admin

Jay:
Warpedcow:
anon:
I'd say it's abundantly clear you've never read up on employment discrimination laws. The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.

Whew, I'm glad I can still refuse to hire anyone who isn't a conservative Christian by claiming I don't like their necktie!

Loopholes are the only good thing about bad laws.

Well, in real life you would end up in court and you would have to convince the judge or jury that you didn't hire him because you didn't like his tie, while he would try to convince them that you didn't hire him because he is a Buddhist or whatever.

In real life, of course, it would be smarter to come up with a more plausible story, like you didn't hire him because he has no experience with Stored Procedures and you believe this to be a crucial job requirement.

Actually proving what the employer was thinking in one particular case is pretty hard. This is why so many discrimination lawsuits bring in statistical evidence, like, 30% of the people in our city are Portugease, but only 5% of this company's employees are Portugease, therefore they must be discriminating.

Of course many employers find this very frustrating because, no matter how much anti-discrimination activists may deny it, in real life there are many jobs that certain categories of people rarely apply for, so it's tough for an employer to meet these implied quotas. Like, relatively few cooks at Chinese restaurants are black people. Is this because Chinese restaurant owners discriminate against blacks? Or could it maybe be that not many blacks apply? Of course rap bands rarely hire Chinese people, so I guess the discrimination works both ways. Few Moslems are bartenders. Few auto mechanics are women. Few aerobics instructors are men. Etc etc. Most people accept that gender, culture, religion, and a host of other factors may influence a person's career choices. But anti-discrimination laws often refuse to recognize this and insist that if all bagpipe players are Scottish, this must be because you discriminated against everyone else. The statistics prove it.

But, if you simply show eveidence that you tried, you're off the hook. For example, if statistics show that the community is 30% black, but your IT department is only 5% black, but you placed employment ads in publications that had prodominantly black subscribership, then you are fine. Even if you get zero qualified applicants, your only obligation is to make a real effort to reach minority candidates.

Jaime · 2011-01-28 Reply Admin

luis.espinal:
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.
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.
Before I do, I see that someone else has already taken up your challenge; let's see your response to them first.

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.

hoodaticus · 2011-01-28 Reply Admin

no2trolls:
anon:
I'd say it's abundantly clear you've never read up on employment discrimination laws. The only things you can't legally discriminate based on are the so called "protected classes": race, color, religion, national origin, age, sex, familial status, sexual orientation, gender identity, disability or veteran status. You can not hire someone because you don't like the color of their tie if you so choose and it's totally legal.

Thanks for clarifying that every person in the world is protected by the exact same "employment discrimination laws" (presumably created by Mr. Obama - Nobel peace prize winner and leader of the free world)regardless of which country they live in and what their legal status is..

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.

2011-01-28 Reply Admin

Jaime:
luis.espinal:
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.

And if you would have read luis post less distracted, you would have noticed that he contrasted T-SQL with SQL.

T-SQL actually is not SQL, it is an extension of SQL.

hoodaticus · 2011-01-28 Reply Admin

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?

Who uses only one or the other anyway?

I use dynamic SQL when the query is stupid simple, or when it's too dynamic to write a stored procedure for (such as when I have query parts being populated by visitors to my business object). I use Stored Procedures when the query is complex and can benefit from the compile-time checking that occurs in SSMS when I create the procedure.

2011-01-28 Reply Admin

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?

2011-01-28 Reply Admin

Matt Westwood:
Yes, that technique always works, but you can only apply it when both interviewers are bearded.

I believe it's not illegal to require a picture of the interviewer when you're called in for an interview... stupid, maybe, but not illegal.

boog · 2011-01-28 Reply Admin

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.

boog · 2011-01-28 Reply Admin

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.

2011-01-28 Reply Admin

If he'd been out of the DB world for long enough that this is his first reaction to stored procedures, whether or not he misheard me, I don't want to be his gateway back into the DB world.

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

Leave a comment on “Stored Procedures, The Porn Guy, and Non-returnable Email”