Swedish SQL Server User Group

Tonight I stepped out of my comfort zone and joined a meeting with the Swedish SQL Server User Group. About 40 people joined the meeting in central Stockholm and people from Göteborg, Malmö and Helsingborg joined online via Zoom (hmmm...)

The main reason for me listening to this is that my cousin’s cousin Magnus Ahlkvist presented at the meeting.

Databases,beard and beer - does it get better than this?

Title: RBAR bad. Sets good.
Presented by: Magnus Ahlkvist (@MagnusAhlkvist)

Session summary:
In this session, I will go over some way too common uses of RBAR (Row by Agonizing Row) processing and show different examples of how they can be replaced with a set based approach. We will look at performance comparisons and talk about code maintainability. Most of the examples are war stories from my 20 years working with databases. Unfortunately, many of the bad examples come from my own code. Fortunately, many of the good examples also comes from my own code, fixing the bad code.

My Notes:
RBAR stands for "Row By Agonizing Row" and means we did not do something in an optimal way. We should not deal with a database row-by-row rather by set-by-set.

RBAR by T-SQL loops

Don't do loops in T-SQL

Populating 1000 rows with weekdays using a loop took 812 ms with some example code.

Using Tally Tables and window functions the same operation (or result) took 505 ms.

Running all commands in one single transaction brought down the time to 16 ms.

RBAR by T-SQL functions

To split strings have been tricky with T-SQL, but in SQL Server 2016 a STRING_SPLIT function was added. T-SQL is not an optimal language to do string manipulations.

RBAR in optimization plans

The session was basically several demos of T-SQL code explaining how to optimize the code to make the queries run faster and more efficient. Even though I did not understand all the examples and all the code the presentation and demos seemed very appreciated by the audience present.

If you would like to look at the scripts in detail you can find them at Magnus blog.

Good job Magnus!

After the session we had a good time hanging with some hardcore DBA nerds telling jokes such as: In the nineties everyone was chatting on IRC - except Lotta who was using UTF-8 (it only makes sense in Swedish - but it was super funny!!!)