Unanswered: command timeout using C# with ODBC.NET
I am writing a program which is designed to query the database (SQL Server 2000) and return a dataset. The problem is that, as the database grows in size, the likelihood of a timeout occurs.
For example, yesterday, there were over 1 million records in a particular table. The Query returned the expected records, but the dataset returned was accompanied by a timeout exception. Today, there are over 2 million records in the database, and the query runs for the same amount of time and returns with a timeout, this time not returning any data.
I have tried changing the connection timeout (dbConnect.ConnectionTimeout = 30, 60,...) and the command timeout (dbCommand.CommandTimeout = 60,90,120,300,600,3000,4800,48000) and it still runs for the same amount of time and times out. What else can I do?
I have also noticed, on a somewhat related note, that reports written in Excel also timeout when run against the data.