Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

How to Increase Query Speed by 3 Orders of Magnitude with no Indexes Expand / Collapse
Author
Message
Posted Thursday, July 15, 2010 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 26, 2010 7:38 AM
Points: 2, Visits: 3
That was cool attitude towards work,thats nice boss,I appreciate
Post #952948
Posted Thursday, July 15, 2010 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 8:47 PM
Points: 132, Visits: 112
CirquedeSQLeil (7/14/2010)
Stephen Hirsch (7/14/2010)
...je ne regrette rien...



Ah bon. Vous parlez francais aussi?


Ouais, j'en parles pas mal (j'l'ai appris au Québec)
Post #952950
Posted Thursday, July 15, 2010 3:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 1,049, Visits: 3,002
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
Post #952951
Posted Thursday, July 15, 2010 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 8:47 PM
Points: 132, Visits: 112
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.
Post #952959
Posted Thursday, July 15, 2010 4:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 8:47 PM
Points: 132, Visits: 112
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 .


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.
Post #952971
Posted Thursday, July 15, 2010 2:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
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?


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #953474
Posted Thursday, July 15, 2010 6:27 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #953562
Posted Thursday, July 15, 2010 7:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 26, 2010 11:02 PM
Points: 4, 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!
Post #953569
Posted Thursday, July 15, 2010 7:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 8:47 PM
Points: 132, Visits: 112
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.
Post #953577
Posted Friday, July 16, 2010 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 20, 2013 9:52 PM
Points: 2, Visits: 14
I can't believe I took the time to read this... or that you took the time to write it
Post #953746
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse