I'm trying to use ODBC to get at a Firebird database for the first time. The db is on the other end of a VPN link, but the pings are around 75 ms in the bad cases and the throughput is normally in the Mbps.
The database in question is only 50 MB on disk and the tables I'm talking to are generally only a few hundreds rows and maybe 20 columns nothing but char, int and float. It's all very small and light! Using RazorSQL as a UI on both the host machine and the client, I have been testing the performance of the query interface. My test queries have a few simple fkey joins, queries strings about 100 to 200 chars at the most, and the returned data is maybe 10 k or so. Running on the server, over the VPN and using RDP, the simple queries I run complete almost instantly, in hundredths of a second. Ok, works for me. Running on my client, over the VPN using ODBC directly, the exact same query takes about three seconds. What the?! More complex queries against *slightly* larger tables with a few more joins continue to complete instantly on the server, while taking between 1 and two minutes to complete when run on the client! Moreover, I can see that during the delays, the *outbound* link from the client is clicking over about 1 kbps for the entire period. It *appears* that the client is dribbling out information that the server is waiting on, although I can't imagine what it is. Does anyone have any idea why this is happening? I'm literally changing nothing but the location of the client. SQL should not care. Yet it clearly does.
