The Daily WTF: Curious Perversions in Information Technology

2006-10-06 Reply Admin

Anonymous:
I'm absolutely speechless! Data integrtity is the job of the database, it's what they are designed to do. Why write a lot of code to handle it in your app when it's taken care of for you if you use the database as you are meant to?

Because your app will have to take care of the errors that arise when the integrity is being violated. In any good app, you will add code to check that integrity is not being violated in the first place, rather than relying on obscure SQL error messages that will more often than not tell you that something went wrong but not what, let alone what you must do to avoid the problem.

Database integrity rules should be the absolutely last safety net, and you shouldn't rely on them for the correct functioning of your app.

Enric Naval · 2006-10-06 Reply Admin

cvladimir:

This reminds me of a perfectly normalized table we have in our db...
CREATE TABLE [dbo].[HIERARCHIES]( [Id] [int] NOT NULL, [Code] [nvarchar](128) NULL, [Description] [nvarchar](1024) NULL, [SomeBoolean] [bit] NULL, [Lvl1] [nvarchar](128) NULL, [Lvl2] [nvarchar](128) NULL, [Lvl3] [nvarchar](128) NULL, [Lvl4] [nvarchar](128) NULL, [Lvl5] [nvarchar](128) NULL, [Lvl6] [nvarchar](128) NULL, [Lvl7] [nvarchar](128) NULL, [Lvl8] [nvarchar](128) NULL, [Lvl9] [nvarchar](128) NULL, [Lvl10] [nvarchar](128) NULL, )

I was going to be witty and say "Yeah, because everyone knows that we will never have/need more than 10 levels of hierarchy" or "10 levels of hierarchy are enough for everybody". But then I realized that an organization having a hierarchy more than frigging 10 levels deep probably has worst problems than its database scheme :(

2006-10-06 Reply Admin

OH MY GOD!

this is, IMHO, The Real WTF (TM)!!

you sir, should not be allowed anywhere near a database!!

2006-10-06 Reply Admin

Anonymous:
Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

holy crap!

thats the real wtf, right there!

CAPTCHA: clueless, do i need to say more??

JustThat · 2006-10-06 Reply Admin

Anonymous:
JustThat:
(...)
I goofed when I sent this in to Alex. Should read like this (note the _ instead of . in the column names):
SELECT Product_ID, Product_name, 
case WHEN 
      (SELECT SUM(ProductRequest_ProductFaceValue) 
      FROM Requests WHERE (ProductRequest_ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) is null 
then 0 
ELSE 
      (SELECT SUM(ProductRequest_ProductFaceValue) 
      FROM Requests WHERE (ProductRequest_ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) 
end 
as TotalDollarAmount FROM Requests WHERE (product_ID IN (@pid))
 I rewrote this to: 
SELECT Product_ID, Product_name, ISNULL ((SELECT SUM (ProductRequest_ProductFaceValue) FROM Requests WHERE (ProductRequest_ProductID = Product_ID) AND (ProductRequest_createdByUserUID = @uid)),0) as TotalDollarAmount FROM Requests WHERE (product_ID IN (@pid))
...which runs in 52 milliseconds mostly because it only has to evaluate the subquery once
Er. I'm not quite sure what "IN(@pid)" is supposed to be. Let's say @pid is some set of values. In that case I'd expect to see something like "IN(SELECT xyz FROM @pid)" but whatever. Now, assuming that this "IN(@pid)" is true for more than just one value - in that case you'd be mistaken. The speedup would be there because your DBMS uses a smart query optimizer that can now see the JOIN which it couldn't see before. Rewrite like this, and you'll see what I mean:
SELECT	p1.Product_ID AS Product_ID, p1.Product_name AS Product_name, ISNULL(p2.ProductFaceValueSum, 0) AS TotalDollarAmount
FROM	(
		SELECT	Product_ID, Product_name
		FROM	Requests
		WHERE	(Product_ID IN (@pid))
	) AS p1 LEFT JOIN (
		SELECT	ProductRequest_ProductID, SUM(ProductRequest_ProductFaceValue) AS ProductFaceValueSum
		FROM	Requests
		WHERE	(ProductRequest_ProductID IN (@pid)) AND (ProductRequest_createdByUserUID = @uid)
		GROUP BY
			ProductRequest_ProductID
	) AS p2 ON (p2.ProductRequest_ProductID = p1.Product_ID)
If you write it like that even a dumb DBMS could execute it quite fast. And one can actually see what's going on, which is a nice thing too. Cheers.

@pid is a parameter and contains a single value

KoFFiE · 2006-10-06 Reply Admin

Anonymous:

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

Wow - this-one stunned me for a moment, this has to be an even bigger wtf than the original article...

I really wonder why using a database at all if you think like that... Flat files all the way then! Or maybe XML? :P

JustThat · 2006-10-06 Reply Admin

Anonymous:
So the WTF here is the means of communication between the DBA and the developers right?
Why should a .NET developer have any real knowledge of the needs of a DBA, and why should a .NET developer live to the rules of a DBA when as far as he's concerned it's just a bunch of hoops and no real use to him?
Instead of having a throw it over the wall attitude to this kind of stuff, there should be dev DBAs in the dev team pairing with people, explaining these things face to face, fostering good relationships between the different parts of the teams and ensuring quality before any code review stage.

Plus, why should the method of 'doing it in dev' be any different to the method of doing it in production? No, I don't mean that the DBA should import the CSV in an adhoc manner, I mean that the only way the developer should be able to get a table into his dev structure is by running the script that he's going to hand over the the DBA. And it should run through the same database build strucutre that every script hits the production database through. The DBA does install the scripts by running a consistent single command containing a version control tag, the script extracting the relevant install scripts from VC, checking they're valid to run, the
database is in the right state to run them, and they've not already been ran on this database before running them?
If not, then the whole structure is big pile of WTF!

Captcha: You've been tango'd

WRONG

The WTF here is that the DBA, me, tried for several weeks to help the developer, a contractor who was hired to write a complete application -- database and all, and still wound up stratching his head every day. The two examples posted in the WTF entry are near the end of the project when I just couldn't take it any more. You can only push on a rope for so long before realizing it isn't moving the object at the other end. Here are some of the more memorable quotes from the developer:

"T-SQL doesn't have an IF statement" (I told him it did and that the SQL Books Online explained it very well)

"I don't know much about SQL" (I did not ask WTF he was developing a database app, then)

"I don't know how to use ISNULL. Can you send me an example?" (I did. It took three more releases before he used it)

"Why is putting the entire update process and all of the logging for the updates in a single transaction a bad thing?" (We had to explain that a problem with the last record in the import could cause the entire import and all of the associated logging telling us where the problem is to be rolled back. He got upset that we didn't tell him how we wanted the import to work.)

"This table shouldn't need indexes"

The REAL WTF (tm) IMHO is that a .NET developer who admits he doesn't know much SQL would still try to develop a .NET/SQL application without seeking the guidance of a SQL expert.

2006-10-06 Reply Admin

Anonymous:
I can see that a char(8000) is wasting resources, I don't see how a varchar(8000) does?

The varchar isn't wasting resources.

It's allowing for the possibility of unexplected errors or truncation when you have multiple varchar(8000) in one table.

At least in MS SQL the storage limit for one row of data is just over 8000 bytes (unless the evil text or image datatype is used).

KenW · 2006-10-06 Reply Admin

D. Skinner:
Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

Are you nuts? Data integrity belongs in the database, where it can be set up once and used by everything that accesses the data. It should not be left up to the application to implement (or not) at the developer's whim.

Putting it in the database means that integrity is consistently enforced. Leaving it up to the app makes it hit or miss.

2006-10-06 Reply Admin

Alex Papadimoulis:
When J.T. mentioned that they will have to optimize the query because it ran for 2000 milliseconds, Frank explained that it's already optimized and can't run any faster. J.T. updated the query to use an ISNULL and increased the run time to 52 milliseconds. It was a small, 3800% decrease.

I once had to rerun an update procedure whose cursor-based routine to transform some imported data took so long to run that during the run I had to write a query to show me what % of the progress had completed thus far. It took about 90 minutes to run...every day.

Basically it stepped through each product in the table, checked its code in a monstrous switch statement, then updated that specifc product's product line.

I replaced it with a half-dozen statements of the form UPDATE [Products] set ProductLine = 'Foo' Where ProductCode in ('Foo1', FooTwo','Foo Accessories')

Unfortunately it only runs 180 times faster than the cursor method (30 seconds).

2006-10-06 Reply Admin

codenator:
Anonymous:

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

This may just be the stupidest quote ever written here...no wait it is!

The thing is, that's the attitude of EVERY major application vendor- even PeopleSoft and Siebel (both owned by Oracle). They do tell you its OK to leave foreign keys in development environments, so you can verify the app is really keeping an eye on things, but, to speed up production, they'll tell you to get rid of them.

That attitude has always baffled me; I agree with posters who wonder why you should be using an RDBMS if you're not going to actually use it. And, what's really funny is, these companies go to the trouble to make versions compatible with all the major RDBMSs, and then, go and basically turn off the fucking things. Why don't they just use their own serialization scheme, if they're going to do that?

2006-10-06 Reply Admin

Anonymous:
Anonymous:
Anonymous:

Seems to me like things are working just fine. And, as long as the DBA keeps being the janitor, things will continue to work fine.
DBAs crack me up. They're like, if a garbage man really thought he was a sanitation engineer, and ran around saying, "If I didn't have to pick up after all these people, I could get some engineering done."

Please use your name, so I can pre-screen any future employers for your presence.

Right On!
DB Administrators and DB Architects Rock!
WE ROCK!!!

Yeah, you rock.

Now, it seems like there's a user who needs his password reset, so get back to work!

mouseover · 2006-10-06 Reply Admin

Anonymous:
Anonymous:

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)
Those who are blindly mocking this statement need to learn a thing or two about engineering, and about database theory.
Keys imply both an index and a constraint. Indexes generally make queries faster at the expense of insert/delete time and space. It is an important engineering tradeoff to determine exactly where this tradeoff makes sense and where it doesn't (hint: it often doesn't). Constraints limit data integrity - for many applications, you already know you will not fail a constraint check, and for foreign key cascading behavior, can provide such behavior yourself more efficiently since it doesn't have to be generic. Why, then, add constraints that should never be hit? Yes, some apps have such high requirements for reliability that protecting your data from even the application is a requirement, but I bet most of you aren't working with apps that require that level of reliability guarantees.

You are absolutely correct, sir! Key fields, both primary and foreign, are for optimization only according to the inventor of the RDBMS (E. F. Codd). The only necessary constraint is that there be no duplicate rows, according to Codd. Key fields can notionally subvert this constraint. Of course, as a practical matter, a complex database with no keys and no duplicate rows in child tables (absent the keys) will probably be unusable in real time for anything except as a write-only repository. As you point out, appropriate design requires analysis of the data and the uses to which it will be put before implementation. This argues for at least some rudimentary knowledge of data design principals on the part of programmers who have to deal with stored data.

2006-10-06 Reply Admin

JustThat:
The REAL WTF (tm) IMHO is that a .NET developer who admits he doesn't know much SQL would still try to develop a .NET/SQL application without seeking the guidance of a SQL expert.

You so need to get your hands on his code... I'm guessing he wasn't much of a .Net developer either and an archeological dig in his code would reveal many a WTF for future posts.

JustThat · 2006-10-06 Reply Admin

Anonymous:
JustThat:

@pid is a parameter and contains a single value
Hm. *confused*. In that case the query optimizer that made the 2000ms execution plan just plain sucks.

Perhaps, but I looked at it this way:

1. Removing the GROUP BY saved a little time because it isn't needed. The results are identical whether it is used or not

2. ISNULL is faster than CASE in that CASE must completely evaluate the subquery each time. ISNULL on the other hand will stop being evaluated (though the subquery will continue) as soon as the value of the subquery is no longer null

3. The original CASE statement ran the entire subquery twice: once to see if the value was null and return 0, once to return the value if it isn't null. This is just waste.

JustThat · 2006-10-06 Reply Admin

Anonymous:
JustThat:
The REAL WTF (tm) IMHO is that a .NET developer who admits he doesn't know much SQL would still try to develop a .NET/SQL application without seeking the guidance of a SQL expert.
You so need to get your hands on his code... I'm guessing he wasn't much of a .Net developer either and an archeological dig in his code would reveal many a WTF for future posts.

I'm no expert in .NET so I won't go poking around there and wouldn't dare criticize him for a topic I know little about myself.

However, there were some parts of his code I did see (huge multiple-line dynamic SQL constructors) that I would have done a different way...say, using a parameterized stored procedure instead of dynamic SQL.

sammybaby · 2006-10-06 Reply Admin

marvin_rabbit:
jmounce:
On second thought, can you get me the names and phone numbers of the 2 Battlestar Galactica babes I see in the upper right-hand ad in this thread? That may actually be more useful...
I believe they are Grace Park and Tricia Helfer. Phone numbers... You're on your own.

You sir, are correct.

And you can see them both in the season premier of BSG tonight at 9 on Sci Fi, just like it says in the ad.

As the kids are so fond of saying these days, "rrowl."

the_saint · 2006-10-06 Reply Admin

The real WTF: SQL Server, try using a real database.

2006-10-06 Reply Admin

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

Too true, Too true. If this was 1980 and you were developing on a mainframe. Indexes are usually necessary for large tables? I'd say that they're always necessary for large tables. If perhaps you want to get data back at some point. You might find that perhaps it's not the overhead of the keys that are causing you slowdowns, but the lack of any indexes. Learn to use explain. Use a primary key. You'll be amazed at the speedup.

2006-10-06 Reply Admin

Ah, a developer willing to learn. They make the job worth while.

I don't see the WTF here though. This describes most of my DBA interactions. As a contractor, as soon as I train one set of duhvelopers to developers I have to leave to work with another set of duhvelopers

And I agree that many DBA's are brainless twits. That is a good thing (for me at least)

Captcha: bedtime, already?

JustThat · 2006-10-06 Reply Admin

the_saint:
The real WTF: SQL Server, try using a real database.

You're one of those condescending DB2 users, aren't you? :)

JustThat · 2006-10-06 Reply Admin

Anonymous:
Ah, a developer willing to learn. They make the job worth while. I don't see the WTF here though. This describes most of my DBA interactions. As a contractor, as soon as I train one set of duhvelopers to developers I have to leave to work with another set of duhvelopers And I agree that many DBA's are brainless twits. That is a good thing (for me at least) Captcha: bedtime, already?

You know, it's funny. You think many DBA's are brainless twits...I think many developers are brainless twits.

We are probably both right

Then again, I've also met some DBAs who I think are brainless twits and no doubt I've met some DBAs and developers who think I am.

Twittery is in the eye of the beholder.

yo no soy "brainless twit"

2006-10-06 Reply Admin

And the real WTF..
Allowing users to post all on one line and avoid word wrapping by adding scrollbars so you have to keep scrolling left and right when they decide to actually add a carriage return. Just plain silly.

I second that emotion!

2006-10-06 Reply Admin

Anonymous:

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

Ah, a FileMaker user?

2006-10-06 Reply Admin

JustThat:

I'm no expert in .NET so I won't go poking around there and wouldn't dare criticize him for a topic I know little about myself.
However, there were some parts of his code I did see (huge multiple-line dynamic SQL constructors) that I would have done a different way...say, using a parameterized stored procedure instead of dynamic SQL.

Maybe a stored proc... but at the very least he should be using a parameterized query. If it's actually string concating, then I do smell some wonderful WTF'ery in the works on the code side.

2006-10-06 Reply Admin

In J.T.'s organization, it's the responsibility of the developer to understand SQL and databases well enough to create tables, understand keys and data types in the database and optimize queries. It's apparently the responsibility of the DBA to mock the developers.

My solution? Fire both Frank AND J.T. and get a competent development staff that doesn't include DBAs.

2006-10-06 Reply Admin

Anonymous:
Ah, a developer willing to learn. They make the job worth while. I don't see the WTF here though. This describes most of my DBA interactions. As a contractor, as soon as I train one set of duhvelopers to developers I have to leave to work with another set of duhvelopers And I agree that many DBA's are brainless twits. That is a good thing (for me at least) Captcha: bedtime, already?

A developer willing to learn? From the cadence of the original post, I'd say the developer was an arrogant, over-inflated moron. If the post describes most of your DBA interactions, then you should re-evaluate the one constant in all of those relationships.

2006-10-06 Reply Admin

Hoo boy! WIth a 'philosophy' like that, I'd hate to have to maintain the crap you develop.

Are you related to Paula?

Ghost Ware Wizard · 2006-10-06 Reply Admin

"P.P.S. Hmm BSG girls. And both of these were cylons. Kinda like an everyready girlfriend. She just keeps... oh sorry wrong blog."

< roflol > nice! < roflol />

2006-10-06 Reply Admin

Anonymous:
Anonymous:

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)
Ah, a FileMaker user?

What! I do believe FileMaker forces the implementation of indexes and primary keys if any relationships are defined.

I have moved on from that little system. I think it's a fine system nonetheless.

2006-10-06 Reply Admin

Anonymous:

In J.T.'s organization, it's the responsibility of the developer to understand SQL and databases well enough to create tables, understand keys and data types in the database and optimize queries. It's apparently the responsibility of the DBA to mock the developers.

My solution? Fire both Frank AND J.T. and get a competent development staff that doesn't include DBAs.

Frankly, regarding database architecture, there is just too much to know. Your average client-side developer is a specialist in what he does. You need a specialist in databases as well. Unless you're just building mom & pop web apps.

JustThat · 2006-10-06 Reply Admin

Anonymous:
In J.T.'s organization, it's the responsibility of the developer to understand SQL and databases well enough to create tables, understand keys and data types in the database and optimize queries. It's apparently the responsibility of the DBA to mock the developers.

My solution? Fire both Frank AND J.T. and get a competent development staff that doesn't include DBAs.

Care to post some of your own data designs so we can see just how well that works?

Oh, and do you manage the database servers too?

Gee, I'd hate to be your boss. I mean, I'd have to pay you a MFT of money to do all that work and then, I'd be scared shitless you'd be hit by a truck. What's more: No one could ever criticize your code because you'd have complete control over it from one end to the other. That's just not good practice in any business transaction; accounting, HR, sales or development. If one person does everything from one end of a project to the other you're going to have a very one-dimensional outcome.

<ahem>

Now to respond to your weak point: If a company hires a developer to write a .NET web-based application with a SQL backend then it is the responsibiltiy of that developer to either know the SQL needed to do his job or to ask for assistance when needed if they don't. This developer did not do the first and did little of the second.

the_saint · 2006-10-06 Reply Admin

JustThat:

the_saint:
The real WTF: SQL Server, try using a real database.

You're one of those condescending DB2 users, aren't you? :)

Nope, just a condescending Oracle developer! :)

2006-10-06 Reply Admin

A village is missing their idiot, I think I have found him!

2006-10-06 Reply Admin

Anonymous:
The dev is probably a PHP-tard, so no cast is necessary. I find this sort of thinking to be very common across the PHP developer-base.
I'm a PHP-tard - and I admit it. I use it often. That means in my case that I'm also the DBA-of-sorts on my projects. I'm fortunate to have peers who make a living as DBA's on an enterprise level. To be able to tap them on the shoulder an ask for advice is a great resource. I've found that when asked, they'll eagerly provide feedback on DB structure, as well as other aspects I would have never considered. There's no measure on what value they provide, other than to say, when I follow their advice... everything works. I have found that the best time to 'tap' them is before, not after the project is underway.
As to which concern should claim ownership of DB integrity... I think it falls squarely on the shoulders of both the DB administrator( for design and governance ) and the Developer ( for implementation and execution ). The time wasted by finger-pointing between DBA and Developer is easily avoided ahead of time.

I would contend that this sort of thinking makes you NOT a PHP-tard. Just like every stereotype, there are those who satisfy the criteria (using PHP), but fail to exhibit the predicted behavior (data-modeling stupidity). I wasn't saying that all users of PHP are retards. I simply hold the belief that the PHP community contains more than it's fair share of "database as a bit bucket" developers. This is why I have given up on PHP. Sticking only to Java improves the company I am able to keep. You are correct to 1.) Use the language that you feel most comfortable in and 2.) Seek the assistance of a specialized professional for your data modeling work. The DBAs probably don't write PHP as well as you do, so it all works out in the end.

Oliver Klozoff · 2006-10-06 Reply Admin

Anonymous:
Anonymous:
Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)
I remember a recent boss who had that "the app will maintain data integrity" philosophy. Needless to say, the data was a giant turdball. Too bad all the major database players have these key things that kill performance. What were they thinking? why didn't they consult someone like you before they went and did all of that?

The "app will maintain data integrity" thing comes from MySQL. MySQL's MYISAM tables (the default kind) do not support foreign keys . If you try to create one, it accepts and silently ignores the request. These same tables do not support transactions; if you do this:

BEGIN TRANSACTION
DELETE FROM MyTable

ROLLBACK

That DELETE statement actually operated outside of the transaction. Not that you get any indicator to this effect -- no warning or anything.

2006-10-06 Reply Admin

Anonymous:

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

keys are bad? WTF??? Mod this comment down as troll!

2006-10-06 Reply Admin

Alex Papadimoulis:
I'll leave you with one of the last things that Frank sent over for review. It was the following query:
SELECT Product_ID, Product_name, 
case WHEN 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) is null 
then 0 
ELSE 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) 
end 
as TotalDollarAmount FROM Requests WHERE (product_ID IN (@pid))
When J.T. mentioned that they will have to optimize the query because it ran for 2000 milliseconds, Frank explained that it's already optimized and can't run any faster. J.T. updated the query to use an ISNULL and increased the run time to 52 milliseconds. It was a small, 3800% decrease.

This is the WTF for me in the whole story. As much of a know-it-all DBA nazi J.T. has been up to this point (not a bad thing mind you), optimizing queries *should* have been his domain. Frank's responsibility at this point should have been limiting to informing J.T. what he's trying to get out of the query, and J.T. should have provided that for him. Not to mention, there should have been some discussion between the 2, with J.T. designing the table based on the input from Frank. Namely, Frank shouldn't be determining the indices to be used for the tables (and in some cases, not even the specific type, though he should be providing useful clues to help J.T. determine the best data types to use).

2006-10-06 Reply Admin

Anonymous:
Anonymous:
Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)
Come on, this IS a troll. Nobody could be THAT clueless!
OK, I'll bite: why in hell are you paying big bucks and a server for a RDBMS if all you want is a flat file? Get enterprisey and store everything in a single XML file instead :-P Imagine all thosepaid hours spent at coding the I/O routines and XML searching methods! You could make meeelions!

That is why people who "learn" sql by using MySql should be kept away from the keyboard.

2006-10-06 Reply Admin

The dev is probably a PHP-tard, so no cast is necessary. I find this sort of thinking to be very common across the PHP developer-base.

Ahem... Some of us PHP-tards know how to build proper databases... Even some of the self-taught ones. Personally, I take quite a bit of pride in what I create.

2006-10-06 Reply Admin

Anonymous:
Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)

Wow, this guy has got to be on the MySQL development team. "Foreign keys are bad and complicate the application unnecessarily." "The application, not the database, is responsible for data quality and should prevent you from storing nonsensical dates like November 31st, 2006."

As for turning of foreign keys and other constraints in the production database: Can you say, with 100% certainty, that you have tested every possible test case, run every scenario, and thought of every possible contingency that may appear in a production environment. No, you cannot. Ever. Database constraints, in comparison to any reasonably sized application, are quite a simple system for maintaining data quality, consistency, and coherency. An application is not directly suited to this sort of work. The economics of specialization require you to have each actor perform the work that it is best able to perform in comparison to all other actors. Ergo, data consistency issues should be handled primarily by the database.

Additionally, the "once and only once" mandate of computer science requires that this work be done once and only once. Now, given that several organizations use the database as an "intergration layer" (Hohpe & Woolf. EIP. Addison Wesley Professional) between applications, this work needs to be performed once across the entire organization. Thus, the database is a natural choice. Additionally, what if your team is rock-star, but the team across the hallway sucks? Would you want to share the same pool if you knew they were pissing in it? But if you could prevent them from pissing in the pool without modifying your app, would you? Yes.

JustThat · 2006-10-06 Reply Admin

Anonymous:
Alex Papadimoulis:
I'll leave you with one of the last things that Frank sent over for review. It was the following query:
SELECT Product_ID, Product_name, 
case WHEN 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) is null 
then 0 
ELSE 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) 
end 
as TotalDollarAmount FROM Requests WHERE (product_ID IN (@pid))
When J.T. mentioned that they will have to optimize the query because it ran for 2000 milliseconds, Frank explained that it's already optimized and can't run any faster. J.T. updated the query to use an ISNULL and increased the run time to 52 milliseconds. It was a small, 3800% decrease.
This is the WTF for me in the whole story. As much of a know-it-all DBA nazi J.T. has been up to this point (not a bad thing mind you), optimizing queries *should* have been his domain. Frank's responsibility at this point should have been limiting to informing J.T. what he's trying to get out of the query, and J.T. should have provided that for him. Not to mention, there should have been some discussion between the 2, with J.T. designing the table based on the input from Frank. Namely, Frank shouldn't be determining the indices to be used for the tables (and in some cases, not even the specific type, though he should be providing useful clues to help J.T. determine the best data types to use).

If we worked together on the project, you'd be right. 100%.

However, Frank was a developer who worked for a vendor whose job it was to deliver a complete product including the database.

My job is to protect the database environment from bad code. I was not consulted during the design or development of the application. I only came across the inefficiencies after the application was near go-live.

JustThat · 2006-10-06 Reply Admin

JustThat:
Anonymous:
Alex Papadimoulis:
I'll leave you with one of the last things that Frank sent over for review. It was the following query:
SELECT Product_ID, Product_name, 
case WHEN 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) is null 
then 0 
ELSE 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) 
end 
as TotalDollarAmount FROM Requests WHERE (product_ID IN (@pid))
When J.T. mentioned that they will have to optimize the query because it ran for 2000 milliseconds, Frank explained that it's already optimized and can't run any faster. J.T. updated the query to use an ISNULL and increased the run time to 52 milliseconds. It was a small, 3800% decrease.
This is the WTF for me in the whole story. As much of a know-it-all DBA nazi J.T. has been up to this point (not a bad thing mind you), optimizing queries *should* have been his domain. Frank's responsibility at this point should have been limiting to informing J.T. what he's trying to get out of the query, and J.T. should have provided that for him. Not to mention, there should have been some discussion between the 2, with J.T. designing the table based on the input from Frank. Namely, Frank shouldn't be determining the indices to be used for the tables (and in some cases, not even the specific type, though he should be providing useful clues to help J.T. determine the best data types to use).
If we worked together on the project, you'd be right. 100%.
However, Frank was a developer who worked for a vendor whose job it was to deliver a complete product including the database.
My job is to protect the database environment from bad code. I was not consulted during the design or development of the application. I only came across the inefficiencies after the application was near go-live.

Let me add to that. The reason I looked at the SQL code in the first place was because the application, under load, was timing out because the query was taking too long when performed by a few hundred users at the same time. We eventually came up with a totally different way of solving the problem that was my idea and the developer's implementation. So, we did eventually work together to make the end result better.

Pap · 2006-10-06 Reply Admin

"It was a small, 3800% decrease."

So how do you decrease something by more than 100%? That's quite a trick to have it complete before you start running it!

2006-10-06 Reply Admin

Anonymous:
Those who are blindly mocking this statement need to learn a thing or two about engineering, and about database theory.
Keys imply both an index and a constraint. Indexes generally make queries faster at the expense of insert/delete time and space. It is an important engineering tradeoff to determine exactly where this tradeoff makes sense and where it doesn't (hint: it often doesn't). Constraints limit data integrity - for many applications, you already know you will not fail a constraint check, and for foreign key cascading behavior, can provide such behavior yourself more efficiently since it doesn't have to be generic. Why, then, add constraints that should never be hit? Yes, some apps have such high requirements for reliability that protecting your data from even the application is a requirement, but I bet most of you aren't working with apps that require that level of reliability guarantees.

Because it makes debugging your application something close to beiong possible?

2006-10-06 Reply Admin

"Wait a minute, you mean there is a company out there with a real DBA that has enough power to actually enforce the rules? " [snip]

Can I work there?

2006-10-06 Reply Admin

Anonymous:
Anonymous:

Since keys create overhead (sometimes a lot), they should not be mandated or even favored by any standards. Frankly, I'm of the philosophy that keys are almost always bad. Data integrity is the responsibility of the app, not of the database. (Indexes, of course, are usually necessary for large tables, but even they should be used sparingly.)
Those who are blindly mocking this statement need to learn a thing or two about engineering, and about database theory.
Keys imply both an index and a constraint. Indexes generally make queries faster at the expense of insert/delete time and space. It is an important engineering tradeoff to determine exactly where this tradeoff makes sense and where it doesn't (hint: it often doesn't). Constraints limit data integrity - for many applications, you already know you will not fail a constraint check, and for foreign key cascading behavior, can provide such behavior yourself more efficiently since it doesn't have to be generic. Why, then, add constraints that should never be hit? Yes, some apps have such high requirements for reliability that protecting your data from even the application is a requirement, but I bet most of you aren't working with apps that require that level of reliability guarantees.

I actually see this as part of the source of many software failures. Regardless of how pointless the developer thinks the data is, the client may think of it as the most important thing in the world. It doesn't matter if the client is a huge multi-million dollar conglomerate or the mom and pop store down the street, thier data is thier businesses life blood. They each need to know exactly how much thier sales were each day, what the value of each transaction was, or the name of thier outside contacts. The actual dollar amount doesn't matter here. Even the local comic book collector with his stuff in boxes in the basment thinks this data is important enough to save, and the reliability of the data is highly important to him.

So yes, every application needs this type of reliability guaruntee.

2006-10-06 Reply Admin

OMFG!

Just stay the f*ck away from ANY database ever created... for the rest of your natural LIFE!!!

(>_O)

please... for the sake of humanity...

stay away!

*...runs away from the reality distortion field being generated by this WTF of WTF's!!!...*

2006-10-06 Reply Admin

JustThat:
If we worked together on the project, you'd be right. 100%.
However, Frank was a developer who worked for a vendor whose job it was to deliver a complete product including the database.
My job is to protect the database environment from bad code. I was not consulted during the design or development of the application. I only came across the inefficiencies after the application was near go-live.

Ah, well that makes a whole lot more sense. The story didn't bring that point across. And, given that, I'd agree with your position.

2006-10-06 Reply Admin

Ah. DBAs. The most underappreciated overworked people in the world. . .

For the competent developer, there's NOTHING better than a competent DBA.

For the competent developer, there's NOTHING worse than an incompetent DBA.

For the incompetent developer, there's NOTHING worse than a competent DBA.

And when you combine an incompetent developer with an incompetent DBA you end up wreaking havoc that shall plague the world until the end of time.

** Martin

Manager of the Data Dump

Leave a comment on “Manager of the Data Dump”