Database Development Made Easy?

  • Jeff Moden - Saturday, September 2, 2017 12:01 PM

    miapjp - Friday, September 1, 2017 4:07 PM

    Back a few years ago, we had very OO developers, who'd never seen a stored procedure that did any kind of multiple statements.  They viewed the DB as a big can of data. All work was done in the code.

    How'd that work out for ya?  EBay does the same thing.  It might be why they actually do need 600 servers. 😉

    Seems to be standard practice... We have loads of un-normalized (not be confused with denormalized) that are a straight 1:1 match with application side form fields. I hate to make generalizations, but on the whole, OOP programmers seem to look at the database layer as nothing more than a big, server side, cookie. Hell, even my old boss saw our databases as nothing more than "data persistence devices".

  • miapjp - Friday, September 1, 2017 4:07 PM

    Back a few years ago, we had very OO developers, who'd never seen a stored procedure that did any kind of multiple statements.  They viewed the DB as a big can of data. All work was done in the code.

    I once came across a database like that - and not by accident,  I was recruited to sort out the comapany's databases, and that database and the bunch of C++ developers who had done it were the most of the reason the company had problems that needed sorting out.  A lot of the database tables weren't even in 1NF, there were no foreign key constraints, no unique constraints, in fact no constraints at all,  and no indexes.  The C++ app calling the database sent only one Sql query at a time, never multiple statements in one call, the app called the database as SA and the SA password was blank  (this for  system already running on two customer sites under contracts which required decent security).

    Tom

  • miapjp - Friday, September 1, 2017 4:07 PM

    Back a few years ago, we had very OO developers, who'd never seen a stored procedure that did any kind of multiple statements.  They viewed the DB as a big can of data. All work was done in the code.

    If that's really how they want to roll, then they should consider MongoDB.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • I believe there is a shortage of what I consider to be real database developers. It seems to me that most database performance issues are addressed from the perspective of platform and operations: migrate the server, tweak the server, scale-up, scale-out, etc. However, these things either have zero impact or at best can provide only a 30% improvement at the expense of doubling the operational cost.
    Addressing the root cause of the issue, meaning analysis and optimization of the SQL code, tables, and indexes; that is only seen as a last resort or maybe never considered at all, because the skillset doesn't exist in-house. But that's what gives you the 10x yield you're truely looking for.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell - Tuesday, September 5, 2017 6:20 AM

    miapjp - Friday, September 1, 2017 4:07 PM

    Back a few years ago, we had very OO developers, who'd never seen a stored procedure that did any kind of multiple statements.  They viewed the DB as a big can of data. All work was done in the code.

    If that's really how they want to roll, then they should consider MongoDB.

    This is the issue I have as I am both a developer and DBA. The lead developer sees relational databases as Python sets/dictionaries which is why he is pushing for the use of MongoDB!

  • Jeff Moden - Saturday, September 2, 2017 12:01 PM

    miapjp - Friday, September 1, 2017 4:07 PM

    Back a few years ago, we had very OO developers, who'd never seen a stored procedure that did any kind of multiple statements.  They viewed the DB as a big can of data. All work was done in the code.

    How'd that work out for ya?  EBay does the same thing.  It might be why they actually do need 600 servers. 😉

    Haha.  I was the one laid off, since I wasn't up to speed on C#.

  • Eric M Russell - Tuesday, September 5, 2017 6:39 AM

    I believe there is a shortage of what I consider to be real database developers. It seems to me that most database performance issues are addressed from the perspective of platform and operations: migrate the server, tweak the server, scale-up, scale-out, etc. However, these things either have zero impact or at best can provide only a 30% improvement at the expense of doubling the operational cost.
    Addressing the root cause of the issue, meaning analysis and optimization of the SQL code, tables, and indexes; that is only seen as a last resort or maybe never considered at all, because the skillset doesn't exist in-house. But that's what gives you the 10x yield you're truely looking for.

    Agree.  At the job where the OO developers ruled, there was a process that took 4 hours (remote web app, to the DB & web servers at our location) when it first went live.  The lead dev said he'd take a look, I asked what ad hoc queries they had written or which of my sprocs they used (can't remember exactly) and I'd take a look, too.  I added some indexes.  The next day, my boss sat down at my cube and asked "OK, what did you do?".  Turned out the process now took 4 MINUTES (still not great, but hey).  And the dev hadn't gotten a chance to look at the code yet, so it was all index tuning.  

    But, as I said elsewhere, I was the one who got laid off due to my middling C#/OO skills.

  • Eric M Russell - Tuesday, September 5, 2017 6:39 AM

    I believe there is a shortage of what I consider to be real database developers. It seems to me that most database performance issues are addressed from the perspective of platform and operations: migrate the server, tweak the server, scale-up, scale-out, etc. However, these things either have zero impact or at best can provide only a 30% improvement at the expense of doubling the operational cost.
    Addressing the root cause of the issue, meaning analysis and optimization of the SQL code, tables, and indexes; that is only seen as a last resort or maybe never considered at all, because the skillset doesn't exist in-house. But that's what gives you the 10x yield you're truely looking for.

    I  reckon that the biggest problemi is the set of myths that are being fed to people (including developers) trying to learn about relational databases, often by people who are called DBAs.  Things like:-
    1) don't normalise because it makes your database take up more disk space  (when in fact it tendes to reduce the disk space required).
    2) don't use foreign key constraints because  they destroy performance (when in fact they tend to improve it).
    3) if you do have foreign key constraints, don't use the on update/delete cascade capability - it's much less error prone to do that stuff in the app than in the database (when in fact using the cascade capability reduces code size and complexity, so it reduces the scope for writing bug).  This myth probably is spread by people who otherwise appear to understand schema design, so I suspect it's a twisted version of the true statement that if you want parents to be undeletable while chidren exist because there is a requirement (perhps a legal requirement, perhps a contractual requirement, perhaps something else) that chidren are always deleted explicity in the app you should not use the cascade capability since referential integrity will then prevent the parent being deleted while it has a child, but apparenty competent people spout the myth version regularly.

    Just normalising to the appropriate level (usually either 3NF or EKNF, but sometimes 4NF or higher is needed), ensuring that columns where NULL is not needed are constrained not to be NULL, providing ALL appropriate unique constraints, all valid referential integrity constraints, and understanding for ech table whether it should be a heap or should have a clustered index (and if it should have a clustered index making a correct choice of the appropriate index for storage of that table to cluster on) will usually producee a much greater performance improvement than throwing hardware at a bad schema design.  And often changing to  good schema design froma truly awful one requires all the quieries to be rewritten, and usually to be much simpler than before because the software doesn't have to jump through hoops to avoid the nomalies that are now ruled out by nrormalisation, and that too improves performance.  And teaching developers to normalise and to take full advantage of all constraints is not at all dificult if one can first convince them that the three myths listed above are just that - myths;  but that initial convincing can  be very difficult when they have been taught the myths by someone who has deluded them (and perhps also him/herself) into thinking he/she is an expert on relational databases.

    Having got to that first point in improving performance, the next step (writing really performant SQL for complex queries) requires rather more knowledge, as does determining which indexes are going to be useful and which are not (and therefor should not be included in the design).  But for that step you need database developers (who are often development DBAs) not OO developers with a little database knowledge.

    Tom

  • miapjp - Tuesday, September 5, 2017 8:16 AM

    Eric M Russell - Tuesday, September 5, 2017 6:39 AM

    I believe there is a shortage of what I consider to be real database developers. It seems to me that most database performance issues are addressed from the perspective of platform and operations: migrate the server, tweak the server, scale-up, scale-out, etc. However, these things either have zero impact or at best can provide only a 30% improvement at the expense of doubling the operational cost.
    Addressing the root cause of the issue, meaning analysis and optimization of the SQL code, tables, and indexes; that is only seen as a last resort or maybe never considered at all, because the skillset doesn't exist in-house. But that's what gives you the 10x yield you're truely looking for.

    Agree.  At the job where the OO developers ruled, there was a process that took 4 hours (remote web app, to the DB & web servers at our location) when it first went live.  The lead dev said he'd take a look, I asked what ad hoc queries they had written or which of my sprocs they used (can't remember exactly) and I'd take a look, too.  I added some indexes.  The next day, my boss sat down at my cube and asked "OK, what did you do?".  Turned out the process now took 4 MINUTES (still not great, but hey).  And the dev hadn't gotten a chance to look at the code yet, so it was all index tuning.  

    But, as I said elsewhere, I was the one who got laid off due to my middling C#/OO skills.

    So True.  About 8 years ago, I was called into a video platform company that was just acquired.  It was a bunch of Java developers, two mirrored SQL Server DB's (one running on a 6 mo.temp license (yikes)) and db design issues.Why was I there?  to fix DB performance and outage issues.  Problem?  queries with joins against 192 tables (yes, 192 tables) , most join logic was created with the OO tools, and were a mess, and trying to run reports against the production system. So many worst practices.   It was a 24/7, global access system, with no real slow time except a small Sunday window with a high TPS.  What a mess.  The biggest hurdle was not fixing the problem, it was convincing the staff to make changes to their code and design issues.  But, finally fixed the problems.

    The more you are prepared, the less you need it.

  • I've found that when working in a team the database and application work immeasurably better, the development goes much faster and deployments go smoother.
    When dealing with remote development teams or as an outsider to a development clique the results are extremely variable.  As long as both sides are willing to work together most problems can be solved quite quickly.  Where good relationships exist even quite difficult conversations can be held without rancour.
    The problem comes when you have someone who overestimates their talent and assumes that what talent they have extends out beyond their area of "expertise".  These people can produce a poorly performing system regardless of technology.  If such people gain authority (and they seem hellishly capable in this respect) they seem keen to advocate technology change as a means of solving problems.  What you end up with is a team of expert beginners who have to figure out how to provide 5 years of experience where there is only 6 months worth each.

  • miapjp - Tuesday, September 5, 2017 8:08 AM

    Jeff Moden - Saturday, September 2, 2017 12:01 PM

    miapjp - Friday, September 1, 2017 4:07 PM

    Back a few years ago, we had very OO developers, who'd never seen a stored procedure that did any kind of multiple statements.  They viewed the DB as a big can of data. All work was done in the code.

    How'd that work out for ya?  EBay does the same thing.  It might be why they actually do need 600 servers. 😉

    Haha.  I was the one laid off, since I wasn't up to speed on C#.

    Oddly enough, I think you may be better off than work in a shop with such a limited view.

    --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)
    Intro to Tally Tables and Functions

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply