Who needs values these days?

 

Whilst doing some SQL optimization on something else in my system I spotted something odd whizz up SQL Profiler. It was a simple query to get the last 8 U2U’s for a user. However, some queries were doing 75,000 reads and taking near enough a second. What the hell ?

I copied and pasted the query and ran it as straight TSQL (converting it from the parameterised query). Odd…. only 130 reads?!

Running the original parameterized version produced the high read count and a complex query plan, and this was repeatable time and time again. So why was using a query window so much faster that using code???

Stupid code, thats why. I was creating the query like this using AddWithValue

    Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, con)
        cmd.Parameters.AddWithValue("@LoggedInUser", PearlUserManagement.GetInfoAboutCurrentUser(PearlConstants.UserInfoItems.UserCode))

But the resultant SQL call contained this:

‘@LoggedInUser nvarchar(9)’,@LoggedInUser=N’ABCDEFGHI’

When the code was passing the usercode (9 character alphanumeric) it was passing it as an nvarchar rather than just a char. What if we change the parameterized version from:

 

exec sp_executesql N'SELECT top 8 UserMessages.*, PearlUsers..Users.UserName as UserFromName, PearlUsers..Users.FullName as UserFromNameFull FROM UserMessages LEFT JOIN PearlUsers..Users ON 
UserMessages.UserFrom=PearlUsers..Users.UserCode Where MessageType = 0 and UserTo = @LoggedInUser order by datesent desc',N'@LoggedInUser nvarchar(9)',@LoggedInUser=N'ABCDEFGHI'

To:

exec sp_executesql N'SELECT top 8 UserMessages.*, PearlUsers..Users.UserName as UserFromName, PearlUsers..Users.FullName as UserFromNameFull FROM UserMessages LEFT JOIN PearlUsers..Users ON 
UserMessages.UserFrom=PearlUsers..Users.UserCode Where MessageType = 0 and UserTo = @LoggedInUser order by datesent desc',N'@LoggedInUser char(9)',@LoggedInUser='ABCDEFGHI'

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Original query used 13436 reads and took 703ms.

Version using ‘char’ used 73 reads and took 171ms!!

Looking at the query plans for both you can see vastly different patterns:

 

 

The first query was ignoring the index as the index was built on a char and not an nvarchar!!

So quick change of the code from:

    Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, con)
        cmd.Parameters.AddWithValue("@LoggedInUser", PearlUserManagement.GetInfoAboutCurrentUser(PearlConstants.UserInfoItems.UserCode))

To

  Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, con)
        cmd.Parameters.Add("@LoggedInUser", Data.SqlDbType.Char).Value = PearlUserManagement.GetInfoAboutCurrentUser(PearlConstants.UserInfoItems.UserCode)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

and we’ve reduced disk reads by a factor of 180, and dropped the query time to just a quarter.

So, AddWithValue is great for not having to think about things, but it can cause some REALLY REALLY poor query plans in SQL!!