The Rich Rewards of Tuning SQL

Question

The Rich Rewards of Tuning SQL

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 1

peter-757102 (12/30/2014)
During development writing code is only part of the story.
What I usually do is spend significant extra time in the data modelling phase.
With respect to performance, primarily how the data will be accessed and what this implies for the queries (even make some test queries to help my though process).
It can be surprising how much this influences decision making and clarifies problems before actual code writing even begins.
Once a good model and minimal indexing as part of this model is in place, writing efficient code it much easier.
Schema's developed this way sometimes differ quite a bit from what is the starting point (norm) and require less resources.

+1000, Peter. That's the way I try to do it as an FTE. What I normally see as a consultant, though, is that I don't get called until the product has been in production for a while and the performance problems have done the inevitable rearing of their heads and it's crunch time. It really doesn't matter if it's a big database or a little one. They all suffer from not doing what you said above and it goes downhill from there.

The usual telltale sign of what the "design process" was and who "designed" the database is the overwhelming presence of the NVARCHAR(255) and NUMERIC(18,0) data-types.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gary Varga SSC Guru Points: 82166 More actions · Answer 2

Comparing adding $2k iron against the cost of "fixing" the code misses out the long term cost of not "fixing" the code. People have mentioned "mindset" and I would have to say that it is akin to that which I am most concerned about in this scenario. Throwing tin at the problem leaves the following issues:

1) The people (whatever hat they wear folks!!!) who wrote it will continue to write poor code thus leading to further problems down the line i.e. continual production of technical debt.

2) Poorly written code tends to be more difficult to maintain.

3) Scaling out does not resolve performance on an individual call.

4) Poor implementation suggests lack of understanding. This is dangerous whether it refers to the technical or the business side.

Finally, I would like to challenge Steve's following statement:

Steve Jones - SSC Editor (11/1/2009)
...
The issue with many web developers is also talent. So many programmers don't know how to tightly tune code...

I don't think that the biggest issue is with tuning code, be it SQL or C# say, but writing clear, simple and clean code. Often the poorest performing code, in my experience, has been when the coder has not used the simplest or best practice code. When developers try to be cleverer than they are then they write very poor code. I'd rather have on my team a mediocre developer who wrote simple code than a "hot shot" who wrote convoluted code for both maintenance and performance reasons.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!