CODEWITHSUNDEEP
asp.netsql-server
Joshua Son
Joshua Son

Reputation: 1897

Performance of SqlParameter in C#

I have a stored procedure like this:

create proc wsp_test
(
    @age           int
    , @userName    varchar(30)
)
as
    select    userID, userName
    from      Users
    where     userName  like '%' + @userName + '%'
    and       age > @age

And here is C# code:

case 1:

SqlCommand _cmd = new SqlCommand();
_cmd.Parameters.Add("age", SqlDbType.Int, 4);
_cmd.Parameters.Add("UserName", SqlDbType.VarChar, 30);

case 2:

SqlCommand _cmd = new SqlCommand();
_cmd.Parameters.Add("age", SqlDbType.Int);
_cmd.Parameters.Add("UserName", SqlDbType.VarChar);

case 3:

SqlCommand _cmd = new SqlCommand();
_cmd.Parameters.Add("age");
_cmd.Parameters.Add("UserName");

case 4:

SqlCommand _cmd = new SqlCommand();
_cmd.CommandText = "EXEC wsp_test 20, 'John'";

Now my question is that what has it got to do with performance to specify the parameter's datatype or length?

Which one has the best performance? Are there any documents from Microsoft's web site that I can rely on?

Or are there any security reasons to specify the parameter's datatype and length?

Upvotes: 0

Views: 419

Answers (2)

marc_s
marc_s

Reputation: 754518

For clarity's sake, to clearly and unmistakenly convey your intent, I would use

case 5:

SqlCommand _cmd = new SqlCommand();
_cmd.Parameters.Add("age", SqlDbType.Int);
_cmd.Parameters.Add("UserName", SqlDbType.VarChar, 30);

For the SqlDbType.Int, there's no need and no point in specifying a size - it's always 4 bytes and cannot change, cannot be adjusted.

For the SqlDbType.VarChar, I would always specify the length, just to be clear what it is you want to have in this parameter. It's probably not absolutely needed - but I like to be very explicit about those kinds of things.

From a performance point of view, I guess cases 1-3 are going to be equivalent (give or take), while case #4 seems a bit fishy to me and I don't know and can't really tell whether or not doing it this way would have a (negative) performance impact; I would just never do it this way to begin with (it's unclear, it's not as obvious what you're doing, you're not using parameters to pass in your values - just messy!)

Upvotes: 0

Samuel Neff
Samuel Neff

Reputation: 74909

The performance of the datatype is completely irrelevant compared to this:

where     userName  like '%' + @userName + '%'

This particular part of the query is going to take exponentially more time than dealing with parameters.

I would recommend don't care about performance at this stage. It's too early. Certainly not the micro-benchmark of whether or not to specify the type and length of parameters. Look at other factors. yes, you should specify the type, it makes the code more maintainable and can make runtime casting issues easier to identify and solve.

If you later determine that this particular code is a bottleneck for your application, then look into using a full text for the username field. You can also look at statistics for this code's usage and identify if you really need it. Do the majority of queries match the username exactly or at least starting with? Would it be good for your use case to search first on exact matches, which is significantly faster, and only search for partial matches of no exact matches are found?

Upvotes: 1

Related Questions