How to Increase Query Speed by 3 Orders of Magnitude with no Indexes

  • Stephen Hirsch (7/14/2010)


    What I find interesting here is how much it bothers many of you out there that the query was never optimized in the normal sense.

    I find it interesting amusing to see how many people it doesn't bother!

    Still it keeps me in work so carry on 😀

  • I am assuming that the transactions on the form are updating to the Oracle DB.

    If users reload the data on the indexed view, wouldn't it be outdated? They may complain that data they just changed did not "save".

  • vdn1986 (7/14/2010)


    I am assuming that the transactions on the form are updating to the Oracle DB.

    If users reload the data on the indexed view, wouldn't it be outdated? They may complain that data they just changed did not "save".

    Sorry, it was 12 years ago. Don't remember.

  • Samuel Vella (7/14/2010)


    Stephen Hirsch (7/14/2010)


    What I find interesting here is how much it bothers many of you out there that the query was never optimized in the normal sense.

    I find it interesting amusing to see how many people it doesn't bother!

    Still it keeps me in work so carry on 😀

    The problem was 12 years ago and Stephen is still quoting the solution as effective, so I think we can safely assume it was a success.

    If that's the case, the poorly performing query was made irrelevant to the business. Why would we be bothered if no-one continued to optimise an irrelevance? And why would you be amused at people moving swiftly on to something that actually does matter to the business?

    I should clarify. I'm not trying to have a go or to be sarcastic. A scenario has been put before us where the results speak for themselves. I'm finding it difficult to understand suggestions that it was the wrong thing to do when (at least from the evidence given by the author) the business made the decision that it was happy with what it got in return for its money. The solution might offend our sensibilities, but how can we say it was wrong when it worked?

    Semper in excretia, suus solum profundum variat

  • I think that replies should avoid attacking the author of an article. That is not productive at all. The author took the initiative to share an experience. If you believe the author to be in error, reply with respect and include the logic behind your opinion. If you don't feel this way about it, please refrain from replying.

  • This article receives 1 star. The reason for this rating is that the title is intellectually dishonest, and especially so considering the likely consumers found here. It implies there is some meaningful, presumably technical information within. Instead, there is an anecdote about early use of a materialized view, * and a reminder not to forget the business. Yeah, thanks.

  • Agile development would have had the constituents involved in the development process from the beginning. This would have been corrected in the prototype had the end user/customer (constituent) been involved from the beginning.

    User perception is that they should not be involved in development but I usually end this resistance by saying that if it is important enough for me to build it is important enough for the user to participate daily.

  • majorbloodnock (7/14/2010)


    but how can we say it was wrong when it worked?

    Just because something works doesn't mean its the right to go about it.

    A query which takes 3 hours in UAT could be taking 12 hours a year later with a fuller set of production data and time shifting problems like that can result in even bigger headaches later on.

    In my opinion he got lucky, as Stephen says, if the users hadn't accepted the suggestion to only have data up to midnight then the article wouldn't have been written. Also, if that method had turned round to bite them sometime down the line then maybe it also wouldn't have been written.

    We've all seen it and we all know it goes on and some of us (including me) have done it. But to suggest it as a valid solution to the problem? In my opinion thats just setting a bad example.

  • I can't believe this even made it into sqlservercentral. If this dribble is the quality of articles to come I think I will look for a better provider.

    If you can't interact with the whole business entity then you are in the wrong vocation and you should think about a change of career.

  • gary.strange-1058508 (7/14/2010)


    I can't believe this even made it into sqlservercentral. If this dribble is the quality of articles to come I think I will look for a better provider.

    If you can't interact with the whole business entity then you are in the wrong vocation and you should think about a change of career.

    Heh... I understand exactly where you're coming from but there are a whole lot of places where regular communication doesn't happen. The article will appear as "dribble" to a lot of folks that don't have such communication problems while it's a key lesson that should be framed and hung on the wall for others. Hang in there.

    --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)

  • Must be the reason he's the boss.

  • Joey Bagodonuts (7/14/2010)


    User perception is that they should not be involved in development but I usually end this resistance by saying that if it is important enough for me to build it is important enough for the user to participate daily.

    Well said! The only thing I'd disagree with there is that I (very fortunately) have users that actually crave to be involved in the development cycle. To your good point, I absolutely love it when I get to do a little "extreme programming" with a thoughtful and knowledgeable user sitting at my shoulder. I've managed to meet and beat many an insane deadline with the help of users.

    --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)

  • I don't follow your Subject Title "How to Improve Performance by 3 Orders of Magnitude without Indexes".

    An "Indexed View" by definition utilizes an "Index".

  • steve_kirchner (7/14/2010)


    I don't follow your Subject Title "How to Improve Performance by 3 Orders of Magnitude without Indexes".

    An "Indexed View" by definition utilizes an "Index".

    It was actually done in Oracle using a snapshot

    . My boss then created a snapshot (an indexed view in MSSQL, now called a materialized view in Oracle)

    You could of course just dump the results into an unindexed table, thus keeping the premise that no indexes have been used

  • Yes, the article title is a teaser, but the lesson is relevant and useful. Sometimes end-users from c-level on down would come up with their passionately-envisioned application concepts or reporting scenarios. You could see that the ideas were important and appropriate and useful but as the IT person you would have a certain sense that the requirement and the imagined technology were out of sync. I used to ask 'What is the problem that this solution is intended to solve?' and that was often enough to elicit some calmer review of the actual business need.

Viewing 15 posts - 46 through 60 (of 87 total)

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