poor performance with sqlparameter

Question

I have a web service, so the handler is called multiple times concurrently all the time.

Inside I create SqlConnection and SqlCommand. I have to execute about 7 different commands. Different commands require various parameters, so I just add them once:

command.Parameters.Add(new SqlParameter("@UserID", userID));
command.Parameters.Add(new SqlParameter("@AppID", appID));
command.Parameters.Add(new SqlParameter("@SID", SIDInt));
command.Parameters.Add(new SqlParameter("@Day", timestamp.Date));
command.Parameters.Add(new SqlParameter("@TS", timestamp));

Then during execution I just change CommandText prorerty and then call ExecuteNonQuery(); or ExecuteScalar();

And I face performance issue. For example little debuggin and profiling shows, that command

command.CommandText = "SELECT LastShowTS FROM LogForAllTime WHERE UserID = @UserID";

takes about 50ms in avarage. If I change it to:

command.CommandText = "SELECT LastShowTS FROM LogForAllTime WHERE UserID = '" + userID.Replace("\'", "") + "'";

then it takes only 1ms in avarage!

I just can't get a clue where to investigate the problem.

Answer 1

That sounds like it has cached a query-plan for an atypical @UserID value (one of the early ones), and is reusing a poor plan for later queries. This isn't an issue in the second case since each has a separate plan. I suspect you just need to add:

OPTION (OPTIMIZE FOR UNKNOWN)

to the query, which will make it less keen to re-use plans blindly.

---

Alternative theory:

You might have a mismatch between the type of userID (in the C#) and the type of UserID (in the database). This could be as simple as unicode vs ANSI, or could be int vs varchar[n], etc. If in doubt, be very specific when configuring the parameter, to add it with the correct sub-type and size.

Clarification

Indeed, it looks like the problem here is the difference between a C# string (unicode) and the database which is varchar(n) (ANSI). The SqlParameter should therefore be explicitly added as such (DbType.AnsiString).