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!!!

Is there any indexes on the queried columns ?

If not try creating one and check the performance again.

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.