Bob Hovious (2/27/2009)
I was referring to inadequacies of alleged IT professionals
My war story is that our development group used to have a guy whose entire job was to import data. I found this out after we went through some rightsizing and he was let go. When I reviewed his code it turned out to be slow AND inaccurate. He was driving it through a VB program with maximum RBAR. We just scrapped it all. Now things he used to run for a day are handled in under a minute, without duplications or omissions. No brilliance required, it was just a sad example of developers (who don't understand database) thinking that knowing how to do an INSERT or an UPDATE is all there is to it.
Looks like were migrating back in the direction of The Thread.
My current job, the prior DBA was okay for some things, but for some aweful reason, he built a whole series of DTS packages that import text files into tables in SQL, and it's all done through VBS. Open file item, set a bunch of variables to the values in the first row, build an SQL string that does an insert statement with those variables, move to the next line in the file, rinse, repeat. Over 7-million times on one of the files. Some of these take as much as six hours to import a few hundred thousand lines of text. No transformations, just move from text to SQL, same columns and everything.
He also wrote this wonderfully complex archive process for another database. The source database is third-party and subject to change without notice. The process queried the schema data for the tables, built a select and insert statement from sys.columns, dynamically, then dumped the data into a holding table using Select...Into..., then finally moved the data into the final archive tables, with the same structure as the primary tables.
One problem was that it didn't compare the table structure and modify the target table, which completely defeated the purpose of the dynamic SQL. Errored out at least once because the target table didn't have a column that the source table did. The other problem was that it's a daily job, and the run-time was up to just over 20 hours, and the data is growing. So I changed it to a simple "Insert ... Select * from ... for XML raw, type" and an insert into a single XML column. No dynamic SQL, no staging tables, but handles schema changes without problem. Took the whole process down to about 3 minutes, and this copy is moving the data across a network to a different database server. Uses about 10% more disk space, and is somewhat more complex to query (XQuery instead of just straight XML), though views help with that, but worth it for not tying up a whole server for 20+ hours per day.
On the other hand, he built a number of reasonably good databases, made sure there was adequate automatic maintenance on them, managed the backups and such, and otherwise did his job. Just those two things were pretty messed up.
They also had a dev here who did a lot of "pay no attention to the man behind the curtain" type support for his applications. In other words, they don't run unless someone holds their hand and issues "back door" commands. Everyone thought he was great, till he left. His name was Dan, and now it's not uncommon to have people refer to broken things as "Dangineered" (pronounced like "engineered", but with his name incorporated).
So, yeah, some time spent on such things. But for me, it's not even close to the majority of my time. Most of it is spent constructively, either on new systems or new features for existing systems.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon