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 ««12345»»»

How to Increase Query Speed by 3 Orders of Magnitude with no Indexes Expand / Collapse
Author
Message
Posted Wednesday, July 14, 2010 1:06 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(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 #952086
Posted Wednesday, July 14, 2010 1:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,285, Visits: 781
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
Posted Wednesday, July 14, 2010 2:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:37 PM
Points: 278, Visits: 1,071
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
Posted Wednesday, July 14, 2010 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, July 14, 2010 2:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 579, Visits: 2,520
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
Posted Wednesday, July 14, 2010 2:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 1,221, Visits: 691
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
Posted Wednesday, July 14, 2010 4:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 337, Visits: 1,988
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
Posted Wednesday, July 14, 2010 4:07 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:47 PM
Points: 132, Visits: 114
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
Posted Wednesday, July 14, 2010 4:21 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 3:07 AM
Points: 121, Visits: 147
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...

Developer, DBA, Pre-Sales consultant.
Post #952164
Posted Wednesday, July 14, 2010 4:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse