Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding [duplicate]

Question

When I run my code I get the following exception:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

My code is the following:

    private void FillInDataGrid(string SQLstring)
    {
        string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString; //hier wordt de databasestring opgehaald
        SqlConnection myConnection = new SqlConnection(cn);
        SqlDataAdapter dataadapter = new SqlDataAdapter(SQLstring, myConnection);
        DataSet ds = new DataSet();
        myConnection.Open();
        dataadapter.Fill(ds, "Authors_table");
        myConnection.Close();
        dataGridView1.DataSource = ds;
        dataGridView1.DataMember = "Authors_table";
    }

And my SQLstring is the following:

SELECT dbo.[new].[colom1],dbo.[new].[colom2],dbo.[new].[colom3],dbo.[new].[colom4],  
                dbo.[new].[Value] as 'nieuwe Value',
                dbo.[old].[Value] as 'oude Value'
                FROM dbo.[new]
                JOIN dbo.[old] ON dbo.[new].[colom1] = dbo.[old].[colom1] and dbo.[new].[colom2] = dbo.[old].[colom2] and dbo.[new].[colom3] = dbo.[old].[colom3] and dbo.[new].[colom4] = dbo.[old].[colom4] 
                where dbo.[new].[Value] <> dbo.[old].[Value]

Capo

your query seems to be taking time executing – Shipwreck

@EhsanSajjad Do you think the time executing is the problem here? – Capo

@EhsanSajjad I only got a table of around 7000rows – Capo

connection string is wrong then – Shipwreck

@EhsanSajjad I am connecting to that same string earlier, and that works – Capo

Clearly there is a timeout during execution. May be the number of rows, the joins, the conditions you have in the joins. See this #8602895 – Dextran

@EhsanSajjad and bit You guys were indeed right, I made the table even smaller, and now it executed – Capo

Is there a specific requirement where you're needing to check equality between every single column of both tables in your JOIN section? – Salomie

@EhsanSajjad I think the question is not duplicate. One is for sql Command And another is for SqlDataAdapter . As both are from System.Data.SqlClient namespace so the error message is same. But solution way is different. – Tergum

@MuhammadAshikuzzaman setting the query timeout is same in either case – Shipwreck

@EhsanSajjad one is SqlDataAdapter da = new SqlDataAdapter(str, dbCon); da.SelectCommand.CommandTimeout = 300; and another is SqlCommand command = new SqlCommand(); command.CommandTimeout = 300; – Tergum

Answer 1

If your query needs more than the default 30 seconds, you might want to set the CommandTimeout higher. To do that you'll change it after you instantiated the DataAdapter on the SelectCommand property of that instance, like so:

private void FillInDataGrid(string SQLstring)
{
    string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString; //hier wordt de databasestring opgehaald
    DataSet ds = new DataSet();
    // dispose objects that implement IDisposable
    using(SqlConnection myConnection = new SqlConnection(cn))
    {
        SqlDataAdapter dataadapter = new SqlDataAdapter(SQLstring, myConnection);

        // set the CommandTimeout
        dataadapter.SelectCommand.CommandTimeout = 60;  // seconds

        myConnection.Open();
        dataadapter.Fill(ds, "Authors_table"); 
    }
    dataGridView1.DataSource = ds;
    dataGridView1.DataMember = "Authors_table";
}

The other option is to address your query. In Sql Server you can analyze the execution plan. I bet there is a full-table scan in it. You might experiment with adding an index on one or two columns in your [old] and [new] table. Keep in mind that adding indexes comes at the cost of higher execution times for inserts and updates and space requirements.

Aguish

I tried 60 seconds, but it still didnt load, im going to try 15minutes now, is there any way to let the process speed up? besides changing my cpu. – Capo

As I said, your query is the problem. You might try adding indexes. And I would simply run that statement in Sql Server management studio and optimize there based on the outcome of the execution plan. – Aguish

After an hour it still wasnt done :P I am now executing the query in de SQL server management studio which already takes 5mins haha – Capo