I was able to spend quite a bit of time configuring a copy of Advantage Database Server (ADS), and then running a series of tests to compare its performance against native Visual Foxpro (VFP). For the ADS, I created a new database on the server and pointed it at a folder of VFP tables. I used Advantage’s ODBC drivers and pulled the data using a VFP CursorAdapter.
Here are some of my findings:
Test 1: Straight Performance Pulling Data
The first test I did was to take the SQL code from getTimeDetail, a method of one of my business objects that’s run probably hundreds of time each night in a variety of queries to retrieve a campaign’s detailed time tracking.
| VFP | ADS | Diff |
|---|---|---|
| 68.0 | 85.0 | 25.0% |
| 80.0 | 88.0 | 10.0% |
| 70.0 | 89.0 | 27.1% |
| 67.0 | 86.0 | 28.4% |
| 66.0 | 92.0 | 39.4% |
| 65.0 | 72.0 | 10.8% |
You can see that based on this raw data, VFP is 10-40% faster. But of course that’s not the only factor…
Test 2: Network Impact Pulling Data
One of the benefits of a client server apps are the reduced traffic going over the network. Indeed VFP creates so much NetBIOS traffic that it’s unusable over a Wide-Area Network (WAN). Since one of our goals is to reduce network traffic, I next ran a test where I used WireShark to watch the conversation between my network card and the server’s network card.
VFP SQL Direct
| Packets | Avg. Packet Size | Bytes | Avg. Bytes/Sec | Avg MB/Sec |
|---|---|---|---|---|
| 41599 | 8129.441 | 21,985,644 | 4,296,521.48 | 34.372 | 28207 | 8005.626 | 14,052,819 | 3,988,429.10 | 31.907 | 236556 | 9453.83 | 117,110,517 | 4,680,257.27 | 37.442 | 11078 | 962.88 | 6,769,441 | 588,387.69 | 4.707 |
| Packets | Avg. Packet Size | Bytes | Avg. Bytes/Sec | Avg MB/Sec |
|---|---|---|---|---|
| 6639 | 528.504 | 8,265,584 | 657,989.62 | 5.264 | 2917 | 810.477 | 3,597,375 | 999,515.98 | 7.996 | 22261 | 831.318 | 27,845,533 | 1,039,867.60 | 8.319 | 3842 | 833.387 | 4,761,968 | 1,032,942.02 | 8.264 |
Difference
| Packets | Avg. Packet Size | Bytes | Avg. Bytes/Sec | Avg MB/Sec |
|---|---|---|---|---|
| -84.04% | -93.50% | -62.40% | -84.69% | -84.69% | -89.66% | -89.88% | -74.40% | -74.94% | -74.94% | -90.59% | -91.21% | -76.22% | -77.78% | -77.78% | -65.32% | -13.45% | -29.65% | 75.55% | 75.57% |
Clearly Advantage has the, er, advantage here. There’s about 90% fewer packets coming across the network here, and quite a few less bytes as well (VFP has to pull unwanted data to get at the stuff you actually want). What’s curious however, is how much smaller the packet sizes are in ADS, causing essentially lower throughput in this test.
Our network admin thought smaller packet sizes may actually be an advantage during the batch, as the switches will be able to move smaller packets more efficiently. The tech support guys at Advantage said this will be better when multiple users are hitting the server, as well. Which leads us to the next test…
Test 3: Multi-User Speed Testing
In this test, I ran the test bed three times concurrently on 22 campaigns. This should test how well the server performs when multiple clients are querying the data simultaneously. This graph shows the aggregated average times to run each campaign.
As the Advantage tech support guy suggested, Advantage does perform better under a multi-user load. The 10-40% edge that VFP had under a single load disappeared, and it’s likely that this spread will increase under larger loads. Conclusion: Advantage scales better than FoxPro. And of course Advantage is usable over wide-area networks, allowing you to distribute your clients anywhere.
Considerations
Advantage has a very nice feature that allows you use existing VFP tables without modification, but that doesn’t mean you can just install it and, poof, everything is now client/server. If you don’t use structural indexes in your VFP tables, you’ll need to manually link the indexes to the databases using stored procedures in the database. Depending on how much forethought you put into your data access classes, switching your VFP front-end to use a non-VFP database can be fairly easy or a major undertaking.
Bear in mind these tests were done using a directory of VFP tables, not using Advantage’s native data schema. Performance may vary.
Comments
4 responses to “Results of In-Depth Testing of Visual Foxpro vs. Advantage Database Server”
-
Hmm, about the packet size – seems that’d be a different layer than db/app, so maybe ODBC vs whatever VFP uses and, probably, tunafishable, er, tunable somewhere. I know those small packets can kill the network if they get too numerous too quickly. Maybe another plus to Advantage if there is a way to change that.
-
Good point! VFP does have settings you can tweak when you set up your ODBC connection, and I thought increasing the packet size would lead to an increase in overall speed; I set SQLSETPROP(0,”PacketSize”) to everything from the default (4096) to the max (2^16), and it seemed like the larger packet size settings actually slowed it down slightly, though the avg packet size didn’t change much if at all which made me wonder if the setting did anything at all. I even tried setting the packetsize to 2k, with no perceived difference. I poked around on the server size to see if, eg, the TCP Receive Window could be changed but couldn’t find the setting. I recall in the old Netware Client all of those settings were configurable, but now it’s not easy to change. Registry perhaps?
Thanks for the feedback!
-
(via email, reposted here)
Hi Eric,To really see the power of Advantage your test needs to do some write operations (and writes that change key values), preferably at the same time it is doing reads. This will show Advantage’s read-through locking algorithm, which FoxPro can not match as it doesn’t distinguish between a client that is reading and a client that is writing data. In a multi-user situation this has a big impact on performance. Using multiple physical machines is also a much better “real world” indicator, as it accentuates
the benefit of less network traffic. If you used 5 individual PCs and ran a multiuser read/write test and compared ADS to FoxPro I think you would be quite impressed.We didn’t mention the packet size and data throughput in our last reply, but I wanted to mention that in your post you failed to account for how much “true” record data the client should have been receiving in each case.
Advantage uses an optimized streaming protocol built on UDP, and as such we have MUCH lower overhead in each burst we send. No TCP headers, netbios headers, etc. Just a thin layer around the actual record data the client requested. Just because Advantage sent smaller packets doesn’t mean it didn’t efficiently deliver the requested record data. One other nice thing about Advantage is that we can also use TCP. If there where routing or throughput issues clients can switch to using TCP for a more “standard” transport layer.
Thanks again for the blog post and for requesting our feedback,
J.D. Mullin
Advantage R&D ManagerP.S. One more note on the indexes. Just seeing the indexes in the table browser in ARC doesn’t always mean that the queries you use will utilize them. A better approach is to run the query in the query utility using the “show plan” button. This will display a graphical query execution plan, which will include a little red warning dot around any operation that could use an index, but was unable to find one. Hover your cursor over each item in the query plan to see details about that operation.
J.D.
-
[…] of VFP data into MySql in order to test the speed, similar to what I did a few posts back with Advantage Database Server. Since MySql can’t just use VFP tables the way Advantage can, I created a script to manually […]
Leave a Reply