I'm brought in as a consultant to help modify a 3rd party real estate system to take data directly from MLS FTP pushes.  Okay, no big deal...just writing an import into whatever layout this database is in, right?

Ignoring the mostly horrible database layout, I do this.  I added a few indexes to increase search speed from 7 sec to .02 and all is good...with the test data of 50 records anyway.

So then the first MLS push comes.  10,000 records.  No problem whatsoever with the import.  10,000 properties are in the database and due to their horrible layout, the "fields" table now has 3 million records (roughly 30 per property).  Cool, the import worked.  My work here is done!

Not quite...

I get a call saying that the advanced search keeps timing out.  This isn't what I was hired to do, but considering what the rest of their code and database was like I figured I could fix it in a few minutes.

So I put an echo on every SQL statement running through the system...it's still in testing anyway.

I get 3 MILLION "UPDATE mos_hp_temp SET ...=..." statements on the screen.  Actually I only got 40,000 because the script timed out after that.

This is only the start of the problems...


- The system starts out by dropping the temp table.  Note that it's not actually a temp table, just a table called "temp".
- It then re-creates this temp table exactly like it was before.  But now it's empty!
- Okay, so user B now has an empty table where his search results will go.  Great!  Anyone stop to think what happened to user A who did a search 30 seconds earlier???  Oh look...user A now has user B's search results...or at least he will in 20 minutes...keep reading.
- Anyway...The code continues on by copying all the properties, agents and companies out of the 3 tables they are nicely stored in and puts them into this one HUGE "temp" table.  All of the records mind you, not just the ones you searched for.
- Remember how I said each extra field was it's own row in another table?  Well the script loops through this table and performs an update on every single row in that table to update the data in the temp table.  Works great if you only have 5 records.  When you have 3 million.....
- So now, 15 minutes later, we have an exact copy of our ENTIRE DATABASE, except it is all in one table.
- Wait, didn't they search for something?  Well let's go through each search criteria they put in and delete any records from the temp table that don't match it!  ONE AT A TIME!

Fortunately, my own goggles do work.

And I am SO, SO grateful because holy crap.

Some people just have no idea what they're coding -- while they're coding it.

I think my old boss wrote this.  If you had simply added that every field was stored inside a single text colum with an autoincrementing ID field, and the format of the data was tilde-delimited [and yes, some of the data has at least one tilde, how did you guess?], I'd know it was him.

Were they sentient enough to do:

for row in temp_table do
   ditchIfNotMatching(row, critera)

instead of

for criterium in criteria do
   for row in temptable do
      ditchIfNotMatching(row, criterium)

?

Hi, how can I see this website? I have HP installed and looking to hire somebody to add database.

Thanks.

anshell wrote:

Hi, how can I see this website? I have HP installed and looking to hire somebody to add database. Thanks.

Oh, uh, yeah,

CWolves knows a couple fine, fine, database people.

that anshell guy is an obvious spambot btw..

 Moderator's note: anshell's spam post deleted
 

Hey, the original developers were just trying to avoid the root of all evil: premature optimization.

;)

sepi

You're an obvious spambot btw..

Gee, a rare sighting - a doubly zombie thread.

Spectre:

Gee, a rare sighting - a doubly zombie thread.

Rezzed by the same dumbass.

 

Who took a year and a half to come up with "ur a spambot"....