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 1234»»»

(Un)Common Speed Phreakery Expand / Collapse
Author
Message
Posted Saturday, August 7, 2010 12:05 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:10 AM
Points: 14, Visits: 388
Comments posted to this topic are about the item (Un)Common Speed Phreakery

Veni Vidi Velcro
I came, I saw, I stuck around
Post #965572
Posted Saturday, August 7, 2010 12:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 1,920, Visits: 19,334
"What’s the dividing line between building a solution which is Blisteringly Fast, and building one which is just Pretty Quick?"

Suppose the answer is, as always, "it depends"....

Interesting thoughts...maybe a few "real world" examples would aid the discussion?

regards


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #965576
Posted Saturday, August 7, 2010 1:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 19, 2014 6:05 AM
Points: 8, Visits: 46
Peter's Tuning Rule 0: Don't fix a problem you don't have.

Peter's Tuning Rule 1: You can't tune something that you can't measure.

So, don't write bone-headed SQL (or whatever it might be), but don't become uber-anal.
Post #965582
Posted Saturday, August 7, 2010 1:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
I don't know it the learning potential got missing in the Speed Phreak Competitions.
That's one of the advantages I have been discussing with Phil Factor when doing the competitions.

Looking at high-performance code do learn you a thing or two. Most often the barrier to the high-speed solutions is not doing undocumented or using some wodoo black art coding techniques.

It's simply looking at the data with a new set of eyes. I most often call it pre-aggregation and to use only as much precision as the task demands to solve the problem.

The key is to know your tools and understand your data.



N 56°04'39.16"
E 12°55'05.25"
Post #965584
Posted Saturday, August 7, 2010 2:41 PM


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: Today @ 2:05 AM
Points: 587, Visits: 2,528
I don't think anyone is going to suggest that all code should be optimized, but it is pretty obvious when it is necessary. Once we've got a 20 minute routine running at under a second, we often tend to wonder, in the SQL Speed Phreak contests, how much further we need to go. The problem is that a routine that is being called every few milliseconds, such as a procedure that is being called by a website every time a page is requested, needs to be optimized pretty severely and the sort of tricks that Peso comes up with to shave a few milliseconds are entirely in order, whereas a procedure that only gets called once a day by a background process, and that doesn't take table locks, can take its time. I'd hate to think that someone is going to take the message that everything in a database has to be optimized until it glows. Nowadays, it is easy; you do you DMVs and it is pretty clear what needs the work.
The reason we like doing the SQL Speed Phreak contests is because it is such a good way to illustrate how a database developer approaches performance optimization. It is a different approach to the DBA.



Best wishes,

Phil Factor
Simple Talk
Post #965589
Posted Saturday, August 7, 2010 5:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
In my experience the following statement is usually incorrect: "solutions which are “fast enough” can break just as easily as those which are tuned to perfection".

On the contrary, usually simpler solutions are more robust.
Post #965603
Posted Saturday, August 7, 2010 7:21 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
From the article:

Is there such a thing as too much attention to the performance of SQL? Are we in danger of wasting time on optimising code when it is entirely unnecessary?



We could, of course, start a full blown holy war on that very subject and, having been repeatedly bitten by the reprecussions produced by those on one side of that likely war, happily admit that if one breaks out on this thread, I will be there launching nukes. Hell... I've been bitten by the reprecussions so often that I might even be the one to start a holy war on the subject.

There's no need for an "It Depends" on these questions at all. The answer to the first question is "Absolutely" and the answer to the second question is usually a very resounding "NO".

If you look at the masters of the trade such as Peter Larsson, Phil Factor, or a couple of the more well known true Speed Phreaks and heavy hitters on this and other forums, then the answer to the second question is absolutely "NO"!!! How can I say that in light of some of the contests they're in and some of the attitudes they take on in the forums about code speed being so very important? The answer is because behind the scenes, they know a couple of things that a whole lot of other people don't...

The first they know is how to write super high performance code all the time without even trying anymore because they've done something that a lot of other folks either haven't had the time to do (but need to) or simply won't do... PRACTICE and EXPERIMENT. They do this on forums, in contests, and just for fun much like a concert pianist would practice his/her trade. Why do you think so many SQL Ninjas show up for such contests? Just for the fun of it and to have the opportunity to learn something new!

The second thing is that, as Phil Factor implied above, these masters of the trade know when saving an extra hundred milliseconds over a million rows actually will matter and when letting something run for an hour won't. Most folks whom I've had to work with that say "good enough" and move on really don't know if it's good enough or not. All they know is that it's good enough for them and the 10 rows they supposedly tested on and are happy that their 10 rows only took 100 milliseconds to run. That's if they even bothered to check.

So... let's answer each question separately...

Is there such a thing as too much attention to the performance of SQL?


Absolutely. The problem is that a whole lot of people don't know what "not enough attention" means which is actually a much larger problem by orders of magnitude.

Are we in danger of wasting time on optimising code when it is entirely unnecessary?

No. Only the people who fall into the category of not knowing what enough attention is will ever think that optimization is "entirely unnecessary" and are the only ones that will ever actually complain about it. That problem is not limited to developers... a lot of managers I know fall into that same category and the company ends up paying a heavy price down the road because of them.


--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 #965611
Posted Saturday, August 7, 2010 7:39 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
Here's a perfect example of what I'm talking about...

http://www.sqlservercentral.com/Forums/Topic965539-8-1.aspx

You can just bet that person thought "good enough" when he started writing that "code". Now look where he's at... he's using the "urgent" word.

This isn't an isolated incident either. This forum and hundreds of other forums have thousands and thousands of similar posts. I've only seen one thread in my entire life where someone complained that the code actually ran too fast and "can you slow it down".

Let's stop worrying about the people that want to optimize code even if it's to a fault. There are too many other people who either don't give a damn enough to learn to not use a cursor for things like a simple update or just don't know because SQL isn't their "first language". Spend your time educating them instead of bitching about people who already know how to do their job.


--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 #965613
Posted Sunday, August 8, 2010 7:57 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: Sunday, October 6, 2013 9:36 AM
Points: 568, Visits: 309
For the development in our systems, we have a set of guidelines for duration, frequency of being called and number of records returned. Once the query or sp comes within those guidelines, then we go into code review and unit testing. While not perfect and it being tweaked over time, it has served us well so far in preventing performance bottlenecks. The developers hated it at first because it made then work harder, but after a few sprints they saw the value in doing it--less occurences of the boss looking over their shoulder when it was a crisis.


Accepting all invites
Post #965651
Posted Sunday, August 8, 2010 8:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
Phil Factor (8/7/2010)

The reason we like doing the SQL Speed Phreak contests is because it is such a good way to illustrate how a database developer approaches performance optimization. It is a different approach to the DBA.


Phil, can you elaborate on what are these differences?
Post #965654
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse