Performance Issues Accessing SQL Server using LabWindows/CVI SQL Toolkit over VPN

Updated May 7, 2020

Reported In

Software

  • LabWindows/CVI
  • LabWindows/CVI SQL Toolkit

Issue Details

I'm communicating with a SQL server using the LabWindows/CVI SQL Toolkit. This communication works well when the computer running it is on the same network as the SQL server, but the performance drops considerably when I connect this computer to the network through a VPN. Is there a way that I can improve the performance of my SQL communication over VPN?

Solution

Microsoft advises against using VPN to access SQL servers due to performance issues, so this performance dip may be an unavoidable addition to your program's latency when used over VPN. However, there are steps to try that could increase the performance of your program, or allow you to identify what part of your program is causing the slowdown.
  • If you are using the DBActivateSQL  command to activate your SQL statements, consider switching your calls to DBActivateSQL  for calls to DBPrepareSQL and DBExecutePreparedSQL . DBActivateSQL is a static query, while DBPrepareSQL and DBExecutePreparedSQL are components of a polymorphic call which allows information to be reused in between statements. the static nature of DBActivateSQL can reduce performance over VPN due to the increased amount of time it takes to create new queries, so switching calls to this function to calls to DBPrepareSQL and DBExecutePreparedSQL can improve program performance over VPN.
  • Ensure that your SQL server program on your primary network and your LabWindows/CVI program connected to that network via VPN are communicating on the same port. Depending on what your server is expecting, your programs communicating on different ports could reduce performance by causing the packet to be analyzed more thoroughly by your server and security protocols.

Additional Information

Using Wireshark to trace your SQL communications could also be a useful tool to see if the resulting logs report any strange behavior like port mismatches or commands being sent multiple times, especially if you can analyze Wireshark logs from normal and slowed communication to see notable differences.