Linq to SQL and Codepages in generated SQL

We have a poorly-performing query in L2S. The actual query looks something like this:

SELECT *
FROM Table
WHERE 
    ([ColA] IN (@p0) AND ColB = @p1) 
    OR ColB IN (@p2, @p3, @p4, @p5)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [220]
-- @p1: Input VarChar (Size = 14; Prec = 0; Scale = 0) [CountryDefault]
-- @p2: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- @p3: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- @p4: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [WF1 1XU]
-- @p5: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [WF1]
-- Context: ProfiledSqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

One problem is a merge-sort as a result of the OR, but we're looking into that.

The other problem is more interesting. One of our DBAs pointed out there's quite a performance hit in the codepage conversion between the NVarChar parameters and the column they're being compare with VarChar.

The weird thing is how L2S chose for some of those parameters to be NVarChar, and the p1 parameter to be VarChar. In the code they are all strings, although p1 is a string literal while the others are variables. Notice they're all being compared to the same column (ColB) so it's nothing to do with the column datatype.

How does L2s decide what datatype to use when generating the query, based on the values passed-in? If possible, how do I control that?

Answers


There was a workaround given in this thread which effectively converts all the parameters to the correct types.


Need Your Help

Reading from serial port in C

serial-port arduino xbee

I'm trying to read from Arduino (who's sending char '4' constantly) with XBee.

Find maximum value between specified date range

excel datetime excel-formula max excel-indirect

I have a range of daily dates in column G and a range of stock prices in column H. I would like to find a rolling 52 week high, i.e. the highest stock price in column H between the current date and...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.