Packaged-Application Database Nightmares - A Horror Story.
-
SQLBlimp (12/24/2015)
... The inspiration for this story was a "database agnostic" ERP application. The trouble is that the lowest common denominator was not SQL Server->Oracle->mySQL - it seemed to be IBM ISAM or worse. mySQL and Oracle support ANSI joins and typed columns. Moveover BIGINT (int64) column types with an identity specification are supported by the big three as well, which means that they are the perfect clustering index for SQL Server/mySQL (I do realize that Oracle IOT are not all that great but an Oracle implementation need not use IOT).The database seemed to be designed to make it fast to code the programs, not to store the data properly. ...
Been there. The database agnostic ERP that I worked on was not quite as bad in one regard, in that the data types were reasonably defined (except their notes -- any note on any record was chunked in to 70 character pieces then later reassembled) but they ran an application server that made every major operation RBAR. As a result, every other year they have to replace their application server as their database continues to grow, even though all server metrics show that the database server is not breathing hard. It simply was impossible to scale performance to the growth in the database without newer and faster servers. They're on their third or fourth 'reimplementation' to try and make it work properly, even though their reimplementation is simply reloading all the data. I'm not quite sure how that's supposed to improve a flawed model.
On top of that, the company outsourced some of their DBA monitoring to the ERP vendor, who later failed to notice a dirty DBCC, followed by failing backups, followed by the SAN dropping a drive that a different database was on, resulting in the loss of 2-3 months of data. I would have busted a gut laughing had it been the ERP system that got crushed, but it was a different DB.
One of the happiest days of my life was leaving that outfit. They bought a massively flawed product and had so much invested that they couldn't admit that it was a bad decision even when it was conclusively pointed out the problems with it. I won't name them, and the vendor's name does not appear on my resume. Sadly, they bought an accounting package that my current employer uses, and they're performing an upgrade on it starting tomorrow. I'm SO GLAD that I have no responsibility for it.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
j.peavey (12/22/2015)
Please add to your bulletined list: All tables are six characters in length and use a cryptographic algorithm for the name.:-DI'd recommend double-ROT13 as the crypto algorithm for the name, it's twice as strong as ROT13 by itself! 😛
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I am a new DBA. I read this article and thought it was all over exaggeration. Two days later this application was stood up on my server. You did not exaggerate, you understated the situation.
-
I am a new DBA. I read this article and thought you were over exaggerating. Two days after reading this I found this application was stood up on my server. You did not exaggerate you understated the situation. There are no stored procedures for the app to use. the tables and columns all have names like cx0002f. These are dynamically generated by the application and are likely to change over time. We have not yet gone to production but should soon. :w00t::w00t: I hope the ending of my story works as well as your story did.
-
ajordan 76503 (3/16/2016)
I am a new DBA. I read this article and thought you were over exaggerating. Two days after reading this I found this application was stood up on my server. You did not exaggerate you understated the situation. There are no stored procedures for the app to use. the tables and columns all have names like cx0002f. These are dynamically generated by the application and are likely to change over time. We have not yet gone to production but should soon. :w00t::w00t: I hope the ending of my story works as well as your story did.Thanks for the up-vote. Good luck; you have my best wishes.
John.
-
You forgot the part about the One True Lookup Table.
Oh, and the Dynamic SQL that opens programmatically named transactions, goes into an infinite loop, and crashes your server because the "machine generated" CSV file that it is ingesting was hand-massaged and a comma or an apostrophe was "accidentally" added. (Damn you O'Houlihan, breaker of weak code!)
There should be a yelp for slick vendor packages.
-
Installed SAP, did you? 😉
-
Surely there were major parts of the application enlisting EF to talk directly to tables resulting in the procedure cache filling up with adhoc queries and highly coupled interactions while at the same time the developers are praising low coupling out of the other sides of their mouths. The other thing missing is the one where the application creates new tables on the fly with random naming. And no table names in production matche development or stage except for the base tables that ship with the package. How do you write unit tests for that? Hmmm, I guess that last outburst was overdone since there obviously was no database unit testing.
As Jeff Moden points out it is, unfortunately, not fiction. I lived a good portion of this...except that the application and database were developed in-house, by a team totally unrelated to the one I was on (I wasn't a DBA, I was a DB Developer who was usually charged with fixing other's performance issues.). Also the database was 5NF with piss-poor (and mostly missing) indexing and monolithic stored procedures that had more lines in the WHERE clauses than there were rows in most of the tables. The majority of the WHERE clauses were testing for NULL parameters...on EVERY ROW. Parameter values did not change during the execution so...WTF?
The other team's answer? They bought a hulking server for SQL. Now it was slow AND expensive. When I got it they were removing features in an attempt to shore up performance. Since it was the primary method for acquiring new orders, they hadda do something, right? I had the help of a contractor to split up the triage and fixes. When we got done they could not add features back fast enough. Of course there were some issues...like fixing an index definition that then let bad data loose in the application. I was roasted for that one but it was short term. I was barely above rare because of all the other good we pulled off.
I have no idea what the team spent originally. I do know that we were 'billed' at $65 per hour internally and I invested $40,000 of my time and well over $10,000 of the contractor's time. We could not afford to do it right the first time but we could afford to fix it.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
2000 tables - seen that, tummy rumbles a bit
No FKs - grinds teeth
No clustered indexes (every table is a heap) - reaches for the vomit bag
No defined Primary keys (instead tables use a nonclustered unique constraint to enforce PK) - slow billowing smoke appears at nose
No indexes on natural primary keys used to join tables - and ears
Every column defined as varchar (except for some dates that are DATETIME) - opens drawer looking for pre-written letter of resignation
Reads the rest of the list, just for fun and to kill time before delivering LOR prior to lunch.
At exit interview politely tells management that there are plenty of good DBAs out there and no one is irreplaceable, right?
Explains to colleagues they are brave warriors and will all be regarded as hero's when they perish.
Meets happy people at local bar, talks shop with techies, has new job within 3 weeks (keeps side consulting gigs going, you know, just in case) -
This DBA has it easy - I only saw one application described in the story! They should be supporting at least half-a-dozen large databases that are this problematic, and dozens of smaller ones!
-
The fictitious bit is the happy ending
-
Good lord. "Packaged Application" certainly does include Microsoft. Here's what I found when I went to download SSMS 17.6. Thankfully, I use precisely zero maintenance plans but I'll be damned if I'll download this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - caroline-599293 - Tuesday, April 24, 2018 12:43 AM
or People, or Pegasystems, or ...
I believe the root problem with turnkey style ISV databases, especially the CRM related ones, is that it's marketing folks, not technical folks, who are driving the product development. Don't waste your time boring them your geeky opinions about software and database engineering; what they care about is market share and selling the next big client implementation."Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
- dbeggs57 - Tuesday, December 22, 2015 7:23 AM
Except he left out "all the table names are obfuscated" 🙂
- Eric M Russell - Tuesday, April 24, 2018 12:31 PM
I helped support PeopleSoft HR and Finance systems, each of which had 55,000+ tables. Of course, not all were used because we didn't use all of the modules available in the products.
Viewing 15 posts - 46 through 60 (of 87 total)
You must be logged in to reply to this topic. Login to reply