This Monday Links episode is about careers and developer life. Four reads. Let’s start with a rant on daily meetings. Enjoy!
Daily Standup Meetings are useless
I think I could have written this article myself. I’ve been in those daily meetings you want to finish as quickly as possible to get back to your life. -“I’m working on ABC, no blockers.” We need to stop doing daily meetings as a micromanaging strategy. Otherwise, let’s replace them with an email or a message in a group chat. That would be more productive for everyone. Read full article
Is tasking developers with creating detailed estimates a waste of company money?
I’ve worked in places where developers spend half a day estimating tasks and, they have to resend their estimates every time they change them. And, I’ve heard stories from past coworkers where they spend a whole day estimating tasks for the next 6-month period. This is a common thing and I don’t see an easy way out because “that’s how we have always done things here”.
Quoting the article about why project management techniques don’t work on software projects… “software development involves messy discovery of new tasks in the complex and abstract environment of code, which results in software development task durations being a ‘?’.”Read full article
Are you planning to change jobs? This article can help you to decide. One common factor is learning. Ask yourself if you’re learning new skills and if your learning is still valuable in another company. Quoting the article “Sometimes five years of experience is just… the same year of experience, five times over.”Read full article
Bring your own code
Have you ever been in a hiring process where you are asked to work on a coding exercise without getting clear instructions? This article shows an alternative to coding exercises: bring an already-written piece of code. I like this idea. Also, it shows hiring managers what to look for. Hiring managers…“you need to be comfortable with the fact that you’ll lose some candidates who are unwilling to do your assignment but would take a different option.”Read full article
Voilà! Another Monday Links about career and workplaces. I don’t know if this topic will become a trend in future “Monday Links.” Stay tuned to find it out.
SQL Server compares columns and parameters with the same data types. But, if the two data types are different, weird things happen. Let’s see what implicit conversions are and why we should care.
An implicit conversion happens when the data types of columns and parameters in comparisons are different. And SQL Server has to convert between them, following type precedence rules. Often, implicit conversions lead to unneeded index or table scans.
An implicit conversion that scans
Let’s see an implicit convention! For this, let’s create a new table from the StackOverflow Users table. But, this time, let’s change the Location data type from NVARCHAR to VARCHAR.
Let’s find all users from Colombia. To prove a point, let’s query the dbo.Users_Varchar table instead of the original dbo.Users table.
DECLARE@LocationNVARCHAR(20)=N'Colombia';SELECTId,LocationFROMdbo.Users_Varchar/* The column is VARCHAR, but the parameter NVARCHAR */WHERELocation=@Location;GO
Notice we have declared @Location as NVARCHAR. We have a type mismatch between the column and the variable.
Let’s see the execution plan.
StackOverflow users from Colombia
SQL Server had to scan the index on Location. But, why?
Warning sign on SELECT operator
Let’s notice the warning sign on the execution plan. When we hover over it, it shows the cause: SQL Server had to convert the two types. Yes! SQL Server converted between VARCHAR and NVARCHAR.
SQL Server data type precedence
To determine what types to convert, SQL Server follows a data type precedence order. This is a short version:
datetimeoffset
datetime2
datetime
smalldatetime
date
time
decimal
bigint
int
timestamp
uniqueidentifier
nvarchar (including nvarchar(max))
varchar (including varchar(max))
SQL Server convert “lower” types to “higher” types. We don’t need to memorize this order. Let’s remember SQL Server always has to convert VARCHAR to other types.
In our example, the VARCHAR type was on the column, on the left side of the comparison in the WHERE. It means SQL Server had to read the whole content of the index to convert and then compare. That’s more than 2 million rows. That’s why the index scan.
Let’s use the original dbo.Users table with Location as NVARCHAR and repeat the query. This time, switching the variable type to VARCHAR. What would be different?
DECLARE@LocationVARCHAR(20)='Colombia';SELECTId,Location/* We're filtering on the original Users table */FROMdbo.Users/* This time, the column is NVARCHAR, but the parameter VARCHAR */WHERELocation=@Location;GO
Now, the VARCHAR type is on the right of the comparison. It means SQL Server has to do one single conversion: the parameter.
Index Seek on Location index when finding all users from Colombia
This time we don’t have a yellow bang on our execution plan. And, we have an Index Seek. Not all implicit conversions are bad.
In stored procedures and queries, use input parameters with the same types as the columns on the tables.
To identify which queries on your SQL Server have implicit conversions issues, we can use the third query from these six performance tuning tips from Pinal Dave. But, after taking Brent Ozar’s Mastering courses, I learned to start working with the most expensive queries instead of jumping to queries with implicit convertion issues right away.
Voilà! Those are implicit conversions and why you should care. Let’s use input parameters with the right data types on your queries and store procedures. Otherwise, we will pay the performance penalty of converting and comparing types. Implicit conversions are like functions around columns, implicitly added by SQL Server itself.
Let’s remember that not all implicit conversions are bad. When looking at execution plans, let’s check how many rows SQL Server reads to convert and compare things.
There’s one thing we could do to write faster queries in SQL Server: don’t use functions around columns in WHERE clauses. I learned it the hard way. Let me share this lesson with you.
Don’t use user-defined or built-in functions around columns in the WHERE clause of queries. It prevents SQL Server from estimating the right amount of rows out of the function. Write queries with operators and comparisons to make SQL Server better use the indexes it has.
With functions around columns in WHEREs
To prove this point, let’s query a local copy of the StackOverflow database. Yes, the StackOverflow we all know and use.
StackOverflow has a Users table that contains, well…, all registered users and their profiles. Among other things, every user has a display name, location, and reputation.
Let’s find the first 50 users by reputation in Colombia.
To make things faster, let’s create an index on the Location field. It’s an NVARCHAR(100) column.
CREATEINDEXLocationONdbo.Users(Location);
This is the query we often write,
DECLARE@LocationNVARCHAR(20)=N'Colombia';SELECTTOP50DisplayName,Location,CreationDate,ReputationFROMdbo.Users-- Often, we put LOWER on both sides of the comparisonWHERELOWER(Location)=LOWER(@Location)-- ^^^^^ ^^^^^ORDERBYReputationDESC;GO
Did you notice the LOWER function on both sides of the equal sign?
We all have written queries like that one. I declared myself guilty too. Often, we use LOWER and UPPER or wrap the column around RTRIM and LTRIM.
But, let’s see what happened in the Execution Plan.
First 50 StackOverflow users in Colombia by reputation
Here, SQL Server chose to scan the index Location first. And let’s notice the width of the arrow coming out of the first operator. When we place the cursor on it, it shows “Number of Rows Read.”
Rows read by Index Scan on Location index
In this copy of the StackOverflow database, there are 2,465,713 users, only 463 of them living in Colombia. SQL Server had to read the whole content of the index to execute our query. Arrrggg!
It means that to find all users in Colombia, SQL Server had to go through all users in the index. We could use that index in a better way.
Write queries with comparisons, functions, and operators around parameters. This way SQL Server could properly use indexes and have better estimates of the contents of tables. But, don’t write functions around columns in the WHERE clauses.
The same is true when joining tables. Let’s not put functions around the foreign keys in our JOINs either.
Rewrite your queries to avoid functions around columns in WHERE
Let’s go back and rewrite our query without any functions wrapping columns. This way,
DECLARE@LocationNVARCHAR(20)=N'Colombia';SELECTTOP50DisplayName,Location,CreationDate,ReputationFROMdbo.Users-- We remove LOWER on both sidesWHERELocation=@LocationORDERBYReputationDESC;GO
And, let’s check the execution plan again.
Again, first 50 StackOverflow users in Colombia by reputation
This time, SQL Server used an Index Seek. It means SQL Server didn’t have to read the whole content of the Location index to run our query. And the execution plan didn’t go parallel. We don’t have the black arrows in yellow circles in the operators.
Let’s notice that this time we have a thinner arrow on the first operator. Let’s see how many rows SQL Server read this time.
Rows read by Index Seek on Location index
After that change, SQL Server only read 463 records. That was way better than reading the whole index.
Voilà! If we want to write faster queries, let’s stop using functions around columns in our WHEREs. That screws SQL Server estimates. For example, let’s not use LOWER or UPPER around our columns. By the way, SQL Server string searches are case insensitive by default, we don’t need those functions at all.
This episode of Monday Link is a bit diverse. From getting better at programming to why it’s harder for juniors to get hired. And, a rant about the C# evolution.
I couldn’t avoid adding the last article to this Monday Links. I try to only share five articles. But, that’s a great story of establishing priorities and putting life, health, and work in a balance. Enjoy!
A Path to Better Programming by Robert “Uncle Bob” Martin and Allen Holub
This is a conversation between Uncle Bob and Allen Holub (I didn’t know about Allen before this conversation) about principles, isolation, mob programming, and leadership.
By the way, the military has been leading large companies for years. How do they do it? The answer mentioned in the video is to empower people to do things. But there’s a whole book on the subject.
“If it’s a good idea, the good idea tends to spread, unless there’s somebody at the management level working hard to make it not spread”
Allen Holub
Also, I didn’t know there was a “Clean Agile” book. I would like to see what Agile meant for the creators and how it differs from what we call “Agile” today. Watch full video
Why flags do not represent languages
These days I needed to translate my Unit Testing 101 workshop instructions to Spanish. And call it fate or not, I found this article about not using flags to represent languages and what to do instead. How often do you see on web pages the US flag to signal English? Read full article
I like the evolution of C# as a language. I wrote about some of the newest and coolest C# features. But, I don’t like some of the new features. Some features, quoting the article, “just add to the complexity of the language without bringing anything to the table besides saving a few lines of code or a couple of curly braces.” Some features make the language less consistent. I share the main point of this article. Read full article.
Read the source code of your dependencies
I first heard about the concept of reading code as a learning exercise from a friend. To learn how star developers code, read their code. This post takes this concept to the next level. Use only dependencies you’re able to read its source code. Read full article.
Why nobody hires junior developers and what happens next
Another consequence of the pandemic. Nobody hires juniors. One thing I’ve noticed is companies don’t have clear career plans and salary policies for developers. Someone starts in a company and after a few years, what? Close tickets forever? Work extra hours to get noticed and promoted? That’s why juniors as soon as they feel more confident, move to a better-paid place. Brain drain is expensive. Read full article
Fetch the bolt cutters
Maybe I don’t have serious eye strain or carpal tunnel syndrome (yet?). But, I can totally relate to this story. And, it’s something more frequent these days. One quote that made me wow and stand in ovation: “In a choice between a job and me, I’m always going to choose me”. Such an amazing way to finish that story. Read full article
Voilà! Another five six reads! Do you also think C# is getting too complex? Have you seen a clear career path at companies where you’ve been?
Want to receive curated links like these? Get 4 more delivered straight to your inbox every week. Don’t miss out on next week’s links—subscribe to my email list here.
In 2021, I switched from posting whenever I had an idea about anything to posting regularly every other week about some topics.
I wrote a whole series of posts about unit testing. From how to write your first unit test with MSTest to what stubs and mocks are. In fact, I wrote my first ebook Unit Testing 101 with some of the posts of the series. By the way, you can download it for free. No email asked.
These are the 5 posts I wrote in 2021 you read the most. In case you missed any of them, here they are:
TIL: How to convert 2-digit year to 4-digit year in C#. Last year, I worked with a Stripe-powered payment module in a reservation management system. And, I needed to parse card expiration dates from a 2-digit year to a 4-digit year. I almost just added 2000 to them. But I didn’t. This is what I found and did instead.