Sql Timeout error, but why?

Question

The following code results in a System.Data.SqlClient.SqlException: Timeout expired.

const string sqlStmt = @"SELECT * 
                         FROM CUSTOMER_INFO 
                         WHERE CUSTOMER_NO = @CUSTOMER_NO;";

SqlCommand command = new SqlCommand(sqlStmt, connection);
command.Parameters.AddWithValue("@CUSTOMER_NO", txtAccountNo.Text.Trim().ToUpper());

but this does not time out...

const string sqlStmt = @"SELECT * 
                         FROM CUSTOMER_INFO 
                         WHERE CUSTOMER_NO = @CUSTOMER_NO;";

SqlCommand command = new SqlCommand(sqlStmt, connection);            
command.Parameters.Add("@CUSTOMER_NO", SqlDbType.VarChar, 25).Value = txtAccountNo.Text.Trim().ToUpper();

I don't understand why, can anyone enlighten me?

Take a look at this post, this may help you: http://stackoverflow.com/questions/345323/addwithvalue-without-dbtype-causing-queries-to-run-slowly — Christian Phillips, Jan 10 '13 at 12:42
@Prasanth - yes i did, but i'm learning c# and just want to understand why the first one doesn't work? — Stuart, Jan 10 '13 at 12:43
can you use sql server profiler to see the differences between the sql queries ? — tschmit007, Jan 10 '13 at 12:46
I don't know how to use sql server profiler, so i guess the answer is no lol, but i expect it's possible! the post above makes sense though! — Stuart, Jan 10 '13 at 12:48
I try you code: in the first case the type is derived from the value of the parameter, that is by the application with no contribution of sql server. That is th type of the column and the type of the parameter does not match. May be a type conversion can explain the difference of time. If the table is very polpulated, it is in cache for the second query. That also can explain a difference of performance. — tschmit007, Jan 10 '13 at 13:04
What is the type of CUSTOMER_NO field in your database ?. I assume that it is of text type — Murtuza Kabul, Jan 10 '13 at 13:18

score 0 · Answer 1 · answered Jan 10 '13 at 12:51

Can you get a look at the SQL statement that gets executed by the database ?

You'll probably see a difference in the type that is used by the parameter. I believe that the AddParamWithValue method will not use the correct type for the parameter.

Then, it is possible that the DBMS will have to convert the value back to the correct type. Some DBMS'es will not be able to use an index-lookup in that case, which will result in a longer running query, hence the timeout.

Sql Timeout error, but why?

1 Answers1