Six SQL Server performance tuning tips from Pinal Dave

Recently, I’ve needed to optimize some SQL Server queries. I decided to look out there what to do to tune SQL Server and SQL queries.

I found Pinal Dave from SQLAuthority. Chances are you have already found one of his blog posts when searching for anything related to SQL Server. He’s been blogging about the subject for years. These are six tips from his blog and his online presentations I’ve applied recently.

Enable automatic statistics update

Turn on automatic update of statistics. You should turn it off if you’re updating a really long table during your work-hours. You can enable automatic statistic update with this query:

USE <YourDatabase>;
GO

-- Enable Auto Create of Statistics
ALTER DATABASE <YourDatabase>
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT;

-- Enable Auto Update of Statistics
ALTER DATABASE <YourDatabase>
SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;
GO

-- Update Statistics for whole database
EXEC sp_updatestats
GO

Fix File Autogrowth

Add size and file growth to your database. Make it your weekly file growth. Otherwise set it to 200 or 250MB. You can change the file autogrowth from SQL Server Management Studio.

Find and Fix Implicit conversions

Implicit conversions happen when SQL Server needs to convert between two data types in a WHERE or in JOIN. For example, the query SELECT * FROM dbo.Orders WHERE OrderNumber = 123 with OrderNumber as a VARCHAR(20) column has implicit warning when compared to an integer.

To decide when implicit conversion happens, you can check Microsoft Data Type Precedence table. Types with lower precedence convert to types with higher precedence. For example, VARCHAR will be always converted to INT.

You can use this script to indentify queries with implicit conversion.

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], 
t.text AS [Query Text],
qs.total_worker_time AS [Total Worker Time], 
qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
qs.max_worker_time AS [Max Worker Time], 
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 
qs.max_elapsed_time AS [Max Elapsed Time],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads], 
qs.execution_count AS [Execution Count], 
qs.creation_time AS [Creation Time],
qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE ('%CONVERT_IMPLICIT%')
 AND t.[dbid] = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

Change compatibility level

After updating your SQL Server, make sure to update the compatibility level of your database to the highest level supported by the current version of your SQL Server. You can check SqlAuthority blog on how to change compatibility level.

ALTER DATABASE <YourDatabase>
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

Create missing indexes

But, don’t create all missing indexes. Create the first 10 missing indexes. You should have only ~5 indexes per table. You can use the next script to find the missing indexes in your database.

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Delete most of your indexes

Indexes reduce perfomance all the time. They reduce performance of inserts, updates, deletes and selects. Even if a query isn’t using an index, it reduces performance of the query.

Delete most your indexes. Identify your main table and check if it has more than 5 indexes. Don’t create indexes on every key of a join.

Also, keep in mind if you rebuild an index for a table, SQL Server will remove all caches related to that table. Rebuilding your indexes is the most expensive way of updating statistics.

You can find your unused indexes with this script:

SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

Happy SQL time!

BugOfTheDay, The slow room search

Another day at work! This time, the room search was running slow. For one of the big clients, searching all available rooms in a one-week period took about 15 seconds.

This room search was a public page to book a room into a hotel without using any external booking system. This page used an ASP.NET Core API project to combine data from different microservices.

Room type details

At first glance, I found an N + 1 SELECT. This is a common anti-pattern. The code called the database per each element from the input set to find more details about each item. This N + 1 SELECT was in the code to find the details of each room type. The code looked something like this:

public async Task<IEnumerable<RoomTypeViewModel>> GetRoomTypesAsync(int hotelId, IEnumerable<int> roomTypeIds)
{
    var tasks = roomTypeIds.Select(roomTypeId => GetRoomTypeAsync(hotelId, roomTypeId));
    var results = await Task.WhenAll(tasks);
    return results;
}

public async Task<RoomTypeViewModel> GetRoomTypeAsync(int hotelId, int roomTypeId)
{
    var endpoint = BuildEndpoint(hotelId, roomTypeId);
    var roomClass = await _apiClient.GetJsonAsync<RoomTypeViewModel>(endpoint);
    return roomClass;
}

These two methods made a request per each room type found, instead of searching more than one room types in a single call. I decided to create a new method to receive a list of room types.

I cloned the existing method in the appropriate microservice and renamed it. The new method received an array of room types. Also, I removed all unneeded queries from the store procedure it used. The store procedure returned three results sets. But, the room search only used one.

Before any change, it took ~4 seconds to find a single room type. But, with the new method, it took ~600ms to find more than one room type in a single request. The client facing the problem had about 30 different room types. Hurray!

After this first change, the room search made a single request to find all room types.

public async Task<IEnumerable<RoomTypeViewModel>> GetRoomTypesAsync(int hotelId, IEnumerable<int> roomTypeIds)
{
    var endpoint = BuildEndpoint(hotelId);
    var roomTypes = await _apiClient.PostJsonAsync<object, IEnumerable<RoomTypeViewModel>>(endpoint,  roomTypeIds);
    return roomTypes;
}

When calling the room search from Postman, the execution time didn’t seem to improve at all. These were some of the times for the room search for one week. What went wrong?

Room Type Time in seconds
Before 16.95 18.39 17.13
After 19.48 17.61 18.65
Encuentro Guadalupe, El Porvenir, Mexico. Photo by Manuel Moreno on Unsplash

Premature optimization

To check what happened, I stepped back and went to the room search method again. The room search method looked for all available rooms, the best rates and any restrictions to book a room. This method was something like this:

[Authorize]
[HttpGet]
public async Task<IActionResult> RoomSearchAsync([FromQuery] RoomSearchRequest request)
{   
    var hotelTimeZone = await GetHotelTimeZoneAsync(/* some parameters */);

    var roomListTask = GetRoomListAsync(/* some parameters */);
    var ratesTask = GetRatesAsync(/* some parameters */);
    var rulesTask = GetRulesAsync(/* some parameters */);

    await Task.WhenAll(roomListTask, ratesTask, propertyRulesTask);

    var roomList = await roomListTask;
    var rates = await ratesTask;
    var rules = await rulesTask;

    var roomSearchResults = await PrepareRoomSearchResultsAsync(rates, /* some parameters */);

    var result = new RoomSearchResponse
    {
        Rooms = roomSearchResults,
        Rules = rules
    };

    return Ok(result);
}

To find any bottlenecks, I wrapped some parts of the code using the Stopwatch class. The Stopwatch measures the elapsed time of a method. For more details, see the Stopwatch documentation.

On one hand, the log with the execution times before any change looked like this:

GetHotelTimeZoneAsync: 486ms
Task.WhenAll: 9641ms
    GetRatesAsync: 9588ms
    GetRoomListAsync: 7008ms
        FindAvailableRoomsAsync: 2792ms
        GetRoomTypesAsync:       4204ms
    GetRulesAsync: 3030ms
GetRoomTypeGaleriesAsync: 8228ms

But, the log after using the new method for room type details showed why it didn’t seem to improve. The log looked like this:

GetHotelTimeZoneAsync: 616ms
Task.WhenAll: 8726ms
    GetRatesAsync: 8667ms
    GetRoomListAsync: 4171ms
        FindAvailableRoomsAsync: 3602ms
        GetRoomTypesAsync:        559ms
    GetRulesAsync: 4223ms
GetRoomTypeGaleriesAsync: 11486ms

The GetRoomTypesAsync method run concurrently next to the GetRatesAsync method. This last one was the slowest of the three methods inside the Task.WhenAll. That’s why there was no noticeable improvement even though the time of the room type call dropped from 4204ms to 559ms.

“Premature optimization is the root of all evil”

-Donald Knuth

I was looking at the wrong place! I rushed to optimize without measuring anything. I needed to start working either on GetHotelTimeZoneAsync or GetRoomTypeGaleriesAsync.

This time to gain noticeable improvement, I moved to the GetRoomTypeGaleriesAsync method. Again, this method called another microservice. The code looked like this:

public async Task<IEnumerable<RoomGallery>> GetRoomGalleriesAsync(IEnumerable<int> roomTypeIds)
{
    var roomGalleries = await _roomRepository.GetRoomImagesAsync(roomTypeIds);
    if (!roomGalleries.Any())
    {
        return Enumerable.Empty<RoomGallery>();
    }

    var hotelId = roomGalleries.First().HotelId;
    var roomGalleriesTasks = roomGalleries
        .Select(rg => Task.Run(()
            => MapToRoomGallery(rg.RoomTypeId, hotelId, roomGalleries)));

    return (await Task.WhenAll(roomGalleriesTasks)).AsEnumerable();
}

private RoomGallery MapToRoomGallery(int roomTypeId, int hotelId, IEnumerable<RoomImageInfo> roomGalleries)
{
    return new RoomGallery
    {
        RoomTypeId = roomTypeId,
        HotelId = hotelId,
        Images =  roomGalleries .Where(p => p.RoomTypeId == roomTypeId)
            .OrderBy(x => x.SortOrder)
            .Select(r => new Image
            {
                // Some mapping code
            })
    };
}

The MapToRoomGallery method filtered the collection of result images with every element. It was a nested loop over the same collection, an O(nm) operation. Also, since all the code was synchronous, there was no need for Task.Run and Task.WhenAll.

To fix this problem, the code grouped the images by room type first. And, then it passed a filtered collection to the mapping method.

public async Task<IEnumerable<RoomGallery>> GetRoomGalleriesAsync(IEnumerable<int> roomTypeIds)
{
    var images = await _roomRepository.GetRoomImagesAsync(roomTypeIds);
    if (!images.Any())
    {
        return Enumerable.Empty<RoomGallery>();
    }

    var hotelId = images.First().HotelId;
    var imagesByRoomTypeId = images.GroupBy(t => t.RoomTypeId, (key, result) => new { RoomTypeId = key, Images = result });
    var roomGalleries = imagesByRoomTypeId.Select(rg =>
    {
        return MapToRoomGallery(rg.RoomTypeId, hotelId, rg.Images);
    });
    return roomGalleries;
}

private RoomGallery MapToRoomGallery(int roomTypeId, int hotelId, IEnumerable<RoomImageInfo> roomGalleries)
{
    return new RoomGallery
    {
        RoomTypeId = roomTypeId,
        HotelId = hotelId,
        Images =  roomGalleries.OrderBy(x => x.SortOrder)
            .Select(r => new Image
            {
                // Some mapping code
            })
    };
}

After changing those three lines of code, the image gallery times went from ~4sec to ~900ms. And, the initial room search improved in ~2-3sec. The client with the slowness had about 70 images. So, it was a good move.

These are the times of three requests to the initial room search using Postman:

Room Gallery Time in seconds
Before 13.96 13.49 17.64
After 11.74 11.19 11.23

When checking the log, the room search had a noticeable improvement for the GetRoomClassGaleriesAsync method. From ~8-11s to ~3-4s.

GetHotelTimeZoneAsync: 182ms
Task.WhenAll: 8349ms
    GetRatesAsync: 8342ms
    GetRoomListAsync: 2886ms
        FindAvailableRoomsAsync: 2618ms
        GetRoomTypesAsync:        263ms
    GetRulesAsync: 2376ms
GetRoomClassGaleriesAsync: 3586ms

Room rates

To make things faster, I needed to tackle the slowest of the methods inside the Task.WhenAll, the GetRatesAsync method. It looked like this:

protected async Task<IEnumerable<BestRateViewModel>> GetRatesAsync(int hotelId, /* some other parameters */)
{
    var bestRatesRequest = new BestRatesRequest
    {
        // Some mapping code
    };
    var bestRates = await _rateService.GetBestRatesAsync(bestRatesRequest);
    if (!bestRates.Any())
    {
        return Enumerable.Empty<BestRateViewModel>();
    }

    await UpdateRateDetailsAsync(bestRates, rateIds);
    await UpdatePackagesAsync(bestRates, hotelId);

    return bestRates;
}

Also, I logged the execution time of three more methods inside the GetRatesAsync. The log showed these entries:

GetHotelTimeZoneAsync: 530ms
Task.WhenAll: 12075ms
    GetRatesAsync: 12060ms
        GetBestRates: 5075ms
        UpdateRateDetailsAsync: 5741ms
    UpdatePackagesAsync: 1222ms
    GetRoomListAsync: 3774ms
        FindMultiAvailableRoomsAsync: 2555ms
        GetRoomTypesAsync:            1209ms
    GetRulesAsync: 2772ms
GetRoomClassGaleriesAsync: 4851ms

Among the inner methods was the UpdateRateDetailsAsync method. This method called an endpoint in another microservice for rates details. The method was something like this:

[HttpPost]
public async Task<IEnumerable<RateDetailViewModel>> GetRateDetailsAsync([FromBody] int[] rateIds)
{    
    var results = new List<RateDetailViewModel>();
    foreach (var rateId in rateIds)
    {
        if (rateId <= 0)
        {
            results.Add(new RateDetailViewModel
            {
                RateId = rateId,
                Error = $"Invalid Rate Id: {rateId}"
            });
            continue;
        }
        
        try
        {
            var result = await _rateService.GetRateDetailAsync(rateId);
            results.Add(new RateDetailViewModel
            {
                RateId = rateId,
                Data = result
            });
        }
        catch (Exception e)
        {
            results.Add(new RateDetailViewModel
            {
                RateId = rateId,
                Error = e.Message
            });
        }
    }

    return results;
}

Again, the N + 1 SELECT anti-pattern. The initial client had ~10 rates, it means 10 separate database calls. To fix this issue, the code validated all input ids and returned early if there wasn’t any valid id to call the database. Then, it made a single request to the database. Either it succeeded or failed for all the input ids.

[HttpPost]
public async Task<IEnumerable<RateDetailViewModel>> GetRateDetailsAsync([FromBody] int[] rateIds)
{
    var results = rateIds.Where(rateId => rateId <= 0)
                        .Select(rateId => new RateDetailResultViewModel
                        {
                            RateId = rateId,
                            Error = $"Invalid Rate Id: {rateId}"
                        });
    if (results.Count() == rateIds.Length)
    {
        return results;
    }

    var validRateIds = rateIds.Where(rateId => rateId > 0);

    try
    {
        var details = await m_rateService.GetRateDetailsAsync(validRateIds);
        results = results.Concat(BuildRateDetailViewModels(validRateIds, details));
    }
    catch (Exception e)
    {
        var resultViewModels = validRateIds.Select(rateId => new RateDetailViewModel
        {
            RateId = rateId,
            Error = e.Message
        });
        results = results.Concat(resultViewModels);
    }

    return results;
}

After removing this N + 1 SELECT, the execution time of getting details for ~10 rates went from ~1.6s to ~200-300ms. For three consecutive calls, these were the times of calling the modified rate details method from Postman

Room Rates Time in milliseconds
Before 1402 1868 1201
After 198 272 208

Also, the room search improved in ~2-3sec for 1-week range. The log showed these improvements too.

GetHotelTimeZoneAsync: 194ms
Task.WhenAll: 8349ms
    GetRatesAsync: 10838ms
        GetBestRates: 8072ms
        UpdateRateDetailsAsync: 2198ms
        UpdatePackagesAsync: 557ms
    GetRoomListAsync: 3207ms
        FindAvailableRoomsAsync: 2982ms
        GetRoomTypesAsync:        218ms
    GetRulesAsync: 3084ms
GetRoomClassGaleriesAsync: 6370ms

It was the last low-hanging fruit issue I addressed. After the above three changes, the initial room search went from ~15-18sec to ~10-14sec. It was ~5 seconds faster. These were the times of three requests to the room search:

All changes Time in seconds
Before 16.95 18.39 17.13 15.36
After 11.36 10.46 14.62 10.38

Conclusion

From this task, I learned two things. First, don’t assume a bottleneck is here or there until you measure it. And, avoid the N + 1 SELECT anti-pattern and nested loops on collections.

I didn’t mess with any store procedure or query trying to optimize it. But, I had some metrics in place and identified which the store procedures to tune.

The next step I tried was to cache the hotel details. You can take a look at my post on how to add a caching layer with Redis and ASP.NET Core for more details.

Happy coding!

In case of emergency, break the glass. Three debugging tips

What do you do when you’re facing a problem or have to fix a bug? Here you have three debugging tips.

Isolate your problem

A coworker always says Isolate your problem! when you ask him for help. He’s right!

Start by removing all irrelevant parts from your problem. Is the problem in your database layer? In your JavaScript code? In your API controllers? Create an unit or integration test to recreate the conditions of your problem and the input that triggers it.

Stop and think

One of my takeaways from the book Pragmatic Thinking and Learning is thinking how to deliberately create the bug you’re facing. Run experiments to test your hypothesis.

Debugging is thinking. Pen and paper are your friends. But, sometimes you have to explain your problem to somebody else. And, the answer seems to come by magic. You don’t have anyone around? Think out loud or explain it to a rubber duck.

Photo by Timothy Dykes on Unsplash

Find how others have solved your problem

Chances are somebody else has already faced and solved the same problem or a similar one. If you look out there, you might find an StackOverflow answer or a GitHub issue. Don’t blindly copy and paste any code without understanding it first. And, always prefer battle-tested solutions.

Happy debugging!

Just Vim It! Learning Vim For Fun and Profit

Have you ever heard about Vim? You might know it only as the text editor you can’t even close if you don’t know a key combination. But, once you know Vim, you can edit text files at the speed of light. Let’s see why you should learn it and how to start using it!

What is Vim?

From Vim’s official page, Vim

“…is a highly configurable text editor built to make creating and changing any kind of text very efficient”.

Vim is short for Vi IMproved. Vim is a fork of the Unix vi editor. Vim is free and open-source. It dates back to the times where the arrow keys and the Esc key were in the middle row of keyboards.

Vim is a command line editor. However, you can find it these days outside the command line with a graphical user interface. You can even bring the Vim experience to some IDE’s using plugins or extensions.

What makes Vim different?

Vim is a distraction-free text editor. You won’t find fancy toolbars full of icons. You will find a blank canvas ready to start.

Vim works in three modes: normal, insert, and visual. In each mode you can perform only certain type of actions. In normal mode, you can run commands on your text; copy and paste, for example. In insert mode, you can type words and symbols. In visual mode, you can select text.

Vim includes the concept of macros. You can record a sequence of actions and repeat it over a stream of text. Using macros, you don’t need to retype the same key combination over and over on the text you want to edit.

Vim integrates with your command line. If you need to compile, run your tests or do any other action in the command line, you can do it without leaving your editor. Even you can import the output of a command right into your text.

Vim can be extended and customized. You can bring your favorite color scheme and define your own key shortcuts. There are lots of plugins to enhance your experience with Vim.

Macro typewriter ribbon
Photo by Dung Anh on Unsplash

Why you should learn it? A note on Productivity

Vim helps you to stay away from your mouse. It reduces the small context switching of reaching your mouse making you a bit faster. You can move inside your files without leaving your keyboard. Your hands will be in the middle row of your keyboard.

Vim can go to almost anywhere in a file with a few keystrokes. You can go to the previous or next word, to the beginning or end of your line and file, to any character in the current line.

Vim uses text object motions. You can copy, change or delete anything inside parenthesis, braces, brackets and tags.

Let’s say your cursor is at the beginning of a line and you need to change the parameter list of a method. How would you do it? What keystrokes do you need?

Without Vim, you place your cursor in the opening parenthesis with the mouse. Then, while holding Control and Shift, you press the right arrow until the closing parenthesis.

Change the parameter list of a method without Vim
Change the parameter list of a method without Vim

But, with Vim, you need fewer keystrokes. You go to the opening parenthesis with f(. Then press ci( to change everything inside the parenthesis. Faster, isn’t it?

Change the parameter list of a method with Vim inside Visual Studio
Change the parameter list of a method with Vim inside Visual Studio

How to start learning Vim

Vim has a step learning curve. Don’t try to do your everyday work with Vim right from the beginning. It can be frustrating and unproductive.

Start editing one of your files from the command line or from an online Vim emulator outside of your everyday work.

If you are working in an Unix-based operating system, chances are you have Vim already installed. If not, you can install it from its official site or with a package manager, such as Brew, apt-get or chocolatey, depending on your operating system.

How to exit Vim

Let’s answer the question from the beginning of the post once and for all.

First, how can you exit Vim if, by any change, you end up inside it? Did you try to commit from the command line without changing the default editor? Press :q! and Enter. It will exit Vim and discard any changes. That’s it!

To exit saving your changes, press :wq and Enter. Also, you can press ZZ and Enter. Your changes will be saved this time.

How to enter and exit modes

You need to switch back and forth between the three modes: normal, insert and visual modes. In a normal text editor, you work in the three modes at the same time. But with Vim, you need to switch between them.

To open a file with Vim, from the command line type vim <your-file>. Change <your-file> with the actual name of the file. When you open a file, you will be in normal mode.

To start editing your file, you need to switch to insert mode. Press i. You will see the -- INSERT -- label in the status bar. Now, you can type any text. To exit insert mode, press Esc. You’re back to normal mode.

To select text, switch to visual mode pressing v. The status bar will display -- VISUAL --. You can select lines of text like using the mouse. Again, to switch back to normal mode, you need Esc. Don’t worry once you’re used to these modes, you will switch almost unconsciously.

How to move through a file

With Vim you can use the arrow keys to move your cursor around. But, to keep your hands in the middle row, you can use h, j, k, l instead of Left, Down, Up and Right, respectively.

Instead of using arrow keys to move the cursor one position at a time, learn some basic motions. These are some of them:

Vim Action
b Go to the previous word
w Go to the next word
0 Go to the beginning of the current line
$ Go to the end of the current line
gg Go to the beginning of the file
G Go to the end of the file
<number>gg Go to line with number <number>
f<char> Go to the next instance of <char> in the current line
I Go to the beginning of the current line and enter insert mode
A Go to the end of the current line and enter insert mode
{ Go to the previous paragraph
} Go to the next paragraph

For example, if you forgot to add a semicolon in a line, in normal mode you would need to type

Vim Action
<number>gg Go to the line number <number>. The line missing the semicolon
A Go to the end of the line and enter insert mode
; Insert ;
Esc Go back to normal mode
Add a missing semicolon to a line with Vim
Add a missing semicolon to a line with Vim

How to copy and paste

A common task while programming and editing text in general is to copy and paste. Vim can copy and paste too. Well, how do we copy and paste with Vim? Vim calls these actions yank and put.

To copy, enter visual mode, select some text and press y. Then move to the desired place and press p. Vim will put the yanked text below the cursor position. You can copy an entire line with yy.

What about cut and paste? Vim deletes and puts. To cut and paste, instead of pressing y to copy, you need to use d to delete. Then, p to put the cut text in the desired location. Similarly, to delete an entire line, you need dd.

Besides y and d, you can change some text with c. It will remove the selected text and enter insert mode.

Text objects

You can use y, d and c to edit text inside parenthesis (, braces {, brackets [, quotes '" and tags <>. These patterns of text are called text objects.

Text objects are useful to edit text inside or around some programming constructs. Parameter list, elements of an array, an string, a method body and everything inside an HTML tag.

That’s why to change a parameter list, you can use ci( to change everything inside ().

How to start using Vim?

Once you are comfortable editing and moving around your files with Vim, you can use it inside your IDE. You can start using Vim inside Visual Studio installing VsVim extension and inside Visual Studio Code with VSCodeVim.

Make sure You might want to have a cheatsheet next to you. So you can look up command if you get stuck. Don’t try to learn all the commands at once. Learn one or two commands at a time and practice them.

Conclusion

Voilà! Now you know how to exit Vim, to switch between modes and move around a file.

There are more things to cover, like searching and replacing, undoing and redoing, and using tabs, among other features. It’s true that Vim has a step learning curve. Give it a try! You might find yourself Viming the next time.

This post was originally published on exceptionnotfound.net as part of the Guest Writer Program. I’d like to thank Matthew for editing this post.

Happy Vim time!

How I got rid of two recurring code review comments?

During code review, one or two of your coworkers look at your code to spot any potential issues and to check if the code follows existing conventions. Sometimes, code review ends up checking style issues. Brackets in the same line, disorganized using statements, extra blank lines. You can use extensions on your IDE or linters to format your code.

For a project I was working on, I had to include the ticket number in every commit message and add Async suffix to all asynchronous C# methods. I used a Git hook to add the ticket number in the commit messages. And, an .editorconfig file (and, alternatively an extension) to raise an error if the Async suffix was missed.

Ticket number in commit messages

A Git hook can format the commit message before commiting the changes. You can define Git hooks globally or per projects.

I was already including the ticket number in my feature branches. A bash script can read the ticket number from the branch name and prepends it to the commit message.

#!/bin/bash
FILE=$1
MESSAGE=$(cat $FILE)
TICKET=[$(git branch --show-current | grep -Eo '^(\w+/)?(\w+[-_])?[0-9]+' | grep -Eo '(\w+[-])?[0-9]+' | tr "[:lower:]" "[:upper:]")]
if [[ $TICKET == "[]" || "$MESSAGE" == "$TICKET"* ]];then
  exit 0;
fi

echo "$TICKET $MESSAGE" > $FILE

I wrote about this hook on my list of Programs that saved you 1000 hours. Also, you can find Git aliases, Visual Studio extensions and other online tools to save you some valuable time.

Async suffix on asynchronous C# methods

I used an .editorconfig file to enforce naming conventions on the C# project. After Googling, a coworker came up with this StackOverflow answer to get an error if I forgot to include the Async suffix on any C# methods.

[*.cs]

# Async methods should have "Async" suffix
dotnet_naming_rule.async_methods_end_in_async.symbols = any_async_methods
dotnet_naming_rule.async_methods_end_in_async.style = end_in_async
dotnet_naming_rule.async_methods_end_in_async.severity = error

dotnet_naming_symbols.any_async_methods.applicable_kinds = method
dotnet_naming_symbols.any_async_methods.applicable_accessibilities = *
dotnet_naming_symbols.any_async_methods.required_modifiers = async

dotnet_naming_style.end_in_async.required_prefix = 
dotnet_naming_style.end_in_async.required_suffix = Async
dotnet_naming_style.end_in_async.capitalization = pascal_case
dotnet_naming_style.end_in_async.word_separator =

The .editorconfig forces to include the Async suffix even in your Main method and in your tests methods. It hurts readability of your tests. And MainAsync looks weird. It misses methods returning Task or Task<T> in interfaces.

Instead, I found the AsyncMethodNameFixer extension. It makes you to include the Async suffix in only methods and interfaces. It doesn’t catch the Main method and test methods. But, it relies on developers to have this extension installed to keep consistency in your project. With the .editorconfig your naming rules travels with the code itself.

Voilà! That’s how I got rid of these two recurring comments while code review. You can read my Tips and Tricks for Better Code Reviews. For more extensions to make you more productive with Visual Studio, check my Visual Studio Setup.

Happy coding!