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.
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.
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
).