T-SQL Requires the Right Approach

Question

Post reply

T-SQL Requires the Right Approach

Kathi Kellenberger

SSChasing Mays

Points: 601
More actions
January 15, 2022 at 12:00 am

#3973167

Comments posted to this topic are about the item T-SQL Requires the Right Approach
January 15, 2022 at 1:23 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

Stugle Newbie Points: 2 More actions · Answer 1

Great article, I’ve made an accidental career out of sorting stuff like this.

If you ever want to see over use of functions check out Great Pains (whoops Plains) 2010.

skeleton567 SSCertifiable Points: 5876 More actions · Answer 2

This is exactly why I am not in favor of the idea of the 'teaching coding' approach. While I'm not against providing opportunities in IT I believe that these opportunities need to be provided in serious learning situations in public or private institutions of learning. As a DBA and SQL Developer I many times had to review, correct, and improve SQL code developed even by seasoned front-end developers with years of experience.

SQL coding is not something to be taken lightly in serious development situations. When I need heart surgery I'm not going to a surgeon who got his training in Heart Surgery Bootcamp.

Sorry if this offends some of you, but I have had to dismiss new developers who just couldn't cut it (pun intended).

This reply was modified 2 years, 11 months ago by skeleton567. Reason: grammer

Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 3

Nice article, Kathi.

Your description of "Some of the most problematic T-SQL code I’ve seen" is a perfect description for simple "RBAR".

And, I agree with Rick. The way SQL is taught needs to seriously change. For imperative languages, folks are first taught to get to the point of displaying "Hello World". Then they taught some syntax with the goal of producing a loop that counts from 1 to some number because the very fiber of computers is based on repetition.

They need to do the same when teaching SQL. Do that with a WHILE Loop and draw the flow chart for it. Then state that's the wrong way to do things and introduce SELECT as the "Pseudo-Cursor" looping-behind-the-scenes "macro" that it is to demonstrate that SQL removes the need for loops and you need to think "the next level up" and that the "secret" to writing high performance code is to simply write SQL that lets the hidden "code monkey" known as the optimizer do it's absolute best without us putting obstacles in the way.

At the very least, they also need to teach the paradigm shift necessary to begin writing good SQL and I have that in my signature line below. It's not the end all to be all but it's a start.

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

rfulbrig SSC Rookie Points: 35 More actions · Answer 4

When I first started using SQL I quickly learned that CURSORS were not the ideal solution to most problems, but it was appealing because it resembled the programming approach that I had been using for years. CURSORS have their place, but should not be the first go-to solution. Learning SET theory freed me from the tyranny of RBAR (pronounced "ree-bar", Row By Agonizing Row) development.

Roy Fulbright
Computer Consultant

EdVassie SSC Guru Points: 60479 More actions · Answer 5

Kathi's article is good at highlighting one of the main frustrations that database people have with app developers, and equally one of the main frustrations app developers have with database people.

App developers face two hurdles (at least) in doing efficient DB code

A) Most IDEs especially Visual Studio are oriented around doing procedural code and it takes developer effort to get the IDE to produce set-based code. When tied into development techniques that require generation of test routines for each process then it is even harder to do set-based SQL.

DB people will continue to face an uphill battle to try to get Devs to bend the IDE suggestions and put together set-based code. Often the only time this gets done is when the procedural code is accepted as far too slow and after the database has been blamed for csusing this.

B) Many program specs (agile or otherwise) are written in some form of procedural shorthand. Those unlucky enough to be developing in a Prince-style environment will often get pseudocode written by analysts who often do not understand the complexities of coding (such as error handling) but expect to see an end result that is compatible with their 'wonderful' logic. The poor app developer really struggles to justify moving away from the procedural spec. I have worked in situations where the analyst was unhappy I dared to put common processes into dedicated functions rather that do everything in-line. My personal answer was I do it like that or you get someone else to do it your way, but not all Devs want to risk their job in this way.

To conclude, DB folk of almost every platform are going to keep seeing procedural DB code, and if we can re-factor the worst excesses we will probably have done the best job we can.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 6

rfulbrig wrote:

RBAR (pronounced "ree-bar", Row By Agonizing Row) development.

Heh... I kinda knew 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.

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)

rob.kaye Valued Member Points: 72 More actions · Answer 7

Spot on Kathi! We need to keep spreading the word.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

Another problem I’ve seen frequently is the overuse of user-defined functions (UDFs), especially nesting scalar UDFs.

scalar UDFs, nested or not, tend to burn the house down. When used in a query or even as a column constraint, for computed or in a view (indexed or not) - their very existence ruins execution plans; Eric Darling has two great articles on this topic.

with SQL 2019 we have scalar inlining, a huge leap forward, but it’s far from perfect.

I’ve found that turning scalar UDFs into in-line table value functions to be one of the fastest “low-hanging fruit” performance tuning techniques. Often the easiest way to improve performance by a factor of 10, 100, 1000 or more!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Ross McMicken SSCarpal Tunnel Points: 4438 More actions · Answer 9

Biggest arguments I've had with developers :

Insistence on putting all of the logic in C#, with no consideration given to whether a stored procedure would be better. Debugging LINQ to SQL is much more difficult than debugging SQL, and it's pretty easy to write LINQ that generates non-performant SQL

Creating an EAV table so "we won't have to create a new table ever again". The EAV table can be great for storing small numbers of mapping values, but is awful, as most of us are aware, if your record count gets very large. I ha da C# developer scream at me that I was an idiot for changing his design. Apparently, C# makes using an EAV table easy, but that ignores performance issues.

Developers want to put stuff in code, but we deploy code only once a month. Stored procedures and other SQL objects can be deployed at almost any time.

C# developers don't think in sets. It's all read a record, process it then write a record.