• Stoneman (unregistered) in reply to anonymous

    Hey just to let you know! I used to run a 5000 user system on hardware just above 486's!!! and they fastest performance came about by putting as much logic as possible in the stored procedures!!!

    So anyone that tells you this is bad!  needs to come back to reality They are the same people that say 5th normal form is the way to go!!! hahaha when in the real world most people run 3rd normal form so they can "USE" their database!

     

    Anyway I highly recommend putting logic in the stored proc's if speed is your holy grail!

  • Scrub (unregistered)

    I could somehow see that making constants for table names and possibly column names could have a purpose, but it takes away readability and makes it so much harder to alter the SQL in the code. Think about it, if you need to proof read or test run this SQL in the database.

    If you use bind variables (which you almost always do except when it is blatantly obvious that the parameters never change) 'select *... ' will break your prepared statements, you will have to restart the application to add a column

    The SqlMisc.PARAMS_FIRST, SqlMisc.PARAMS_SECOND probably comes from Oracles older syntax where you could name the bind parameters :1, :2 etc

     

     

  • Narendra Venkataraman (unregistered)

    Very true.. In fact I was planning on writing on "Parody of constants" but this is too good.. some more enterprisocities like coffee talks -

    http://devdiary-naren.blogspot.com/2007/02/coffee-talks-may-also-be-referred-to-as.html

  • Jonadab (unregistered) in reply to anonymous
    For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes). Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

    I am against putting application logic in the database, which is the only thing I've ever seen stored procedures used for. In the case I'm thinking of, 25+ pages of seriously inscrutable SQL performed what would have been less than 20 lines of code in pretty much any high-level language. Because of the wrong-headed design, which apparently used triggers just to demonstrate that the programmer knew how, various things had to be coded in SQL up to three times: once in the stored procedure that runs nightly as a job, again in the stored procedure that the trigger calls, and again in the one that is called in response to a user action at some later time. Another time I found stored procedure code created, propagated, and iterated over entire temporary tables that were strictly necessary, making for 5+ pages of stored procedure, which when refactored came to less than 1 page of SQL, let alone what it would have been in a high-level language.

    And this wasn't even really in an enterprise product, just a garden variety dubiously-implemented one.

    I don't know that I would categorically forbid stored procedures, but I would certainly discourage gratuitous overuse of them.

  • (cs) in reply to jsmith
    jsmith:
    So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"?  The only reason this is a hack is that they are called "indexes"?  That goes back to a "the world revolves around me" position.  You don't like covering indexes because they aren't "right". Here is an interesting analogy: Indexes are called "indexes" because they work like the indexes in these things we call "books".  Well, a typical book index contains a keyword and a page number.  If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index.  Hey, look, database servers do that too!!!!  Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.

    Actually there is a new feature in SQL2005 that allows you to explicitly include columns into an index (Those new columns will be added to the leaf nodes of the index, so you can save some performance, since the extra Columns dont have to be sorted)

    To create a covering index, you need to know how you DB is used. Its not something you do light hearted. If you have a wide table, and you often retrieve only a very small subset of columns, then its a perfect solution. Yes, it creates some aditional overhead when you modify your data, but the net result can be quite effective.

    [edit]

    And it is by NO WAY a "hack"

  • Ömer Faruk Z (unregistered)

    where is the enterprise sql?

  • Jim McMaster (unregistered)

    This is not a new phenomenon. I worked in IBM 370 Assembler language for several years. My company decreed you had to define constants for everything. There was a constant for "the number of bits in a byte." Apparently, if IBM ever changed, we could just rebuild and continue.

  • ship (unregistered)

    It's really silly approach! I can e.g. say, that next generation of Java will completely use different definition of constants... I'm really curious how much time u would spend by refactoring your Java code to allign this changes with...

  • Frosty (unregistered)

    I've met an incredible enterprisey guy. See his work: http://stackoverflow.com/questions/6514260/php-string-constants-overuse

Leave a comment on “Enterprise SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article