CommandTimeout - Query doesn't abort if in responding state

Connectivity
Enthusiast

CommandTimeout - Query doesn't abort if in responding state

Hi,

In my .Net application in which i connect to TD to run queries, i have given a CommandTimeout of 5 minutes. Now, when i submit a query that runs a large select statement, it runs for 2 minutes(active state)and then goes into responding state. It runs in this state for 10-15 minutes.

As i understand, CommandTimeout is only applicable when the query is in "Active" state. In my case, it is no more in active state and it keeps on running, but i want it not to run for so long. Is there a way i can abort a query kicked off from my application irrespective of which state it is in. I dont want any query to run on the platform after say 5 min that kicked off from my application. I want to handle is in my program and not through viewpoint/TASM.

Thanks !

Samir

Tags (2)
5 REPLIES
Teradata Employee

Re: CommandTimeout - Query doesn't abort if in responding state

Your application must process the result (DataReader) and it must close/dispose the DataReader to release the spool. I think the Select statement completed and DBS started to send back data to the application hence the responding state. What happens after 10-15 minutes in responding state? Are you stating that the TdCommand.ExecuteReader method blocks and it does not return control back to app for 15 to 20 minutes when the Command-timeout is set to 5 minutes? 

The Command Timeout cancels/aborts a long running query. The Command Timeout will not release the spool by automatically disposing the DataReader. The Data Provider will send a abort message to the Teradata Database after the spcified timeout. It then waits for the Teradata Database to abort the Command and return an error. In some cases, the Command might succeed before the Teradata Database can process the Abort/Cancel request; in this case the Teradata Database will return the result of the Command back to the application. 

Enthusiast

Re: CommandTimeout - Query doesn't abort if in responding state

Hi,

  i am developing an application for CPU scoring. In this, a user can run a query and after is it successfully executed, i use the session id and queryband to get CPU scores like total cpu, impact cpu, i/o used etc for the query. I dont display the o/p of select statement to the user as it is not really required.

  My application works fine. Now, I have a select query that returns bollions of rows. When i run the query, it runs for 2 min (active state) and then it goes into respoindng state as it is fetching the rows now. Since, i have a connection timeout of 10 minutes, this query doesn't time out as it is active state for just 2 minutes and no longer requires CPU.

  For the period of time, the query is in responding state, front-end of my tool is in freeze state. I want to handle such situations. I want that if the query goes into responding state for some period of time, it should timeout. I tested by selecting only top 1 million rows form the same query, it went to responding state and then completed.

  Is it possible to give a warning like sql assist that more than 2000 rows are being selected and stop processing ? I believe the CPU parameters won't change if you limit the number of rows to be selected.

 My query is: sel * from mytable where VIEW_DT between '2015-09-01' and '2015-10-19';

 My code is :

using (TdCommand cmd = tdCon.CreateCommand())

{

    cmd.CommandText = "SET QUERY_BAND ='query=" + my_query_band.Text.Trim() + ";' FOR SESSION;";

    cmd.ExecuteNonQuery();

    cmd.CommandTimeout = 1000;

    cmd.CommandText = query_to_run.Text.Trim();

    cmd.ExecuteNonQuery();

    o_query = query_to_run.Text.Trim();

    MessageBox.Show("Original Query ran successfully!");

    origransuccess = true;

    cmd.CommandText = "SET QUERY_BAND = NONE FOR SESSION;";

    cmd.ExecuteNonQuery();

}

  Hope i am clear now.

  --Samir Singh

Enthusiast

Re: CommandTimeout - Query doesn't abort if in responding state

Hi Cal,

 In fact i noticed one thing. When my query is running, i.e in active queue, i can close my application using the cross button on the form right corner (i dont have a separate button to abort the query as we have in sql assist). But, when my query goes to responding state, the form is totally unresponsive and i cant click on the cross botton. I need to do alt+cntl+del to abort the application. However, this doesn't abort the session in responding state. It goes on untill it is aborted from viewpoint.

I want to read row by row when query goes into responding state and stop if row count is 2000. i tried the TdDataAdapter method also. When tdDA.Fill(dsComp, "T_Database"); is executed, it runs the query but the control comes back only after it is completed fully(after responding phase is over). Is there a way to read rows when it is still in responding state ? Like in sql assist, it askes to stop intermittently bases on rows we set (2000 default) and if we stop, the query is out of responding state.

 --Samir Singh

Teradata Employee

Re: CommandTimeout - Query doesn't abort if in responding state

You are using ExecuteNonQuery. This method must process the entire result set. You need to use ExecuteReader instead and immediately dispose the DataReader:

using (TdCommand cmd = tdCon.CreateCommand())
{
cmd.CommandText = "SET QUERY_BAND ='query=" + my_query_band.Text.Trim() + ";' FOR SESSION;";
cmd.ExecuteNonQuery();

cmd.CommandTimeout = 1000;
cmd.CommandText = query_to_run.Text.Trim();
cmd.ExecuteReader().Dispose();

o_query = query_to_run.Text.Trim();
MessageBox.Show("Original Query ran successfully!");
origransuccess = true;

cmd.CommandText = "SET QUERY_BAND = NONE FOR SESSION;";
cmd.ExecuteNonQuery();
}
Teradata Employee

Re: CommandTimeout - Query doesn't abort if in responding state

You must execute the Query with TdCommand.ExecuteReader() method and must use the TdDataReader to process the Result-set (read one row at a time).

TdDataReader.RecordsReturned or TdDataReader.RecordsReturned64 get teh number of rows in the result-set.