September 18, 2015 at 7:50 pm
Comments posted to this topic are about the item Whatever happened to Database Table Refactoring?
Best wishes,
Phil Factor
September 19, 2015 at 3:48 am
I don't know if it counts as "refactoring", but parts of our core-code were written 15 years ago and with-what-we-know-now we would have done it differently/better 😎
In some instances the tables are only accessed through a couple of SProcs, so we can change the table and change the SProcs and we are done. But more typically these tables have not been improved, when we became aware that they could be improved, because their use is systemic throughout our code and thus it would be a Big Change.
Occasionally, when I feel both bored and brave!, I replace the table with a VIEW and transition the original table to a new table suffixed "_V2" or somesuch. I usually want to avoid INSTEAD OF triggers on the VIEW so I go-fix all the Insert/Update/Delete code that touches the table, but leave the SELECTs behind. But over time each time we bump into a SELECT from the old-style table (now a view) it jars; and like anything Legacy it requires knowledge of why we did it that way back then so is increasingly a problem for any new hires. At some point I then decide to go-fix all instances that are left ... which tends to be a Flag Day for all Users with the fall out from that! plus we really need to run a complete QA test cycle to pick up anything which is not currently on the radar, otherwise we encounter bugs (in DEV) that slow us down until the next release (i.e. a cause of running the full QA cycle).
We have some fully automated revision tests, so that's great, but sadly we've never been able to get to the point where automated tests cover 100% of the APPs.
Perhaps I should make such changes really late in the DEV cycle, just before the full-blown QA cycle starts? that terrifies me in case it brings an unpredictable length of delay to the release date ... whereas the temptation is to do it right at the start of the DEV cycle, but then we keep bumping into changes because we haven't run a full QA, and instead waste time in DEV looking for a problem unrelated to whatever we were trying to build.
Perhaps there is a better way?
September 19, 2015 at 12:04 pm
Governance has to precede application development. The technique of persisting blobs and objects instead of relational data has always made my flesh crawl. Phil nails the problem.
if they are used to avoid 'design-up-front', then refactoring techniques can postpone the inevitable problems, but they can't avoid them. Truer words have never been spoken.
September 19, 2015 at 5:19 pm
Perhaps Phil has an opinion regarding the iterative approach in the metadata driven rdbms world like Dynamics CRM
September 21, 2015 at 12:48 am
With no documentation and too many versions of the application there is no refactoring.
The manager is of the mindset that because the schema is so difficult to modify or add anything to it, its MS SQL Server fault. Now they are going down the route of MySQL with just tables probably without any constraints. The fact a noticeable chunk of our revenue comes from bespoke reports is going to be irrelevant.
The term "slow motion car crash" comes to mind 🙁
The better places I have worked tend to refactor cause there has been a layer of sprocs in the way and documentation to help. Makes testing easy, keeps the developers happy, reduces on going risk, aids performance......
The gulf between companies following good practice and companies that don't ( or even understand the concept of good practice) seem to be growing from my experience.
September 21, 2015 at 12:59 am
Yet Another DBA (9/21/2015)
Now they are going down the route of MySQL with just tables
Couldn't they do that, and stick with MSSQL? I'm interested in what the factor is for choosing MySQL. When we brush up against competitors offering a product on MySQL its usually the license cost of MSSQL.
September 21, 2015 at 3:25 am
re: MySQL I'm going to say the licence cost issue is a big one these days, and seems to be more onerous than it has been. I've not had to cross swords with MySQL an awful lot but to be fair for many applications it may well be perfectly acceptable. I have found it mildly annoying to use but this is simply lack of familiarity cf MSSQL.
September 21, 2015 at 3:48 am
call.copse (9/21/2015)
for many applications it may well be perfectly acceptable.
I agree, except that whenever I see it used it is never used with the Full Recovery Model (equivalent) syntax, even on data that I would consider to be mission critical to the Client, so I (like many maybe?) tend to write off competitors that choose to use it without actually checking if they know what they are doing, or not.
Plenty of rubbish MS SQL implementations out there too ... including some 3rd party ones installed here!
September 21, 2015 at 4:48 am
call.copse (9/21/2015)
re: MySQL I'm going to say the licence cost issue is a big one these days, and seems to be more onerous than it has been. I've not had to cross swords with MySQL an awful lot but to be fair for many applications it may well be perfectly acceptable. I have found it mildly annoying to use but this is simply lack of familiarity cf MSSQL.
Anti-microsoft bias being the main issue. They say its free but when the backups are gigabytes of insert() values() for each table and its 24x7 then the costs is not going to be an issue when the clients sensitive data can be restored quickly.
September 21, 2015 at 7:03 am
re: MySQL I'm going to say the licence cost issue is a big one these days, and seems to be more onerous than it has been. I've not had to cross swords with MySQL an awful lot but to be fair for many applications it may well be perfectly acceptable. I have found it mildly annoying to use but this is simply lack of familiarity cf MSSQL.
I have sat in presentations where a engineer from Oracle has said that the one of the main reasons MySQL is funded is that it takes the lower end customers away from MS SQL Server. (Although I thought MS increasing license costs was doing a good job of that all along.)
September 21, 2015 at 7:58 am
call.copse (9/21/2015)
re: MySQL I'm going to say the licence cost issue is a big one these days, and seems to be more onerous than it has been. I've not had to cross swords with MySQL an awful lot but to be fair for many applications it may well be perfectly acceptable. I have found it mildly annoying to use but this is simply lack of familiarity cf MSSQL.
/shrug MySQL is freeware and like always you get what you pay for, sometimes you might pay a lot and not get much but if you don't pay anything you definitely won't get much. It's fine if you want to throw up a website or something quickly and you're comfortable working in command line but yeah... it's not very feature rich....
September 21, 2015 at 8:15 am
The database tables are what integrates line of business applications, backend processes, and business intelligence. To change the schema of the tables, you have to get everyone on the same page. The problem is that in a typical corporate or government IT department, there is a wall of separation between the application developers, DBA, and BI teams. In fact, they probably don't even see themselves as one unified team with a common project, rather they see themselves as the application team, the DBA team, and the BI team, each team with it's own separate management chain who compete for limited budget. You end up having complaints like the following:
"MY poor overworked team had to spend XXX hours to re-doing XX reports because THAT other team of slackers decided to change all the tables and... blaa.. blaa.."
That's why it's so uncommon for large corporate or government IT shops to develop good software and database systems or retain good developers.
However, if you have a tightly knit, cross-functional, and agile SCRUM modeled team, then stuff like collaboration, iterative design, and refactoring are just assumed and planned from the very beginning. It's so much easier to properly deal with something when you see as a normal part of the overall process and actually plan for it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 24, 2015 at 7:16 am
Yet Another DBA (9/21/2015)
call.copse (9/21/2015)
re: MySQL I'm going to say the licence cost issue is a big one these days, and seems to be more onerous than it has been. I've not had to cross swords with MySQL an awful lot but to be fair for many applications it may well be perfectly acceptable. I have found it mildly annoying to use but this is simply lack of familiarity cf MSSQL.Anti-microsoft bias being the main issue. They say its free but when the backups are gigabytes of insert() values() for each table and its 24x7 then the costs is not going to be an issue when the clients sensitive data can be restored quickly.
I may be mistaken, but the free version doesn't do online backup, does it? You have to take it offline (not sure about the Enterprise product - but that's far from being free)
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
November 16, 2015 at 9:26 am
Yet Another DBA (9/21/2015)
...The gulf between companies following good practice and companies that don't ( or even understand the concept of good practice) seem to be growing from my experience.
I was thinking this today. It is almost as though some companies are suffering "Silver Bullet Fatigue"1 and have given up.
1I have just coined this phrase but I still bet I am not the first to do so.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply