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 13 posts - 76 through 87 (of 87 total)

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

gary.strange-1058508 (7/15/2010)
This is a forum, inviting users to express their opinion.
I would consider using cut & paste to repeat your comment along the thread contributes nothing of substance.
Perhaps my comments about the article was a little harsh, but I like many others were tricked into reading this article which meant that other articles submitted by other authors were robbed of some exposure to the readers (reducing their AIR).
In my experience developers have plenty of opportunity to interact with the whole business but they choose not to. They're not interested in contributing and assume that just coding page after page of shocking code makes them a developer.
In my humble opinion the title should be "get your head out of the sand and interact with your business and you'll find you can achieve so much more!".

Gosh, Gary... How long did it take you to read the article? :blink:

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

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 2

My knowledge of Oracle is limited, but I understood that a Materialized View was always accurate to the second, just as an Indexed View always is in SQL. If he used something analogous to an Indexed View why did it need to have a time lag?

I will say I have done something similar in arranging for long running reports to be precomputed at night, but that was always to avoid the write performance reducation that would have come from putting those in an indexed view that would have been constantly current and always up to date.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

steve.vidal Valued Member Points: 52 More actions · Answer 3

timothyawiseman (7/15/2010)
My knowledge of Oracle is limited, but I understood that a Materialized View was always accurate to the second, just as an Indexed View always is in SQL. If he used something analogous to an Indexed View why did it need to have a time lag?

Under certain conditions, changes to the underlying data tables can be automagically merged into the materialized view, but that's not "always" possible. Also if the materialized view points to many tables, tracking changes would be counter-productive. Most of materialized views I've seen were recalculated on a 24hr cycle basis. Oh and the business requirements were understood before UAT...but I digress! 😉

Stephen Hirsch SSCommitted Points: 1822 More actions · Answer 4

An Oracle materialized view is really just that: a query whose results are stored in a table instead of disappearing into the ether. You can schedule to run on just about any schedule, or no schedule at all. You can have it run once a week, once a month, but in my experience, once a day is about the average.

Today (but not 12 years ago), you can have "fast refreshes" for materialized views based on join queries. This way, only the changed rows come over. That would make the query run faster after the initial load, but that wasn't available in those days.

boner28 Grasshopper Points: 16 More actions · Answer 5

I can't believe I took the time to read this... or that you took the time to write it

majorbloodnock SSCrazy Eights Points: 9389 More actions · Answer 6

boner28 (7/16/2010)
I can't believe I took the time to read this... or that you took the time to write it

I feel the same way about your post. Not the most constructive criticism in the thread.....

Semper in excretia, suus solum profundum variat

Ron Hooijschuur Grasshopper Points: 16 More actions · Answer 7

I too find the title severely misleading. This is certainly not a "how to" guide. The conclusion is lacking also. The business requirement was changed from realtime to snapshot. A good business analyst would have flushed this out before development started.

Communication is always key in any IT project. Combining responsibilities (programmer and analyst) is not necessarily a good thing and may cause Sarbanes-Oxley compliancy issues.

Stephen Hirsch SSCommitted Points: 1822 More actions · Answer 8

People, the title was purposely misleading. It was intended to get you to question your assumptions.

How in the world would combining the roles of analyst and programmer cause Sarbox problems? I worked in the clinical trial world for many years, under 21CFR Part 11, which is much stricter than Sarbox.

Ron Hooijschuur Grasshopper Points: 16 More actions · Answer 9

Segregation of duty: Programmers are not allowed access to production. Business Analyst require access to production for systems in scope.

Well, it got my attention alright. The content just didn't live up to the title's promise

Stephen Hirsch SSCommitted Points: 1822 More actions · Answer 10

Ron Hooijschuur (7/16/2010)
Segregation of duty: Programmers are not allowed access to production. Business Analyst require access to production for systems in scope.

Is that from Sarbox directly? If so, then make someone a business analyst/programmer. Problem solved.

Either way, that's idiotic. Sounds like a sure fire way to accurately hit the wrong target.

boner28 Grasshopper Points: 16 More actions · Answer 11

Fair point! My comments were not constructive however after being confused for 20 hrs, tired and grumpy, ur article title led me to premature thoughts of sleeping in my own bed. I think you should edit the title and not annotate with descriptions such as genius. Your boss probably asked a thousand questions during spec and that should probably have been in the top five. From reading some of the comments, it's obvious it's been beneficial to some and appreciated by others but it barely exceeds common sense. Now I've slept though, I like the passion u write with and I'm sure you've got a lot more to contribute. Rick

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

MikeAngelastro-571287 (7/14/2010)
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.

Man, do I ever agree with that! The author wasn't at fault for anything here. Lighten up folks.

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 13

I enjoyed the article. And it just goes to show, pay attention to everything, not just the code.