Member Avatar for rregalad

I am working on an ASP.net 2.0 application written in C#, pulling data from an Oracle 10g database on a remote server (i.e. the application and database are on different servers).

The data is pulled via a SqlDataSource control. When I run small, quick queries, everything works fine, but when I try to run a query that returns 3000-4000 records, I get the error message in the subject line. The query times out after about 15 seconds. I have taking the exact query that my application is trying to run, and ran it directly on the database server. It takes a few seconds, but it works. I have reached the conclusion that it must be a timeout issue.

I have pored through many forums, and based on my findings, tried 4 fixes:

1) Going into the control panel, then administrative tools, then ODBC sources. I selected the appropriate DSN, and unchecked the "Enable Query Timeout" option.

2) Added the connection lifetime and connection timeout options to the connection string as follows:

sql.ConnectionString = "DSN=******;Connection Lifetime=0;Connection Timeout=0;UID=****;PWD=****; DRIVER={Microsoft ODBC for Oracle}; SERVER=***.***.**.*;";

3) I have added the line "SQLNET.EXPIRE_TIME = 0" to every one of the three sqlnet.ora files on the database server.

4) An old coworker of mine used "Server.ScriptTimeout = 600" in his code. But, these were plain old ASP pages.

None of these things worked. Please help!!!

  • 2 Contributors
  • 2 Replies
  • 136 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by rregalad

Dani AI

Generated

Timeouts usually live in several places: the ADO.NET provider command, the web host/request, the driver, or the network. ’s observation that the query runs quickly on the DB host but times out from the app points to an application-side timeout (not the Oracle server itself). was on the right track about performance, but a common practical fix is to raise the ADO.NET command timeout that the SqlDataSource uses before the statement is executed.

You can access the underlying command in the SqlDataSource Selecting event and set its timeout. Example (code-behind):

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    var cmd = e.Command as System.Data.Common.DbCommand;
    if (cmd != null)
        cmd.CommandTimeout = 120; // seconds (adjust reasonably)
}

The Selecting event gives you the DbCommand that will run; changing CommandTimeout affects only that execution. See the control/event docs: SqlDataSourceSelectingEventArgs and the command timeout property: DbCommand.CommandTimeout.

Other practical notes: prefer a native Oracle .NET provider (ODP.NET) for consistent behavior and performance over generic drivers; see Oracle’s ODP.NET resources at Oracle Data Provider for .NET. Avoid setting “no timeout” globally — on a busy web server that can exhaust threads. For large result sets, prefer server-side paging, streaming with a DataReader, or an async/background export so requests don’t block. Quick troubleshooting: try the same query from the application server using the same client library to isolate network vs provider latency, enable provider/client tracing, and temporarily raise the provider command timeout to confirm which layer is aborting.

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

Is there any indexes on the queried columns ?

If not try creating one and check the performance again.

Member Avatar for rregalad

Thanks, but even if I made an index, I may eventually run into a query that takes too long and times out. I ended up just splitting large queries into smaller subqueries.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.