Timeline for Slow remote SELECT statement due to long "client processing time", but fast locally
Current License: CC BY-SA 3.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 1, 2012 at 14:34 | comment | added | FranticRock | It is really interesting that even though DEV pings 53 MS, it only takes 11-22 seconds to run the query. Whilst, stage pings 1 MS, it takes over 1 minute to return the data. Dev is also much further away geographically. And stage is right there next to the prod box, and yet is taking much longer. | |
| May 1, 2012 at 14:19 | comment | added | FranticRock | 4. The top column "Client Execution Time" is just the local time of the machine (IE: 15:30:00) 5. The problem occurs on any machine hitting the production DB server, including on our production web server. 6. The ping delay is <1 MS from the stage box to the prod SQL box. 7. Please see above. 8. Unfortunately the old network no longer exists. | |
| May 1, 2012 at 14:19 | comment | added | FranticRock | from our dev box tracert Prod_IP_Address 1 53 ms 52 ms 53 ms SQL2008 From the stage machine, the time is consistently over 1 minute. tracert Prod_IP_Address tracert: 1 1 ms <1 ms <1 ms SQL2008 From production web server: execution time is 53 seconds. tracert: 1 1 ms <1 ms <1 ms SQL2008 | |
| May 1, 2012 at 14:17 | comment | added | FranticRock | 2. The consumption time was close, if not the same as that of the same query ran locally from the PROD box. (I.E. 2 seconds) 3. That's right 1 min 8 seconds is the execution time. This time varies among different client machines. From our development machine (located much further away than the stage machine), I ran this query 8 times in a row, and the time ranged from 11 seconds to 22 seconds. (average 18 sec. ) | |
| May 1, 2012 at 14:17 | comment | added | FranticRock | Thanks a lot for the info. My responses below. 1. Correct. Client tools also installed on prod, and the same query i mentioned takes 2 seconds to return all of the 30,000 records (totalling 4 MB size). By the way, the query I used is just an example. It is not a real business query. It's just a means to get 4 MB of data from a table. We currently have a performance problem reading several megabytes of data from any table with any query currently. | |
| May 1, 2012 at 3:35 | history | answered | alexei | CC BY-SA 3.0 |