I have a situation where it is slow from the application and ok from the SSMS interface. From the application, the SQLClient data provider parameters are defined as follow:

            Dim loDataParameterSQLClient As SqlParameter = Nothing

            loDataParameterSQLClient = New SqlParameter()
            loDataParameterSQLClient.ParameterName = "MyField1"
            loDataParameterSQLClient.Value = 1
            loDataParameterSQLClient.SqlDbType = SqlDbType.Int
            loDataParameterSQLClient.Size = 4

            loDataParameterSQLClient = New SqlParameter()
            loDataParameterSQLClient.ParameterName = "MyField2"
            loDataParameterSQLClient.Value = '12/30/1899 12:00:00 AM'
            loDataParameterSQLClient.SqlDbType = SqlDbType.DateTime
            loDataParameterSQLClient.Size = 8

            loDataParameterSQLClient = New SqlParameter()
            loDataParameterSQLClient.ParameterName = "MyField3"
            loDataParameterSQLClient.Value = '        '
            loDataParameterSQLClient.SqlDbType = SqlDbType.VarChar
            loDataParameterSQLClient.Size = 8

            loDataParameterSQLClient = New SqlParameter()
            loDataParameterSQLClient.ParameterName = "MyField4"
            loDataParameterSQLClient.Value = '3/16/2008 9:26:07 PM'
            loDataParameterSQLClient.SqlDbType = SqlDbType.DateTime
            loDataParameterSQLClient.Size = 8

and, from SSMS:

DECLARE @MyField1 Integer
DECLARE @MyField2 DateTime
DECLARE @MyField3 Varchar(8)
DECLARE @MyField4 DateTime

SET @MyField1=1
SET @MyField2='12/30/1899 12:00:00 AM'
SET @MyField3='        '
SET @MyField4='3/16/2008 9:26:07 PM'

When I execute it from the application, I change the MyField2 value with a date value ranging from one month to 6 months old. The more I go back in time, the longer it takes for the query to be executed. If I go back in time such as 2 weeks, I get the result in 0.15 second. If I go back in time such as 3 weeks, I get the result in 0.30 seconds. Then, suddenly, if I go back several weeks, I end up with results such as 15 to 45 seconds. However, on each of those queries, no record are returned.

When I execute it from the SSMS interface, using the DECLARE approach such as shown in code sample #2, I always have a result within 1 to 1.5 seconds.

So, obviously, something is not defined properly in the application in the way I define my parameters. Anyone would know what is causing this?