poor performance with sqlparameter poor performance with sqlparameter sql-server sql-server

poor performance with sqlparameter


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


You're sending seven times more data to the server, so it will be slower.

Also, if your userID strings have different lengths, setting an explicit length in the SQL parameter will allow it to reuse the query better.