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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85615 Visits: 41082
gserdijn (7/14/2010)
Dutch Anti-RBAR League


I've just gotta love a signature line like that. ;-)

--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
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3053 Visits: 860
if the initial query took 3 hours to populate 1 screen then i imagine the database developers need shooting.

i just can't conceive that there were not other solutions that did not require a snapshot.

MVDBA
Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 1163
I'm in total agreement that in this scenario, and for new functionality in particular, the requirements should first be questioned before spending too much time/money on tuning. Ideally the project would not have got this far as the requirements such as this should be agreed as early on as possible in the project.

I encountered a similar scenario a few years ago and (as a dev/operational DBA) I questioned whether or not the users actually needed all of the data that was being displayed on-screen. The answer from the Business was 'no' - it transpired that the 'requirement' for the superfluous data was added to the solutions documentation by one of the project's Business Analysts who had gotten a little carried away.

After modifying the query appropriately the execution time went down from about 5 mins to less than 1s, which is a big difference when waiting for a form to populate whilst a customer is on the other end of the phone.

Chris
Dan Moran-347932
Dan Moran-347932
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 95
Yes, flattened structures are faster than complex structures. Next?
Phil Factor
Phil Factor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2002 Visits: 2971
We all admire bosses who, to use a British phrase, 'wear their underpants outside their trousers'. (play superman in a crisis). I support this type of solution, but because it is necessary rather than sufficient. As a database developer, I simply hate the thought that that query is still there, grinding away for three hours, albeit only once a day. This is a time-bomb. When the usage of the system quadruples, what then? No! I'm a firm believer in the idea that there are very few queries that can't be improved, and, usually, anything that takes more than ten seconds ought to be improved. In the SQL Speed Phreak challenge on Ask.SQLServerCentral.com we regularly increase performance by two orders of magnitude. The last winner was an SSIS task!


Best wishes,

Phil Factor
Simple Talk
Graeme100
Graeme100
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1434 Visits: 787
Great article Stephen.

Yes it does open a can of worms with regards to poor development, bad communication etc...but i still think it needs an article like this to remind people of this. I imagine there are still lots of DBAs out there working with poorly developed code and /or users who don't really know what they want until they have some options so good effort give them a nudge in the right direction.

Spot on with a page taking 3 hours to populate....a firing squad awaits such developers.

I often think one of the main roles of the DBA is to suggest efficient and practical solutions even if they don't match up to the developers/users expectations. Of course there must always be cooperation from all sides Smile

Thanks
Graeme



Samuel Vella
Samuel Vella
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 2144
Here's another story about bosses and optimisation...

A few years ago when I was a still slightly wet behind the ears developer my boss asked me to run a report for the finance department. He already had the SQL from the previous year, he just wanted me to change the dates, run it and send the results in a nice looking spreadsheet to the finance controllor making sure that they got it by 9am the following morning.
Boss went off to meeting leaving me to get on with it.
About an hour later my development leader was going home. He paused by my desk smirking "Good luck with that report, the boss was here till 4am last year waiting for it to finish running!"
I beckoned him over to my desk.
"Have a look at this email" I said.
He looked at my screen and read the email, it began:
"Dear Financial Controller,

Please find attached the report you requested...."

The moral of the story?
Don't spend your afternoons getting bored and watching the executing circle spinning round. Try making something better.
Stephen Hirsch
Stephen Hirsch
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 161
To answer some critics: yes, we could have done a lot of tuning. It was a development database that was being played with daily, the DBA was famously lazy, there were lots of things that could have been done. The concept of time shifting was not new. To use another movie analogy, in Raiders of the Lost Ark, Harrison Ford's character just shoots the guy with the swords, because Harrison Ford the actor had diarrhea at the time. It wasn't in the script, but it worked so well, it made it into the final cut.

I know that it bothers a lot of you that the same stinky query was running, just at a different time. It bothered me to. It's just that sometimes, our normal definitions of quality aren't really valid; the trick is to know when.
r.stebbens 78867
r.stebbens 78867
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 162
Hmm, quite a misleading title there. ***SO*** what would have happened if the previous nights data had not been acceptable? The DBA would have been responsible...w00t

Developer, DBA, Pre-Sales consultant.
parcival
parcival
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 166
Excellent article. Call it lateral thinking, thinking-outside-the-box or pure professionalism but it is a great holistic way to work

I think Mr Vidal, above, has missed the point somehow.

Regards,



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