New Job wtf



  • I recently started a new position at a large conglomerate-type place. It took a while to get everything I needed to do my job, but I'm finally set up. I spent some time getting to know the code and the business it's supposed to support. Then it's time for the next phase of the project: to do a major upgrade of the functionality. Ok, no problem. There are some decent design documents. Finally, I get to do some actual work.

    I log into the DB server to look around in the database. It's Oracle. The data is partitioned by year, month, date, transaction type, and a couple of subordinate types under that. Why? There are literally billions of rows in the multi-terabyte database. The system seems ridiculously slow. I check. There are over 300 people actively hitting the DB server simultaneously. Of the 300, most are doing queries without using PK's, and so are doing multiple nested table scans. I send out an e-mail. It creates a flurry of nose-bent-out-of-joint e-mails. Finally the big boss chimes in: we don't use the PK's because we don't think about the data that way - leave people be. I suggest that if this is so, they might want to invest in a much more powerful server with a ton of memory to cut the 2 hour queries down to a few minutes. If nothing else, it would pay for itself in productivity. You can imagine the answer.

    Don't get me wrong, the people are nice enough, and the equipment and development process are actually decent. I just don't get some people.



  • If you don't have a primary lock, you don't need a primary key. It's as simple as that, don't you know?


  • Considered Harmful

    They're just academics thinking in terms of pure set theory, where performance is not an issue.

     Or, they're just <ADD>ignorant</ADD>.

    Edit: stupid forum software won't recognize <del> or <s> tags.



  • @snoofle said:

    <snip />

     Finally the big boss chimes in: we don't use the PK's because we don't think about the data that way - leave people be.

    <snip />

    Don't get me wrong, the people are nice enough, and the equipment and development process are actually decent. I just don't get some people.

    I've worked with a lot of clueless people before as well.  Every time I've been asked about them I would just say, "Well, they are just nice people."

    Save your sanity and move on.



  • @clively said:

    @snoofle said:

    <snip />

     Finally the big boss chimes in: we don't use the PK's because we don't think about the data that way - leave people be.

    <snip />

    Don't get me wrong, the people are nice enough, and the equipment and development process are actually decent. I just don't get some people.

    I've worked with a lot of clueless people before as well.  Every time I've been asked about them I would just say, "Well, they are just nice people."

    Save your sanity and move on.

    You know, 20 years ago I would have, and did, several times. Then I finally got old enough to realize that the world is full of idiots, and you'll always need to work with them. I think I've gotten to the point where I realize that I can't get rid of them, but if I can find a bunch of [i]tolerable[/i] idiots, then I can live with it,



  • Won't the query optimizer use indexes on non-primary keys (unless the queries are too complicated)? Or are there not indexes on the columns they're using?  I'm barely Oracle literate.



  • @poochner said:

    Won't the query optimizer use indexes on non-primary keys (unless the queries are too complicated)?

    Of course it would use such indexes; otherwise, those other indexes would have no reason to exist. 



  • @ammoQ said:

    @poochner said:

    Won't the query optimizer use indexes on non-primary keys (unless the queries are too complicated)?

    Of course it would use such indexes; otherwise, those other indexes would have no reason to exist. 

    Agreed, but only if those non-primary keys are part of the query. These, ahem, geniuses, were using the non-indexed attributes to search. The really sad part is that they HAVE the PK's - they just don't get that you can reference an entire record by a single integer value; you HAVE to describe it by virtually all of the other information.

    I honestly believe that I get paid to be entertained by this sort of cluelessness. OTOH, work would be just, well, work, without them ;)



  • @snoofle said:

    @ammoQ said:

    @poochner said:

    Won't the query optimizer use indexes on non-primary keys (unless the queries are too complicated)?

    Of course it would use such indexes; otherwise, those other indexes would have no reason to exist. 

    Agreed, but only if those non-primary keys are part of the query. These, ahem, geniuses, were using the non-indexed attributes to search. The really sad part is that they HAVE the PK's - they just don't get that you can reference an entire record by a single integer value; you HAVE to describe it by virtually all of the other information.

    I honestly believe that I get paid to be entertained by this sort of cluelessness. OTOH, work would be just, well, work, without them ;)

    And they won't let you create indexes on those columns, either, I take it? I know the optimizer has (had?) of bugs which cause it to ignore indexes at times, and having to many indexes can slow down your inserts, but this sounds like a situation where selects are the bigger deal.



  • I very briefly wondered whether this poster was from my company. We have researchers and analysts with catch-as-catch-can SQL knowledge muddling through and not knowing the best way to do things. The difference is, when (not if) someone knowledgeable tells us there's a way to improve our query performance, management's reaction is, "Great. We want you to give a seminar to go over these techniques, and anything else you think would be helpful." And we'd need the big conference room.



  • Reminds me of checking one company's mysql configuration / server with standard mysqltuner... Result had a line simillar to:

    Key buffer size: 8MB (size of all indexes: 1.6GB)



    Unfortunately for small companies it's easier to buy next server once than get a DBA permanently...



  • from what you've written, i get the impression these folks are somewhat literate in sql syntax as there's no mention of an app inbetween the db and the users, only that the users seem to be writing their own queries on the database.  perhaps finding some commonalities in their select statements and building indices of those common fields would be a start, and as a follow-up providing them information on index hints in oracle might help too.  if none of that works, oracle can always use more memory(unless it's on windows, ugh) and processor power too.


Log in to reply