What happens if you make some difficult stored procedure (SP) and call it by ADO for example in connected mode?? All code is just pseudo….
- We have this code:
…
Command cmd = new SqlCommand(“LongCall”,con) ;
cmd.CommandType = CmdType.StroredProcedure;
cmd.ExecuteNonQuery();
…
- We have this procedure
CREATE PROCEDURE LongCall AS
…….Inside of LongCall SP can be cascade calling of selects
SELECT * FROM ( SELECT * ……( SELECT *) ) basically something with huge complexity
What happened?? ExecuteNonQuery closes connection. Implicitly after 30 seconds. So what you have to do?
- You can set TimeOut
cmd.TimeOut = 100; //sets timeout to 100 seconds
- You can disable Timeout
cmd.Timeout = 0; //calling of cmd.ExecuteNonQuery finish however LongCall ends.
- You should call asynchronous ExecuteNonQuery
IAsyncResult result = cmd.BeginExecuteReader(CommandBehavior.CloseConnection)
while(!result.IsCompleted)
{
….
}
cmd.EndExecuteReader(); - Take a thing on your Stored Procedure LongCall. Can you degrease complexity??
What about make a cache!! Use temporary tables as much as you can. Replace Views by selects etc…
