Having this information made the problem even stranger. I suggested that he change the ODBC objects over to be the newer OLE DB objects figuring it might be a bug with the ODBC Command or with something with how ODBC Command was retrieving records. The same behavior was demonstrated using OLE DB. This made the problem even more puzzling. My next thought was that there was some sort of locking taking place somewhere. This seemed unlikely given the simplicity of the database accesses and the lack of long running transactions. The repeatability also make this unlikely as well. Again, my team member came back to me with a negative report.
At this point I had him test it with retrieving more records. The results of this were odd as well. When he tested it with 1500 records the problem would happen at about 1000. When he used 2000 records the pause happened at about 1500. He couldn't test it with many more than 2000 records because that is the maximum number of parameters that can be put on a command object. This was a very odd pattern to be exhibiting. Why approximately 500 records from the end of the result set was a pause occurring.
It was at this time that the maximum of approximately 2000 records retrieved was realized because of the parameter limit. The reason that this issue was first discovered was that we were looking to be able to run the method in the Web Service to retrieve 5000 records efficiently. Obviously we could not have a 2000 record limit or we'd have to have multiple queries. We however were not really using parameters for their intended purposes really. One of the fatal flaws with all database connection objects I've seen in Java and in C# are that there is no way to easily push a list into an IN clause. Instead you wind up writing
After some discussion we decided that making this change made sense in the code. It was implemented building the condition dynamically and tested again. Magically the 90 seconds of delay disappeared. That meant we killed two problems at once. Our delay that caused the Web Service to time out was gone and we were no longer capped at about 2000 records. What did this mean about our problem though. It appears that there is a bug with both ODBC and OLE that if you try to have more than 500 parameters or so, they will freeze for an undetermined amount of time, for no good reason. You must therefore heavily consider what you are doing if you have 500 or more parameters. Analyze your code and you probably will find that there is some other change that can be made to eliminate that many parameters and you should no longer have that problem.