0

I have some ODBC SQL queries which, when run from the local environment where the database is hosted, take 4 seconds. But when they're run from a remote, hosted server, those same exact queries take twice as long.

I've tested the connection speed and am getting about 2 Mb/s between the two machines, so that doesn't seem to be the problem.

Any ideas on where to look or how to improve the execution of those remote queries?

Jon
  • 846
  • 1
  • 8
  • 25
  • Addendum: Discovered the "fetch array size" setting in ODBC settings. Increased that from 50 to 5000 and saw a ~30% improvement in speed. – Jon Apr 19 '12 at 13:56

1 Answers1

0

Are you dealing with large result sets or querieng (B)LOB fields? All the result data must be transferred over the network, this may take some time. If so, you may consider using paging / batching techniques.

Community
  • 1
  • 1
bubblez
  • 814
  • 1
  • 8
  • 14
  • That's a really good point. We are transferring around 25k rows. We're going to try doing the processing locally and then only sending back one row, and seeing how that effects the speed. – Jon Apr 19 '12 at 15:20