SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ajitpitlellu
ajitpitlellu
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
That was cool attitude towards work,thats nice boss,I appreciate
Stephen Hirsch
Stephen Hirsch
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 161
CirquedeSQLeil (7/14/2010)
Stephen Hirsch (7/14/2010)
...je ne regrette rien...:-P



Ah bon. Vous parlez francais aussi?


Ouais, j'en parles pas mal (j'l'ai appris au Québec)
majorbloodnock
majorbloodnock
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1507 Visits: 3062
gary.strange-1058508 (7/15/2010)
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!".

This general subject is far from exhausted, and if you were to contribute an article with that title, I'd definitely read it. I'm not being being facetious, by the way; a lot of contributors to this thread have raised points that are well worth exploring further in an article.

Semper in excretia, sumus solum profundum variat
Stephen Hirsch
Stephen Hirsch
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 161
I think it was Mark Twain that said, to a man with a hammer, everything is a nail. While that's a funny saying, and all too true in the IT world, it's really not a good way to go. Sometimes the best use of a tool is to not use it.

For example, I've done a lot of work that used dynamic SQL. To properly use dynamic SQL, you almost have to have a well-maintained metadata repository. Well, the first thing I found out about metadata was that there was a limit to how much you could automate it. Too much automation made it brittle and easily broken.
Stephen Hirsch
Stephen Hirsch
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 161
Jeswanth Jaishanker (7/14/2010)
Dear Stephen,
How does this improve the query speed when the previous 3 hour sql and current 15 seconds sql actually querying different tables and views?

Wondering why your team waited till UAT to find this very visible issue Hehe.


In actuality, it was the perception that changed. My boss just moved the poorly performing query to what we call in the US "garbage time".

Why did we wait until UAT to find this problem? That's a very good question, one that I couldn't have answered at the time, let alone 12 years later.

Another point I want to make is that you can't assume in your development estimates that there won't be silly problems like this occurring at the worst possible time.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 920
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
steve.vidal
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 26
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
Stephen Hirsch
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 161
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
boner28
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 15
I can't believe I took the time to read this... or that you took the time to write it
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search