- 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
Sounds like this project "bit" Aaron (where it hurts).
Admin
“WHERE clauses and other optimization techniques.”
Holy carp, I nominate this statement to be the biggest WTF of all time!
Admin
My thoughts...
Beaglesoft: completely incompetent at development. Must have been good at marketing to convince people to pay so much money for this crap.
The dentists: extremely fortunate. If an IT glitch on day one could dissolve the partnership, then they shouldn't have been working together in the first place...and it's great that they found this out before the partnership had had a chance to get off the ground. If the migration had gone well, they would probably have wound up with a tense, unproductive partnership.
Admin
Hey, a WHERE clause is an optimization compared to an unqualified SELECT *...
Admin
http://www.gendex.com
Interesting...
Admin
TLDR
Admin
So getting the records was a bit like pulling teeth then?
Admin
http://www.ddaportland.com/
Admin
I'm not sure what a blessed bottom-feeding fish has to do with it... wait, you're exactly right!
Admin
Certainly on par with the biggest WTF I’ve ever personally witnessed. When consulting with the “DBA” of a client that was disappointed with the database engine’s performance on queries against several million-row tables: “Index? What’s an index?”
Admin
http://patterson.eaglesoft.net/index.htm
Admin
the worse thing is, this was happening all the time. I've seen applications written in PowerBuilder that loaded more than 100.000 rows across the network just because the programmer never heard of the GROUP BY clause, or in others cases, having a form that just positioned the row in the entire resultset instead of using WHERE to retrieve just the record needed
These programmers just used the "graphical assistant" to generate SQL code, They even hadn't ever noted the "SQL Syntax" second tab in the editor!!!
Admin
WTFs aside, I just like the name BeagleSoft. Now, if it was TerrierSoft or PoodleSoft, I might have kicked the software to the curb. :)
Admin
And if it was ShitsuSoft or MongrelSoft, I'd have brought it just for the comedy factor.
I think that'll be my new busines model.
Admin
I second that.
I guess "WHERE clauses and other standard database usage techniques" would be a SLIGHTLY more correct term?
I find it funny that this software BS caused all 4 to completely go berserk and decide to never work together again. I mean I could understand them ripping teeth out of that company's mouth, but why at eachother? Guess there was lots of bitterness there already.
Admin
I guess this is what happens when you have a C/C++/Java developer in charge of the database stuff. They don't know much about SQL so they do the bare minimum and clean up the results in their language of choice.
I'm still confused on why the 4 dentists decided to merge in the first place. Sounds like they still had their original 4 offices. I guess it would be kinda cool that their patients could visit any office but I think most people would just stick to their original dentist in the first place.
I say the solution to this problem would be to just buy a huge office in the center of town and have all 4 docs in the same building. BAM! no more networking problems!
Admin
I ran into a similar problem a few years ago. A client asked me to look into why their calendar page was taking between 2 and 3 minutes to load.
The code looked something like this (psuedo):
For year = 1990 to 2010 begin For month = 1 to 12 begin For day = 1 to 31 begin open recordset "SELECT * FROM CALENDAR" Foreach record in recordset If record["date"] = month &"/" & day & "/" & year then 'output the record Next end end end
At the time of the problem, there were only 350 records in the calendar table. I threw away all of the code and rewrote it in an afternoon. The result was a page that loaded in under half a second.
Admin
Admin
My dad is a dentist and uses "Beaglesoft" to manage his practice. As the family "tech support" person I've had a few chances to work with it and this story doesn't surprise me a bit. They must have some great marketing people over there at Beaglesoft.
Admin
Is all medical/dental practice software necessarily written poorly? Currently I'm working a contract at a company that puts out multiple practice A/R packages. The app they have me maintaining and extending is insanely stupid.
I'm a C++ programmer. You'd probably think it's insanely stupid just to write A/R software in C++ these days, and you'd be right. This app is ancient. Much of it looks like it pre-dates M$ MFC, and some of it even looks like plain C (or it had a programmer who was obsessed with sacrificing the strength of C++ for the sake of looking like C). A few years ago they migrated this thing from a semi-relational DB to SQL Server, and the result manages to avoid all of the advantages of a relational system while coupling the DB structure to code so closely that even adding a single stinking field to a table can require a full day(!) of coding. Almost literally, you cannot change one significant feature in this monstrosity without breaking two other features.
Admin
There is no need for a "Where" clause as long as you use the "Top" statement. Concerned about returning too many records? Just write code like "SELECT TOP 10 * FROM PATIENTS (nolock)". The nolock is for advanced dentists only.
Admin
WHERE clause... {shakes head}
I second the nomination for biggest WTF.
Admin
I find it worse to ignore JOIN clauses. Well normalized data has at least two tables. A useful INNER JOIN key will reduce the rows to consider.
The 4 combined dentists should have been two tables: DENTIST & PATIENT. Create the DENTIST table & add a FORIEGN KEY PATIENT(ID) to it. Good relational skills make it easy to extend SQL databases.
Moreover, the DENTIST & PATIENT tables take less space than a combined ALL_PATIENTS table.
Admin
I once worked on a practice management application for a company that did medical exams for insurance and legal clients. My boss was longtime FoxPro programmer, and was absolutely excellent in the desktop database paradigm. But in the late 90's, VB and SQL server were the in thing, and so the decision was made to implement using them.
A common practice in FoxPro development is incremental searching in response to user input. It's very fast - you can search a few million records in a tenth of a second, because ISAM access like FoxPro is optimized for this kind of usage. The natural translation of that technique in into a SQL implementation was the iterative SELECTs with a LIKE in the WHERE clause. It worked fine for small sets of data, and rapidly became unworkable as clients and data were added.
I saw the problems with the way we were doing things, and I tried to warn my boss. He was a bit pigheaded, though, and we stuck with the paradigm he knew best. I left before the project ended - last I heard, there were lawsuits involved.
Admin
Yanks eh?
Admin
Now then
Admin
Yeah, sure, why bother with perhaps trying to find the right record. Just give them some results in whatever order the database seems fit to present, and shrug it off if the customer complains the patient record he just saved isn't in the databases Top 10. They asked for results, never for the result they were looking for, right?
Admin
Why did they try to merge when they didn't like or trust each other? The story says why: "everyone knows what brought the dentists together: the nationwide “denta-corps” that can out-price, out-service, and out-anything their small, family dental practice." They were losing patients to the large-scale dental offices, who used economies of scale to reduce costs and undercut the prices charged by the four dentists. So the dentists tried to do the same thing.
The mistrust made them break apart again rather than adapt to the situation for a while until BeagleSoft could produce a fix (or a different application selected, tested and deployed).
Admin
So each dentist can only have one patient?
Admin
Even worse, why would each keystroke trigger a database query? Even if it had a WHERE clause, that's still a waste. I could see it as being an AJAX-y sort of application where it would trim the names down in real-time as you typed the search query. But why would it keep re-running the query if you are working with the same data set.
Admin
Patterson EagleSoft Version 14.00 features enhanced electronic business tools equipping users to work smarter and increase productivity. New to Patterson EagleSoft is the eReminders feature, allowing users to send appointment reminders to patients through e-mail, text messaging or voicemail, along with eRA, an electronic version of the EOB form. Oh yeah, and we use WHERE now.
Admin
I'm certainly looking forward to seeing statements like 'select date() from dual where (1=1);'
Admin
I've run into a similar situation with a contractor. Our database stored several hundred "destinations" and tens of thousands of "sources" through a normalized structure of "Source", "Destination", "Location", and "Address" tables that referenced eachother; i.e. a destination was a linked set of destination, location, and address records, as a source was a linked set of source, location, and address records. Each of the destinations also had associated with it a distance number to indicate a radius of source coverage (small, on the order of 20 miles), and the address table was populated with gps coordinates that spanned the continental US and overseas territories.
The contractor was tasked with presenting a display which indicated for each destination how many sources were in its service area. They implemented the solution by first selecting the set of destinations in a script, then having the script iterate another query over each one:
SELECT count(*) from Source,Location,Address where locationId = Location.id and addressId = Address.id and round(3963 * acos(sin(Destination.Latitude/57.2958) * sin($Origin.Latitude/57.2958) + cos(Destination.Latitude/57.2958) * cos($Origin.Latitude/57.2958) * cos(Destination.Longitude/57.2958 - $Origin.Longitude/57.2958)),1) <= $Radius
Now, forgetting for a moment the fact that I hadn't designed the database to use homogeneous ids for these tables (it was my first db job ever), the database was not too shabby, with indexes and all those other fancy things. Well, the report took several minutes to complete - enough minutes that the web app server would time out on the response. The contractor pointed the finger at our database server, suggesting that it was too slow and "it works fine on our end".
Well, after looking at the code I came to some different conclusions - first and foremost that the working set of the selection became debilitating large very quickly from the anonymous cross joins of tables and evaluation of a nontrivial where clause on a set of data that vastly exceeded the number of expected results (the average destination covered roughly 3% of all sources) This was further exacerbated by the anonymous cross joins introducing row combinations into the working set that were invalidated by the first two conjunctions in the where clause only after the working table had been created. To put it in perspective, with about 30000 sources, locations, and addresses, the number of rows from the resulting anonymous cross-join is 30000x30000x30000 - that's around 270 TRILLION rows, with twenty or so columns to a row. I don't care what hardware you have or how l33t your my.conf is, MySQL takes a long while to build and process that temp table.
I rewrote the script to calculate a bounding box in gps coordinates that would circumscribe the circle formed by the destination's radius, and introduced those bounding parameters as parameters to the selection query. This alone reduced the amount of wasted sources in the selection from 97% to around 3-5%. I then trimmed the fat in the working set by replacing the anonymous cross joins with left joins with appropriate conditions. The report then executed in a few seconds as opposed to tens of minutes.
I remember this story as a vivid example that no matter how far software and hardware evolve, there is always someone programming with a sufficient degree of cluelessness and laziness to bring a system to its knees.
Admin
AAAAAARRRRRRGGGHHH!!! These pictures...
Admin
So the company was started not long after the death of Charles Babbage? It must have been started by one of his offspring.
125 years worth of C++ code....I can only imagine what that looks like. I would especially be interested in the part of the code that was written before C++ was invented in 1983.
Admin
I have to maintain a Win95 machine for a diagnostic software suite for my mechanic. Win98SE was already out at the time, and there still isn’t a newer version available. Part of the problem is the card that provides the connection to the car. It requires an ISA slot on the motherboard, and they don’t have a new version of that either.
Admin
I'm surprised nobody has pointed out the other major WTF:
"For example, a wand-shaped oral camera required a $7,000-per-site “camera driver,” in addition to the ungodly amount the camera cost in the first place. When Aaron plugged the camera into his laptop (which didn’t have any Beaglesoft software running), it was recognized as a plug-and-play camera and immediately started streaming video."
So what did this driver do if it wasn't necessary to stream video? Or did Microsoft include it with XP?
Admin
Classic WTF, keep up the brillant writing. It hit very close to home with regard to our experiences with other software vendors... "Sure, that will work with up to 200 nodes, it won't be a problem!" I'm guessing that meant "#define MAX_NODES 200" rather than that they actually tested anything...
Admin
But each patient can have N dentists. That's democracy.
Admin
If I had the power, I'd give you a teaching gig. That is genius.
Admin
I once put an ad-hoc query page in as part of a web app, where authorized users could type in SQL statements and see the results. (With the ability to do deletes and updates highly restricted, of course.) I refused to accept a query without a "where" clause. For the rare case where it was really logical -- like wanting to dump a small code table --you could always type "where 42=42" or some such.
Still, even non-idiots get burned. On that same system I had a very complex query that joined about half a dozen tables. It ran fine in test, but as the size of the database grew in production it gradually ground to a halt. It turned out that although I had a perfectly good index that should have allowed the SQL engine to read just a few dozen records, it didn't use the index but instead did a full file read on a very large table. Apparently the query was just too complex for the optimizer to figure out. I ended up re-writing the query just to trick the optimizer into coming up with a more intelligent plan.
Moral of the story: Always always always build a realistic-size database to do performance testing, and use EXPLAIN on every query before you release it to production.
Admin
It's easy to imagine how this happened (and will continue to happen -- it should get its own anti-pattern). You start out with a desktop app, single-user, and it reads its data from flat files on local disk. The data is small, so you just read the whole thing into memory. Winnow a selection down as the user types a name? No problem, you're just dealing with an in-memory collection.
Later, someone says "Let's make this an Enterprise app. All we have to do is replace the local flat file access with SQL access." So, in a straightforward manner, the developer(s) replace code to read the entire "patients" flat file into memory with a "SELECT * FROM Patients". Simple! You can still use the same collection object that was storing the patient records in memory under the old file-based system.
A colleague of mine made this mistake a few years ago. The difference is, we caught it as soon as we connected to a remote server. Which we did early in the development cycle, long before QA got hold of it and long, long before a customer ever saw it.
Admin
Also, small vendors tend to disappear without warning, leaving you with both a business-critical and an unsupported product. In this case however, the issue appears to be a simple lack of basic competency in using SQL.
The Beaglesoft company motto is probably "You can buy better, but you can't pay more".
Admin
Admin
Actually only one patient can have N dentists; everyone else is limited to whomever is left, until you run out of dentists and then they get no one.
Admin
I know of a number of companies who could easily use that motto. Their general marketing approach seems to be "You get what you pay for; Our software costs twice as much as the competition, so you're clearly getting the best system if you empty your wallet with us."
Admin
Err, call me confused, but what is it about C++ makes it insanely stupid to use for A/R?
Admin
There is a conventional wisdom that a computer project can have two of fast, cheap and correct but not all three. Apparantly, this only managed one.
Admin
Admin
Ahh... dentists... (shakes head). I think there isn't an angrier, more maladjusted group of professionals on the planet.
But on to the WTF, I have to agree with the poster above that this sort of thing is endemic to software written for 'vertical markets'; one or two companies establish a foothold early on (as this one probably did in the 80s), and then manage to become de facto standards, since markets like this take a LOT of work to break into.
Also, software companies for this sort of market are often founded by former professionals IN the market, rather than people experienced at running software companies. Thus the company founder doesn't really have the knowledge to tell a good programmer from a bad one, or what sorts of skills are really needed. These programmers might have been good at client-side GUIs, but obviously had almost no knowledge of server-side applications or databases.
It's really a shame, since good database design can reduce the complexity of client-side code significantly. Most business problems are data management problems, and to solve them in client-side ad-hoc application code often becomes a huge mess. I mean... 100,000 records is nothing to manage in a database, but try to shuffle it around to every node for every single action...???? The performance problem is only the BEGINNING of the WTFs in this case. Imagine the concurrency problems and data integrity problems sure to follow. Of course, I'm also willing to bet none of these developers ever heard of a foreign key constraint, much less a view or a trigger. It wouldn't even surprise me to find that the whole database is one large table in 0th Normal Form. I have seen that.