• DentalGuy (unregistered)

    I work for one of Beaglesoft's little competitors, and we do use WHERE clauses and client-side caching of the patient list.

    The funny thing is that another of Beaglesoft's competitors (actually the one with the greatest market share) had exactly the same type of problem with their "Enterprisy" product in 2004. It seems that both of these companies developed and tested their distributed solutions with all of the computers on the same 100MB LAN. It was slow, but not any slower than their original version, so no one noticed.

    Four years later, and although their have been some improvements it is still not technically client/server. Once a patient is selected, all of that patient's data gets shoved through the pipe with all the processing done at the client.

    But hey, if the had figured out how to do it right I wouldn't have a job!

  • (cs) in reply to Antoni
    Antoni:
    http://www.ddaportland.com/

    The doctors link in the footer of the home page is broken, and then gives the nice "Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request."

  • (cs)

    Great article.

    The Beaglesoft name reminds me of the Beagle Bros from the Apple II days...

  • Nate (unregistered)

    I think you've outdone yourself this time, Alex and Aaron B. This is the most entertaining writeup, and most outrageous WTF, that I've seen in a very long time. Pretty much made my day.

  • (cs) in reply to Steve H.
    Steve H.:
    warmachine:
    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.
    How was this fast, cheap, or correct? I don't think they managed any.
    As no bugs are mentioned, just abysmal performance and high price, it must be assumed that the program didn't make any mistakes. Correctness is a computer engineering term distinct from performance. Missing labels in DLLs is incorrect but, as users wouldn't notice these, doesn't really count.

    Mind you, it can be argued that it's so far short of fast, this counts as a double failure and cancels the correctness. Ye flipping gods, full table scans, full result sets and no client caching.

  • (cs) in reply to Outlaw Programmer
    Outlaw Programmer:
    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.
    Phew, good thing I work in C#!

    Seriously, though, what are you suggesting, that PHP programmers have better database management skills, or just that every business product needs a DBA?

    Working with sequentially-accessed instance-based class hierarchies and working with set-based relational data are two very different skills, I grant you that, but there's no reason a person can't have both skills. Being a C++ or Java programmer is hardly an excuse for sloppy architecture.

  • NothingNewUnderTheSun (unregistered)

    Oi! I've come along behind those guys (or at least it seems like it) - Lots of unhappy campers and then I said "must be something wrong with your sproc calls..." only to be asked, "what's a stored procedure....?" Apparently, of the 10 "programmers" working on the project, only one actually knew how anything worked other than the GUI and he wasn't familiar with stored procs (on a SQL Server no less!!!). 15 minutes later (took a while to browbeat the "programmer" into letting me see the "code") what was taking, literally, 60-90 minutes to run was returning results in < 1 second.

  • James Schend (unregistered) in reply to Andy Goth
    Andy Goth:
    dlikhten:
    I guess "WHERE clauses and other standard database usage techniques" would be a SLIGHTLY more correct term?
    If I write a SQL textbook or teach a class on it, I will do my best to keep it secret that WHERE is optional, and I will wait until its use is fully ingrained before ever presenting a SELECT statement that doesn't use it. SELECT *without* WHERE would be in my "advanced topics" chapter/lesson, not the other way around. And if I write an SQL query construction "wizard," I'll make it pester and taunt the user if he neglects to supply a WHERE clause, much the same way many email clients bother the user when he tries to send a message with no subject.

    Idiot programmers are too clever for you. They'll just add "WHERE 1=1" at the bottom of every query.

  • Spudley (unregistered) in reply to Andy Goth
    Andy Goth:
    If I write a SQL textbook or teach a class on it, I will do my best to keep it secret that WHERE is optional...

    If I'd designed SQL, I would have made sure that WHERE clauses were mandatory. Making them optional has made a handful of lucky queries marginally shorter... at the expense of producing some truly horrible systems.

    And I know plenty of inexperienced developers who've had an "accident" with their data by forgetting the WHERE clause on a delete statement. It's not a mistake (m)any of them make twice, but they always wish it'd thrown an error rather than just deleting the entire customer table.

  • Only I Need to Know (unregistered)

    The real WTF: (1) Where was the test servers, capacity planing, and load tests performed before the actual migration? (2) Where was the user acceptance testing and system load testing after migration before go-live?

    The article made is sound like the first time for migration was that weekend, and they just crossed their fingers and hoped it worked. I understand it's 4 dentists and small offices, but the consultant should have been able to avoid a lot of unhappy clients with some planning and prep work. If you trust the vendor alone, you will get burned.

  • Michael (unregistered) in reply to akatherder
    akatherder:
    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.
    Not necessarily. If it's a name search, then adding a letter will only match a subset of the previous set, so you hit the database once for the first letter, then filter the rest yourself, keeping them in memory until the user changes screens.
  • mal(adjusted) (unregistered) in reply to rycamor
    rycamor:
    Ahh... dentists... (shakes head). I think there isn't an angrier, more maladjusted group of professionals on the planet.

    I'll see your dentists and raise you some cab drivers...

  • Michael (unregistered) in reply to GalacticCowboy
    GalacticCowboy:
    Cloak:
    GalacticCowboy:
    Andrew:
    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.

    So each dentist can only have one patient?

    But each patient can have N dentists. That's democracy.

    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.

    So it's Capitalism?

  • Jeff (unregistered) in reply to ParkinT

    Having worked in the practice management software arena, I guess I'm not too surprised. By the time I landed in the industry at one particular company, much had already been cleaned up.

    But I did see queries that many *'s in them (nobody has ever convinced me that it's a good idea), nested cursors, transactions that included SELECT statements, and a host of other performance killing code.

  • Jeff (unregistered) in reply to Michael 
     GalacticCowboy:

    Cloak:

        GalacticCowboy:

            Andrew:
            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.



        So each dentist can only have one patient?



    But each patient can have N dentists. That's democracy.



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.

    So it's Capitalism?

    You seperate dentists (or nurses, etc) from patients, and use an xref table to link them, allowing for any kind of combination. Patients can see more than one actor (dentist, nurse, lab) and actors can have more than one patient. Beautiful world we live in.

  • Jason B. (unregistered) in reply to Steve H.

    There is some benefit to writing a business app in a language that makes it extremely simple to create windows and controls (VB .NET/C# .NET) versus MFC C++ or <shudder> pre-MFC C++. You can spend more time on business logic and less time on the interface.

  • (cs)

    I'm pretty sure I've seen this or a very very similar dentistry practice management software in action at a dentist office that I only visited twice on the "rich" side of town. They charged a shitload for all of their various services, and tried to upsell their clients on teeth whitening and such, but the real gem was their software system when I first saw it in action.

    The first time I went there, it was Windows 2000 machines, and the assistants had to speak into a mic to record a number to rate the dirtiness of every tooth in your mouth. This was around 2004 as well, and the voice recognition was of course horrible. The assistant kept getting pissed at the computer, even rebooted it once while explaining to me that they were all having "real problems" with their machines that day. Well no shit, sherlock! You're using Windows to run a medical practice - and not just for the billing stuff! The next time I went back, which wasn't until this past spring, they were no longer using the voice recognition, but they were now running Windows XP, streaming audio off of Live365, and the receptionists all had their fancy little headsets like the floor sales people at Best Buy and similar retail stores now have.

    I realized then that my money was being wasted. I won't be going back to that dentist.

  • (cs) in reply to Cloak
    Cloak:
    lizardfoot:
    http://www.gendex.com

    Interesting...

    AAAAAARRRRRRGGGHHH!!! These pictures...

    The floss ... it does nothing!

  • (cs) in reply to James Schend
    James Schend:
    Idiot programmers are too clever for you. They'll just add "WHERE 1=1" at the bottom of every query.
    That's assuming that their keyboard is up to the job.

    BTW, this particular WTF is an awfully good incentive for Enterprisey (dentists? Enterprise?) companies like Beaglesoft to outsource their technical work to <pick subcontinent of choice>. At least for brain-numbing "skillz" such as SQL. They couldn't do much worse, could they?

    Hah! Take that, C/C++/Java haters!

    Mind you, a decent C/C++/Java compiler would of course reject this silly "1=1" crap. Even if it was spelled correctly, as "1==1", you'd still get a warning. Or, presumably, in Java's case, a "PossibleAbuseOfAliasedBooleanWouldYouLikeToCheckThisWithAnAdultSupervisorException..."

    Java is so much more advanced that way.

  • Franz Kafka (unregistered) in reply to cavemanf16
    cavemanf16:
    I realized then that my money was being wasted. I won't be going back to that dentist.

    What do you care? As long as the price is ok and your teeth are clean.

    Aaron:
    Outlaw Programmer:
    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.
    Phew, good thing I work in C#!

    Seriously, though, what are you suggesting, that PHP programmers have better database management skills, or just that every business product needs a DBA?

    Well, yeah. They don't all need a full DBA, but a consult from one would catch a whole lot of these problems early.

  • Matthew (unregistered) in reply to Marnen Laibow-Koser
    Marnen Laibow-Koser:
    Hey, a WHERE clause *is* an optimization compared to an unqualified SELECT *...

    Bah! Only losers go for the low hanging fruit. I'm sure the code that parsed the thousands of results for each query was highly optimized. There was probably some inline asm in there.

    God, I love it when programmers get obsessed with trying to pluck the highest fruit on tree... while passing up the big juicy ones right near the bottom. Oh, wait, no, I hate that.

  • Matthew (unregistered) in reply to Franz Kafka
    Franz Kafka:
    Well, yeah. They don't all need a full DBA, but a consult from one would catch a whole lot of these problems early.

    Oh, come on. Any programmer worth his weight in salt knows how to use a where clause and probably even JOIN a few tables. Many even know how to apply indexes. Maybe they're not going to always write the most optimal queries, but it is generally enough for a programmer to go by the "make the database do as much of the work as possible" principle. Even if the programmer doesn't know a lot about SQL at first, he'll learn pretty quick just by keeping to that principle.

    If you have to call in a DBA "consultant" to get basic queries right... something is wrong with the programmers.

    -matthew

  • Bill at The Phone Company (unregistered) in reply to DentalGuy

    It's amazing how much database software development gets done on LANs and never tested across wide area or on wimpy computers. I work for a telecom company, and a lot of my customers have had applications that worked just fine when they did everything at their main office or using Citrix to do remote screens, but when tried to do a distributed helpdesk with one office in California and one in Asia or Europe, they'd find that their application software did a dozen little SQL queries per screen, and they were hauling all the data back and forth across the Pacific, 250ms latency, and trying to fit it onto a < 1Mbps connection. If things go well, that's only a few extra seconds per screen, but sometimes applications fail badly and turn into minutes per screen.

  • Franz Kafka (unregistered) in reply to Matthew
    Matthew:
    Franz Kafka:
    Well, yeah. They don't all need a full DBA, but a consult from one would catch a whole lot of these problems early.

    Oh, come on. Any programmer worth his weight in salt knows how to use a where clause and probably even JOIN a few tables. Many even know how to apply indexes. Maybe they're not going to always write the most optimal queries, but it is generally enough for a programmer to go by the "make the database do as much of the work as possible" principle. Even if the programmer doesn't know a lot about SQL at first, he'll learn pretty quick just by keeping to that principle.

    If you have to call in a DBA "consultant" to get basic queries right... something is wrong with the programmers.

    -matthew

    No, that's a clue that you have a decent process. The DBA is there, in part, to tell you to actually use indexes and in part to tell you why your query isn't going to use that index. He should be around to review your schema and queries during the design phase so you don't have to redo the schema when there's already customer data in there.

  • SomeCoder (unregistered) in reply to Franz Kafka
    Franz Kafka:
    Matthew:
    Franz Kafka:
    Well, yeah. They don't all need a full DBA, but a consult from one would catch a whole lot of these problems early.

    Oh, come on. Any programmer worth his weight in salt knows how to use a where clause and probably even JOIN a few tables. Many even know how to apply indexes. Maybe they're not going to always write the most optimal queries, but it is generally enough for a programmer to go by the "make the database do as much of the work as possible" principle. Even if the programmer doesn't know a lot about SQL at first, he'll learn pretty quick just by keeping to that principle.

    If you have to call in a DBA "consultant" to get basic queries right... something is wrong with the programmers.

    -matthew

    No, that's a clue that you have a decent process. The DBA is there, in part, to tell you to actually use indexes and in part to tell you why your query isn't going to use that index. He should be around to review your schema and queries during the design phase so you don't have to redo the schema when there's already customer data in there.

    I agree that programmers should know the basics of SQL. And by basics, I mean knowing about indexes, keys, foreign keys, etc.

    But I would also say that any non-trivial database app needs a DBA. Your programmers shouldn't be expected to write all the code + all the SQL stuff needed for the app.

    We didn't have a (dedicated) DBA at my last job and it was rather painful.

  • MyDogHasFleas (unregistered)

    ummmm... there actually IS a BeagleSoft

    http://www.beaglesoft.com/index.htm

    they specialize in time synch software

    maybe check your faux names via this newfangled thing we call Google?

  • Barf 4 Eva (unregistered) in reply to Marnen Laibow-Koser
    Marnen Laibow-Koser:
    Hey, a WHERE clause *is* an optimization compared to an unqualified SELECT *...

    if it's sql server 2005, that's an excellent chance to get some serious bookmark lookups, however from the article, that is the least of their concerns... Probably need to index the tables first. :P

    I'd really love to see the DDL for this sucker.. Just earlier today I had the joy of looking at a database that only had clustered indexes on identity columns as primary keys, and no other non clustered indexes whatsoever, nor were there any foreign key constraints to help you determine how the hell the relations were interconnected... Seriously, no fk constraints, and no indexes on what I'm guessing were the fks? :P

    waiting for someone to push the red button that says on it "I believe fk constraints are a boon to performance and thus do not implement them!"........ :P

  • Barf 4 Eva (unregistered) in reply to GalacticCowboy

    lol... I think he just stated it backwards so it just needs to be flipped around, WHICH, after rutherford, price, atkinson, strickland merged together, would still be wrong and would probably need to be M:N relationship. :P

    Although it is funny and ironic that he follows up his explanation with the line "Good relational skills make it easy to extend SQL databases." :P

  • captcha: (unregistered)
    mal(adjusted):
    rycamor:
    Ahh... dentists... (shakes head). I think there isn't an angrier, more maladjusted group of professionals on the planet.

    I'll see your dentists and raise you some cab drivers...

    I gotta agree with this guy. The way I see cabs driven around here makes me appreciate the bus.

  • (cs) in reply to Andy Goth
    Andy Goth:
    “WHERE clauses and other optimization techniques.”

    Holy carp, I nominate this statement to be the biggest WTF of all time!

    The real WTF is that this is not unusual at all. On a project I had the misfortune of working for, they did exactly the same thing:

    SELECT * from person

    Over a WAN.

    And wondered why it didn't work. The project took years to develop and less than 1 hour to crash and burn. I could have fixed it in five minutes with that magical WHERE clause. Thats not the only one. On another project, I noticed a lot of full tablescans on a multi-million row table. Looking at the query, the developer had actually managed to use a WHERE clause, but forgot to create an index on the column. When I asked him why he didn't create an index, he basically said: "Its too complex".

  • (cs) in reply to James Schend

    Favorite quote from a computer class (from the instructor for Oracle CASE 5.1)

    "These case tools help you write bad code faster."

  • (cs) in reply to Spudley
    Spudley:
    Andy Goth:
    If I write a SQL textbook or teach a class on it, I will do my best to keep it secret that WHERE is optional...

    If I'd designed SQL, I would have made sure that WHERE clauses were mandatory. Making them optional has made a handful of lucky queries marginally shorter... at the expense of producing some truly horrible systems.

    And I know plenty of inexperienced developers who've had an "accident" with their data by forgetting the WHERE clause on a delete statement. It's not a mistake (m)any of them make twice, but they always wish it'd thrown an error rather than just deleting the entire customer table.

    I would have to agree with you. Consider the following: In Oracle, there is a special table called DUAL owned by SYS which contains exactly one row. It never queried directly, as the only column (DUMMY) contains the value 'X'. It is used entirely to return the value of an expression in a query, ie:

    SELECT sysdate-1 into yesterday from dual;

    SELECT round(1048576*1024/1000) into KB_ROUNDED from dual;

    If you use Oracle, you will see code like this everywhere. On day years ago, a client called saying they where having major problems and nothing worked with their database. After troubleshooting with Oracle support for hours, I discovered someone had inserted an extra record into DUAL, breaking every SELECT INTO involving it.

  • (cs) in reply to SELECT wtf FROM this;
    SELECT wtf FROM this;:
    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?

    It made Beaglesoft $7,000 + more than half the cost of the camera. That is the Beaglesoft sold the hardware, for a jacked up fee, plus the software for another jacked up fee.

  • DHager (unregistered) in reply to el_oscuro
    Andy Goth:
    If I write a SQL textbook or teach a class on it, I will do my best to keep it secret that WHERE is optional...

    I'd argue that it should be especially required for DELETE statements. Make people actually work to blow away all their data.

    Outlaw Programmer:
    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.

    Compared to what, PHP? You could argue the greater overlap of PHP "programmers" with SQL in fact makes WTFs more likely. (Dave Bowman:"Oh my god! It's full of injection attacks!")

    It's not about the language they use, but about whether they deserve to be called a "programmer".

  • Cale (unregistered) in reply to lizardfoot

    http://patterson.eaglesoft.net/

    Perhaps even more interesting?

  • asifyoucare (unregistered) in reply to Only I Need to Know
    Only I Need to Know:
    The real WTF: (1) Where was the test servers, capacity planing, and load tests performed before the actual migration? (2) Where was the user acceptance testing and system load testing after migration before go-live?

    The article made is sound like the first time for migration was that weekend, and they just crossed their fingers and hoped it worked. I understand it's 4 dentists and small offices, but the consultant should have been able to avoid a lot of unhappy clients with some planning and prep work. If you trust the vendor alone, you will get burned.

    Agree 100%. I've seen many projects fail badly because it was ASSUMED that the software performed well over a wide area network. The programmers AND the consultant should be shot.

    The consultant might have an excuse if he was brought in at the last minute, but that was not the case here.

    Who the hell codes these monstrosities any more? The mid-nineties were littered with the crappy two-tier designs. There was little excuse for them then and no excuse for them now.

  • (cs) in reply to halber_mensch
    halber_mensch:
    .... 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.

    That is an absolute gem of a quote!

  • Jared (unregistered) in reply to rycamor

    Wow, all this client/server side talk, running MS SQL over networks, makes me think how hard it must be for you guys trying to do this using MS products.

    May I make a suggestion, try using something that is inheritly scalable. Like JADE

    www.jadeworld.com www.jader.co.nz (my crappy example site)

    p.s. patterson.eaglesoft.net has way too many faulty links.

  • joe17301 (unregistered)

    Too late to add meaningful commentary to this wtf, but the Google ad for http://www.HorrorDentalSurgery.com is... er... interesting... thanks Google.

  • (cs) in reply to akatherder
    akatherder:
    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.

    Hey, the guys at BeagleSoft are professionals. Of course, they do not work with the entire dataset:

    var results = list();
var typed_so_far_filter = enterprise_filter();
var typed_so_far = patient_name_control.text();
for i = 0 to size(typed_so_far) {
    typed_so_far_filter.add_condition(
        "NAME[" + i + "] == '" + typed_so_far[i] + "'");
}
foreach record in sql_query("SELECT * FROM patients") {
    if(enterprise_filter_matches(record, typed_so_far_filter)) {
        results.append(record);
    }
}
  • diffuse (unregistered)

    I work for a company where we basically do the same thing but for doctors...

    I can't imagine a company not using WHERE clauses, that's a huge WTF.

  • Christian Vogel (unregistered) in reply to CodingForPretend
    It requires an ISA slot on the motherboard, and they don’t have a new version of that either.
    You'll be surprised to hear, that even today you can buy Core2Duo-mainboards with ISA-slots. They are great if you have some long-living machinery which still works fine but happens to have a ISA-card to control it. (Think about a huge lathe or milling cutter.)

    These boards often look funny, because it's the crowded "standard" motherboard with an added area that's void of any chips short of the ISA-bridge and connector.

  • david (unregistered)

    This is a classic MS Office Access upsizing conversion. The original local network Access application applies client side filters: the application upsizes to SQL Server without any changes.

    Worse still, anything that you would implement as a stored procedure converts to a client side filter even if the original application did not have it that way. So the original application, which was probably using ISAM indexes to find matching records, gets converted to entirely client-side filters.

  • Cloak (unregistered) in reply to operagost
    operagost:
    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.

    so they are on top of technology. justy 10 years too late.

  • Cloak (unregistered) in reply to halber_mensch
    halber_mensch:
    clively:
    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.

    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.

    you sure are a hero, and you sure are a voll_mensch

  • Cloak (unregistered) in reply to el_oscuro
    el_oscuro:
    I discovered someone had inserted an extra record into DUAL, breaking every SELECT INTO involving it.

    I didn't know you can do that.

    CAPTCHA: nulla, yeah, that's what it is

  • Cloak (unregistered) in reply to joe17301
    joe17301:
    Too late to add meaningful commentary to this wtf, but the Google ad for http://www.HorrorDentalSurgery.com is... er... interesting... thanks Google.

    Doesn't work in your area is all I get.

  • (cs)

    Years ago one of the first exercises in a telcom class was how to calculate the size of the circuit needed to handle your data. As this was back in the day of “Big Iron”, and only sending screens to terminals it was very simple. You looked at bits per screens, screens per day, etc. Then install a 9.6 circuit to your remote locations. I was working for a semi-major Main Frame software company in the Voice/Data area of the IT Department and you could go to the developers and actually get real numbers on this. Later with the advent of Client/Server and distributed computing all you would get from the programmers were blank stares. They had no idea what amount of data was sent.

    The PC became the norm with 10 baseT to connect them it was easy for the developers to test there applications. The only problem was what worked nice and fast locally would slow to a crawl when used remotely. To combat this we would set up a Lab with 2 routers and CSU/DSUs and simulate a T1 between sites. By controlling the number of channels turned on we could vary the bandwidth from 56Kb 1.544Mb. and monitor the bandwidth utilization.

    With the increase in bandwidth availability I suspect that now they just throw more bandwidth at the problem.

  • Cloak (unregistered) in reply to Jay
    Jay:
    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.

    realistic db size is VERY important because the explain thing will yield different results for different table contents. the statistics will be different according to the data. and query optimizer uses the statistics to see if a given index is selective or not.

  • (cs)

    After reading this, I now feel much less ashamed about my first contact with DBMSs, a PHP-based hobby database of fanfiction where I merely faked a JOIN (which I didn't know) by doing an unqualified SELECT on one table and then for each result a SELECT WHERE on the other...

Leave a comment on “Rutherford, Price, Atkinson, Strickland, and Associates Dentistry, Inc”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article