What determines if the system is experiencing a bottleneck? Bottlenecking occurs for all sorts of reasons and is easily recognised when you see slow response times, slowdowns of the application and time-outs.
The exact cause of a network bottleneck may not be instantly recognised and it may take a bit of investigation as to the exact cause. One of the factors to investigate is whether other resources may be causing a delay when sending data over a network. If there is excessive reading and writing of database pages, as is the case when working with large data-sets, a bottleneck is often the result. If this is the case, it means SQL Server might not be getting enough disk resources for its normal operation and is waiting to read and write to disk.
There are a few things that can be done to try and rectify this situation. Start by reviewing and tuning queries to try and streamline the process. You can try resetting the 'Fetch' size hint , but be aware that some drivers ignore the hint and others may need more parameters then the hint itself.
You can try to reconfigure the network and add more physical memory. Each of these require some degree of know-how, but your system administrator should be on the ball with this.
One way we have found to completely neutralise this problem is by using result-set streaming for the transferring of large data-sets over a network. Result-set streaming creates a continual flow of data between applications rather then holding onto the data in memory before transferring it onto its destination.
To accomplish this we created our own software with full streaming architecture so we can now handle GetFeature requests that return Gigabytes or Terabytes without choking up the server. If your administrator or development team is not up to the task of developing their own architecture, you could always connect your SQL databases to a service like DB Rover to save yourself some development time, but if you can develop a server with the result-set streaming built in, it will be well worth the effort and you'll be ever glad you did!
If you would like to find out about our result-set streaming protocol, talk to us at www.customsoftwaregroup.com