- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
:|
Admin
...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause
Admin
could someone please draw me a map back to sanity? i seem to be lost...
yeesh.
Admin
[:S] Please excuse me while I go speak with Ralph the driver of the porcelin bus.[:D]
Admin
wow, do people just sit down and try to obfuscate? I mean it's obvious he had to have enough mental faculty to know about all the aspects of the query he used. a momentary lapse of reason I suppose
Admin
select programmer.name from programmer inner join dba on
programmer.name = dba.name where programmer.name not in
(
select programmer.name from programmer inner join dba on
programmer.name = dba.name
where programmer.name in ('moron', 'excusemaker') or
programmer.language in ('foxpro', 'scriptlanguages')
)
Sorry, had to get in my shots at the end there.
Admin
Well obviously this would be better then:
Admin
Sombody please beat this guy with a rubber hose. There are too many things wrong with this query I won't point them out.
Admin
The fact that you know this means you probably weren't thinking it had any limits at some point in the past.
Admin
I have a sample query that was done in MS Sql Server. that is 600 lines.
it executes in less than 1 second. so it can't be Sql Server.
I still can't figure out why that query (the WTF) is slow.
Admin
I think I'm gonna be sick
Admin
Unfortunately, if you used a map provided by the writer of that query, you would have to circumnavigate the Earth to get to your next door neighbor's house.
Admin
<font size="2">looks like a case of 3am zombie syndrome, rather than a serious WTF.</font>
Admin
Easily worked around...
CREATE Table Systems_With_Some_Characteristic
( [Name] AS varchar(20)
)
-- create foreign key
INSERT INTO Systems_With_Some_Characteristic ([Name])
VALUES ('STC0017127')
INSERT INTO Systems_With_Some_Characteristic ([Name])
VALUES (...)
...
WHERE SMS_G_System_SYSTEM.Name IN (
SELECT [Name] FROM Systems_With_Some_Characteristic
)
Admin
At least he didn't use SELECT *[;)]
Admin
And my guess is that he doesn't need to do the inner join... unless he's really sure he needs records that are both in SMS_G and SMS_R, which I think are fucked up table names.
The "SELECT DISTINCT" is fun; you almost always start to see SELECT DISTINCT pop up a lot in queries after a system has aged for a while (nobody goes to the trouble of normalization).
Fun fun fun!
Admin
"and for my next number, I will have the database run laps around the moon after retrieving each record!"
Admin
At an interview recently, I was asked, "How many tables can you join in one Sybase query?"
I responded, "More than you should use, but it used to be 16. I don't know what the current limit is."
I wasn't impressed with the interviewer.
Admin
Ooh... trivia questions. My rule of thumb is to treat those kinds of limits like engineers treat max load limits on bridges and elevators: you can hit the limits if you absolutely have to, but it's better to stay well below them. [:)]
Admin
OMG! er... hum... ah.... OMG!!! ................. O. M. G. ! ! !
-dZ.
Admin
Microsoft SQL Server has no such limitation.
Admin
Indexing strategies and table row counts could fully account for the slow performance in this case. The number of lines in the query is not a good indicator. This query, although inane, would probably run well if there was indexes on the columns of interest.
Admin
The use of SELECT DISTINCT does not necessarily indicate poor normalization.
If I want to know which of the 50 states I have customers in,
select distinct state from customer
works fine. Typically I would use GROUP BY (obviously if want to know how many customers in each state) but SQL Server usually generates the same query plan.
I usually see SELECT DISTINCT when somebody has a bad join condition and can't figure out how to fix it.
Admin
Isn't funny how pretty much all WTFs are written in Microsoft products?
Admin
lol, gee, your like 1 order smarter.
Admin
I would imagine that if the query were analyzed and re-written with joins rather than many IN and NOT IN clauses that it woudl be much faster. IN and NOT IN seem inherently slow, and so are subqueries.
I know it's a different DBMS, but I once helped optimize an Access query from about 15 seconds down to 1.5 just by rewriting IN and NOT IN subqueries as good joins that did the same thing. I've seen similar optimizations make things faster in some brief experiences with Oracle, however YMMV.
Admin
Wow - Ouch, that hurts. I hope it isn't contagious.
Admin
[+o(] never seen anything horreble than this [8-)]
Admin
I look at this and think ... what on earth? and then I start to cry... [:'(]
Admin
and they did use a where clause
Admin
Then you haven't been paying attention.
Admin
I'll probably get flamed for this but: Without proper insight into the database design and the restrictions placed on the developers it's tough to say if the WTF is on the developers or the database.
Depending on the the structure or lack-there-of of the 2 tables and the business rules imposed on them, a query like this may be required to get the proper result.
Now I'm not saying that the IN list shouldn't have been stored in a table somewhere, but the developers may not have been allowed to alter the schema in this case.
Admin
If case sensitivity is turned on on the database and the field name "ResourceId" in the sub-select of the original query isn't a typo from Alex's anonymizer the query may be joining to a completely seperate (albeit poorly named) field.
I know there's a WTF in there but without more information we can't say where it is.
Admin
Now that's funny!
Admin
I love it when jackasses make fun of the intelligence of others and they can't even get simple contractions correct.
your != you're
Or perhaps you're (see? you are = you're) using the code from yesterday's wtf to remove apostrophes.
Admin
I remember when Unify only allowed a join of 5 tables... but wouldn't tell you not to use more.[<:o)]
Admin
I remember when Unify only allowed a join of 5 tables... but wouldn't tell you not to use more.[<:o)]
Admin
Yeah, but you are an engineer (at heart anyway, for all I know your are a musican who programs on the side). In the real world limits are meaningless, if it looks like it will fit, it must fit. Sql might specify a limit, but your text editor will let your enter as much as you want, so you enter as much as you want.
This is also the cause of a lot of buffer overflow security bugs. Engineers tend to forget that people are not reasonable about obeying limits.
I too am an engineer, which is why in college I would not get on one elevator - I never could be sure that 15 people wouldn't get on behind me and overload it. (Several people got Fs on one final because being stuck in an overloaded elevator is not an excuse to miss a final test) When the elevator opened I just waited for the doors to close, and it to move on a little before pressing the button again.
Admin
I beg to differ. I see much WTF-ery in Java, Perl and tools like Ant pretty regularly. Programming languages don't kill applications...$hitty programmers kill applications. Maybe I'll port "theDaily" to Java.
Admin
I think one trick engineers use is to tell people that an elevator can handle only 10 people and design it to work with 20. [:)]
Admin
I'd say the percentage of WTFs written in Microsoft products is roughly the same as the percentage of code-in-general written in Microsoft products.
Admin
Put another way, any language that is turing complete can be used to create a wtf. [:)]
Admin
I like the process of blaming everybody else before somebody finally goes to check what's actually happening...
Admin
About the abuse of "distinct" - sometimes businesses grow too fast and their IT teams are overstretched. Tool/table/db ownership is passed around amongst the IT team as some folks stop having the time to keep an eye on every single thing. Documentation never gets written because nobody has the time. Software systems grow and grow and nobody really knows what all affects what. So inevitably you wind up with seemingly innocuous changes or new tools or updates or whatnot that wind up fucking up the erstwhile integrity of your data, and once-unique keys become no longer unique, normalization gets lost, etc.
It's no excuse, but it happens in small IT shops, even when the average IQ is well above 150.
Admin
rogthefrog wrote:
s/sometimes/pretty much always/ s/small/nearly all/
In my experience, anyway.
Simon
Admin
Have you noticed that all SQL-related WTFs seem to be about ridiculously long queries? I mean, come on, an 175-line SELECT? That's bound to perform poorly and have at least one bug lost in there. Are you going to tell me MS SQL Server has no views? And even if it doesn't (MySQL had this limitation up to version 4.0), what about temporary tables? What about simulating the join in code? As a wise man once said, there are only solutions.
I did the same kind of mistake once with PosgreSQL. I was still learning, and though the server did support CREATE VIEW, I cheerfully ignored this feature. My little monster query was about 50 lines long, took 15 minutes to run and filled most of the server's memory. After breaking it in parts, the time went down to 30 secs, and the resulting data, once put together in PHP, was more accurate (the specs were odd, but that's a different story).
Morale: do your job and let the computer do its own. Then everything will work better.
Admin
The problem is you can never tell what the specs will be.. I worked on a project that was required to generate it's SQLs in runtime. the output was from a 10 line to 15 pages of SQL. However, with the help of a good DBA and some common sense it executed in reasnoble times (< 1 sec).
Admin
What crappy database is that then? Oracle can cope with 1000 items (now there's a number clearly pulled out of nowhere), and SQL Server doesn't advertise any limit so is probably limited to the maximum length of a SQL statement (default 256KB)
Admin
Oh shoot the tool maker. That makes sense.
What is it with people who confuse a dislike of Microsoft with an opinion that their technology is poor? Sloppy thinking.
SQL Server is actually a seriously good product, albeit one written by an arguably evil company.
Admin
Has anyone done any analysis on the average time it takes a DailyWTF thread to move from 'this particular piece of code is a WTF' to 'Microsoft is evil?'
Or is the real explanation that the Daily WTF forums are actually just being used in a techie version of Mornington Crescent?