Click here to Skip to main content
15,742,973 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone. Here's the scenario I wish to inquire about : I have at least 10 remote test machines with their own SQL Server installed. What I am doing is that I send at least 5 SELECT statements to pull data from those machines concurrently (on a multi-threaded approach) on 5 non-related tables. Some of those machines, depending on user usage, contains at least more than 2000 records, that what I needed to extract using the aforementioned SELECT query. I created a thread for each remote connection so that they will run concurrently and dispose with the problem of waiting for one select query to return a record before moving to the next queued connection. It is working fine as of my last test run. My question is, if I pushed it as far as 100 machines, will these have any adverse effect on the integrity of records being returned by each connection if I am going to run the SELECT statement concurrently? Your thoughts and opinions regarding this matter would be much appreciated as it will help me improve more the software I am developing as of the moment. May thanks and here's looking forward to your feedbacks :)

What I have tried:

Tried as much a 20 machines and set the SQL connection timeout by at least 3 minutes for each connection. It worked but I noticed a rather significant spike in my CPU and network usage. While it is executing. And some of the connections get dropped / timed out even if pinging the machine indicates that it is online.
NightWizzard 6-Jan-17 12:44pm    
Think there may be some bottlenecks. First of all: did you take in account that all of your concurrent requests are using the same network/internet connection that have to transport all the incoming answers? There may be package collisions at Ethernet protcol level which leads to package lost and therfore to timeouts. Second: internet connections and answer times depend on several conditions (how busy is the server you're sending a request to, how stable is the current connection route that will include some other servers that may be busy and how fast is your own current internet connection). This all may lead into a timeout for any single server connection you established. The Ping uses a different protocol layer and may return immediately while the SQL request will take more time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900