0

I have a SqlDataAdapter that is being populated with 33k rows of data (about 15 columns). When running query in MS SSMS, i get my rows in about 5 sec. When using my code, I get a timeout, even by setting the timeout parameter to 120 sec :

    private static DataTable getResult(string query, int someId, SqlConnection myConn)
    {
        SqlCommand myCmd = new SqlCommand(query, myConn);
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable dt = new DataTable();

        myCmd.Parameters.Add("@someId", SqlDbType.Int);
        myCmd.Parameters["@someId"].Value = someId;
        da.SelectCommand = myCmd;
        da.SelectCommand.CommandTimeout = 120;
        da.Fill(dt);

        return dt;
    }

I've already tried advices from websites such as getting higher connection numbers, and timeout values with no result to this day ...

Any ideas on how to avoid the timeout error ?

Thanks for your insights.

Wingi
  • 21
  • 4
  • Are you sure that you are running the same query in SSMS as in getResult method? Also is your connection string login same? Did you try to just execute the command to see the results, before trying to put them into DataTable? – xszaboj Nov 16 '16 at 10:38
  • Try running your query in a new MS SSMS window. If you have run the query multiple times in the same window then it will take the same query plan and execute faster. Check how much time it takes in the new query window. – Niar Nov 16 '16 at 10:38
  • Query and connection string are strictly the same. Time laps are the same wherever/whenever I run query in MS SSMS. – Wingi Nov 16 '16 at 13:12

1 Answers1

0

Ok, so I was finally able to get rid of time out errors by adding indexes on my tables, as stated in this page [MSDN].

Seems like time laps differs from MS SSMS to .net code ...

Wingi
  • 21
  • 4