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.