StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTimeout for OracleDataReader.Read Method
text
Body
copied!<p>The ODP.NET OracleCommand class has a CommandTimeout property that can be used to enforce a timeout for the execution of a command. This property seems to work in situations where the CommandText is a SQL statement. The example code is used to illustrate this property in action. In the initial version of the code, the CommandTimeout is set to zero - telling ODP.NET not to enforce a timeout.</p> <pre><code>using System; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.Linq; using System.Text; using Oracle.DataAccess.Client; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;")) using (OracleCommand cmd = new OracleCommand()) { con.Open(); cmd.Connection = con; Console.WriteLine("Executing Query..."); try { cmd.CommandTimeout = 0; // Data set SQL: cmd.CommandText = "<some long running SQL statement>"; cmd.CommandType = System.Data.CommandType.Text; Stopwatch watch1 = Stopwatch.StartNew(); OracleDataReader reader = cmd.ExecuteReader(); watch1.Stop(); Console.WriteLine("Query complete. Execution time: {0} ms", watch1.ElapsedMilliseconds); int counter = 0; Stopwatch watch2 = Stopwatch.StartNew(); if (reader.Read()) counter++; watch2.Stop(); Console.WriteLine("First record read: {0} ms", watch2.ElapsedMilliseconds); Stopwatch watch3 = Stopwatch.StartNew(); while (reader.Read()) { counter++; } watch3.Stop(); Console.WriteLine("Records 2..n read: {0} ms", watch3.ElapsedMilliseconds); Console.WriteLine("Records read: {0}", counter); } catch (OracleException ex) { Console.WriteLine("Exception was thrown: {0}", ex.Message); } Console.WriteLine("Press any key to continue..."); Console.Read(); } } } } </code></pre> <p>Example output for the above code is shown below:</p> <pre><code>Executing Query... Query complete. Execution time: 8372 ms First record read: 3 ms Records 2..n read: 1222 ms Records read: 20564 Press any key to continue... </code></pre> <p>If I change the CommandTimeout to something like 3...</p> <pre><code>cmd.CommandTimeout = 3; </code></pre> <p>...then running the same code produces the following output:</p> <pre><code>Executing Query... Exception was thrown: ORA-01013: user requested cancel of current operation Press any key to continue... </code></pre> <p>Calling a stored procedure that returns a ref cursor is another matter though. Consider the test proc below (purely for test purposes):</p> <pre><code>PROCEDURE PROC_A(i_sql VARCHAR2, o_cur1 OUT SYS_REFCURSOR) is begin open o_cur1 for i_sql; END PROC_A; </code></pre> <p>The example code below can be used to call the stored proc. Note that it sets the CommandTimeout to a value of 3.</p> <pre><code>using System; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.Linq; using System.Text; using Oracle.DataAccess.Client; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;")) using (OracleCommand cmd = new OracleCommand()) { con.Open(); cmd.Connection = con; Console.WriteLine("Executing Query..."); try { cmd.CommandTimeout = 3; string sql = "<some long running sql>"; cmd.CommandText = "PROC_A"; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new OracleParameter("i_sql", OracleDbType.Varchar2) { Direction = ParameterDirection.Input, Value = sql }); cmd.Parameters.Add(new OracleParameter("o_cur1", OracleDbType.RefCursor) { Direction = ParameterDirection.Output }); Stopwatch watch1 = Stopwatch.StartNew(); OracleDataReader reader = cmd.ExecuteReader(); watch1.Stop(); Console.WriteLine("Query complete. Execution time: {0} ms", watch1.ElapsedMilliseconds); int counter = 0; Stopwatch watch2 = Stopwatch.StartNew(); if (reader.Read()) counter++; watch2.Stop(); Console.WriteLine("First record read: {0} ms", watch2.ElapsedMilliseconds); Stopwatch watch3 = Stopwatch.StartNew(); while (reader.Read()) { counter++; } watch3.Stop(); Console.WriteLine("Records 2..n read: {0} ms", watch3.ElapsedMilliseconds); Console.WriteLine("Records read: {0}", counter); } catch (OracleException ex) { Console.WriteLine("Exception was thrown: {0}", ex.Message); } Console.WriteLine("Press any key to continue..."); Console.Read(); } } } } </code></pre> <p>Example output from the code above is shown below:</p> <pre><code>Executing Query... Query complete. Execution time: 34 ms First record read: 8521 ms Records 2..n read: 1014 ms Records read: 20564 Press any key to continue... </code></pre> <p>Note that the execution time is very quick (34 ms) and that a timeout exception was not thrown. The performance that we see here is because the SQL statement for a ref cursor is not executed until the first call to the OracleDataReader.Read method. When the first Read() call is made to read the first record from the refcursor, then the performance hit from the long running query is incurred. </p> <p>The behavior that I've illustrated means that the OracleCommand.CommandTimeout property can't be used to cancel a long running query associated with a ref cursor. I'm not aware of any property in ODP.NET that can be used to limit the execution time of a ref cursor SQL in this situation. Anyone have any suggestions on how the execution of a long running ref cursor SQL statement could be short circuited after a certain amount of time?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload