SqlDataAdapter.Fill () Timeout - Basic Sproc returns quickly

Question

I have a SqlDataAdapter that is populated with 21 rows of data (4 columns). The sproc that manages it returns in a couple of seconds to SQL Mgmt Studio, but .Fill () takes 5 minutes.

    ArrayList ret = new ArrayList();
    SqlDataAdapter da = null;
    SqlCommand cmd = null;  
        cmd = base.GetStoredProc("usp_dsp_Stuff"); //Returns immediately in MSSMS.
        cmd.CommandTimeout = 3600; // Set to 6 min - debug only
        base.AddParameter(ref cmd, "@Param1", ParameterDirection.Input, SqlDbType.BigInt, 8, 19, 0, theParam1);
        base.AddParameter(ref cmd, "@Param2", ParameterDirection.Input, SqlDbType.BigInt, 8, 19, 0, theParam2);
        base.AddParameter(ref cmd, "@Param3", ParameterDirection.Input, SqlDbType.Char, 1, 'C');
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt); //Takes 5 minutes.

Any ideas?

Thanks in advance! -Chris

Answer 1

, , 7 ! . , SQL, . 3500 1 , Fill() #. , , . , - , .NET , SQL, , , . , 100%, , .

Answer 2

. (nolock) , sproc:

FROM category_tbl c INNER JOIN dbo.categoryItem_LNK cl WITH (NOLOCK) ON c.categoryid = cl.categoryid

, SqlDataAdapter, .

,

Answer 3

, (NOLOCK) , , , , / . SQL - .

(, , ) , , SSMS, , . SQL Profiler, , .

- , , SSMS . .

Answer 4

da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 1800;

Answer 5

() , .NET , .

SQL Profiler , SQL.NET Fill().

SET,

set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off

etc...

.. , , .

Answer 6

sniffing . , , , . SQL Management Studio - SET.

: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9fd72536-f714-422a-b4c9-078e2ef365da/

. , SET ( API- ) , SSMS. , . , . . : http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

:
http://technet.microsoft.com/en-us/library/cc966425.aspx