You have correctly identified the declaration of the parameters, specifically the data type, as the problem. My VS statement, “.addwithValue(“@LotNo”,”000000113855”) which is implicitly converted to @LotNo=N' 000000101058' is the source of the slow down. The conversion of the two dates does not seem to affect performance.

I found two methods to correct the problem. The first is to just put the string into the query programmatically, “Where Ser_lot_no = ‘ 000000113855’ …”.

The second method is to declare the dbType of the parameter. This a bit tricky in this case. The database (from a third party vendor. Not one I created) defines Ser_lot_No as a Char(15). I encountered a problem here in that the SQLParameter dbTypes do not include the char data type. The article at http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx suggests that the Char type does exist but it was not available to me in the intelisense and manually typing it in resulted in a error.

After some trial an error I found AnsiStringFixedLength to work in this instance.

My original code:

.addwithvalue(“@LotNo”, “ 000000113855”)

My revised code

Dim p as new system.data.SQLParameter

p.Name = “@LotNo”

P.SQLDbType = SQLDbType.AnsiStringFixedLength

P.Value = “ 000000113855”

MacCmd.Parameters.Add(p)

The new code is much longer and I will decide whether or not to use it on a case by case basis. (I have been using the old method for several years. Writing thousands of parameterized queries against a Char data type. This is the first instance of a detectable problem. )

Thanks everyone for your assistance.

PS not sure how to mark multiple post as partial solutions. Sorry.