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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Stephen Hirsch » How to Increase Query Speed by 3 Orders of...

88 posts, Page 2 of 9

««1 2 3 4 5 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Wednesday, July 14, 2010 1:06 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

gserdijn (7/14/2010)

Dutch Anti-RBAR League

I've just gotta love a signature line like that. Wink

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #952086

MVDBA

Posted Wednesday, July 14, 2010 1:53 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

Post #952110

Chris Howarth-536003

Posted Wednesday, July 14, 2010 2:04 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:01 AM
Points: 266, Visits: 1,023

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

Post #952116

Dan Moran-347932

Posted Wednesday, July 14, 2010 2:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:12 PM
Points: 1, Visits: 95

Yes, flattened structures are faster than complex structures. Next?

Post #952121

Phil Factor

Posted Wednesday, July 14, 2010 2:29 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

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

Post #952122

Graeme100

Posted Wednesday, July 14, 2010 2:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:12 AM
Points: 1,209, Visits: 628

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

Thanks
Graeme

Post #952125

Samuel Vella

Posted Wednesday, July 14, 2010 4:02 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 328, Visits: 1,851

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.

Post #952160

Stephen Hirsch

Posted Wednesday, July 14, 2010 4:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 3:53 PM
Points: 127, Visits: 85

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.

Post #952162

r.stebbens 78867

Posted Wednesday, July 14, 2010 4:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 7:09 AM
Points: 111, Visits: 116

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. Occasional Blog writer

http://rstebbens.wordpress.com

Post #952164

parcival

Posted Wednesday, July 14, 2010 4:38 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 17, 2011 7:14 AM
Points: 16, 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,

Post #952167

« Prev Topic | Next Topic »

88 posts, Page 2 of 9

««1 2 3 4 5 »»»

Permissions