Click here to monitor SSC
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45071 Visits: 39906
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 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 (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 1158
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 (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 95
Yes, flattened structures are faster than complex structures. Next?
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 2937
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1249 Visits: 760
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
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 2141
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-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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 (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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