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

Question

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

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

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

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 😀

vdn1986 Newbie Points: 7 More actions · Answer 2

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".

Stephen Hirsch SSCommitted Points: 1822 More actions · Answer 3

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.

majorbloodnock SSCrazy Eights Points: 9389 More actions · Answer 4

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

MikeAngelastro-571287 SSCommitted Points: 1576 More actions · Answer 5

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.

Eric_AZ Grasshopper Points: 18 More actions · Answer 6

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.

Joey Bagodonuts Newbie Points: 3 More actions · Answer 7

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.

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 8

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.

gary.strange-sqlconsumer SSCommitted Points: 1816 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

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)

pstamant Valued Member Points: 53 More actions · Answer 11

pstamant

Valued Member

Points: 53

July 14, 2010 at 12:20 pm

#1193045

Must be the reason he's the boss.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 12

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)

steve_kirchner SSC Enthusiast Points: 190 More actions · Answer 13

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".

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 14

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

Roy.G.Biv Newbie Points: 4 More actions · Answer 15

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.