Excellent post, Thomas!

~~Bonnie


>Hi,
>
>we did similar tests and I think I can give a few more pointers in the area Kevin's answer describes.
>
>Scenario: workstation work, single user, tables approaching and a few times exceeding 2GB (with code to split those tables in vfp) aggregating the data of all [german] insurance selling areas. The parts moved to T-SQL were at first 20 to 25% slower than the vfp code, but that was to be expected since we had tried to use a few vfp tricks to optimize and are generally more firm in vfp. After optimizing the T-SQL and the index layout in SQL server (even with parttime help a help by a SQL specialist to get "fair" results) we had comparable runtimes: nowhere a clear cut winner.
>
>Like Kevin we found that vfp worked better with relativly smaller data sets, but even more pronounced was the difference based on hardware: given oodles of RAM and 2 processors SQL Server still was getting faster. OTOH, on relativly weak machines (256-768 MB, 800Mhz-1GHz, some laptops for fun) vfp took the lead.
>
>My interpretation of the results is, that working on datasets which fit into vfp's smaller memory range, vfp is faster, possibly [only] due to the enhanced features of SQL server writing data. When given more memory than vfp can directly adress and working on datasets utilizing that much memory, SQL server is able to gain more speed, while the speed gains using a vfp based solution are reached only through a larger disk cache.
>
>Keep in mind that the test was for workstation work: AFAIR Rick Strahl summarized the pure speed loss going from vfp backends to SQL backends (in multiple instanced vfp web applications) to a factor of 2.
>Since this was some years ago, this rule of thumb probably was won on machines more in the range of the max vfp level: the factor might be a bit less given todays hardware (Remember: multiple server instances probably CAN utilize more memory <g>), but should be measurable none the less. But when people are inputing data, data loss can be much more expensive then in just analyzing data.
>So there might be reason to go for better hardware to get the same speed as vfp on cheaper machines, but with the hightened data security of SQL server...
>
>Still, the level of performance possible with vfp doing work right at vfp's 32bit breaking point of 2 Gig is marvelous. Basic data cleaning and timeseries preparation work on all customers and policies of a prominent german insurance company for a couple of years. Keeping an eye on the 2 Gig barrier is more trouble than performance with vfp.
>
>So get an estimate on the amount of data, cost equivalent of a slightliy higher danger of data loss and the kind machines to be used in the near future.
>
>HTH
>
>thomasthe area
>
>>Thanks Kevin:
>>This was a very clear answer.
>>
>>Mike
>>>Hi, Michael,
>>>
>>>Sorry for jumping in late on this, but wanted to offer some additional feedback:
>>>
>>>There was a question about whether Bonnie's answer (SQL will out perform VFP, no question) was valid. Her answer is valid...both in a server-based environment and even in some single-user environments.
>>>
>>>I've run tests (single user and server-based) of fully-optimizable joins in both products with hundreds of thousands of rows, and found SQL Server to be faster. Performance differences became even more noticeable as the queries became more complex.
>>>
>>>VFP may be a little faster when the tables are small (few hundred rows or so), but for large tables, SQL Server will typically perform SELECTs more quickly.
>>>
>>>Yes, INSERT and DELETE statements may run slower in SQL Server, because of the transaction processing and other previously mentioned factors involved. But for many CRUD activities triggered by daily user actions, this is generally not an issue for users. The architectural benefits (see below) usually outweigh the difference in performance.
>>>
>>>I believe you mentioned that you had seen bad performance in applications that use SQL Server as their back end when compared with VFP. This could have been for a variety of reasons (insufficient hardware, queries/data access methods not properly constructed, database not optimally configured, etc.).
>>>
>>>As Bonnie also stated (and as most know), SQL Server is also the clear winner when it comes to scalability and security...and I'll add things like transaction logging, out-of-the-box support for text searching, better support for the ANSI-92 standard, and protection against physical corruption.
>>>
>>>These factors are equally as important as performance comparisons, if not more. SQL Server (or MSDE for smaller installations) is typically the better choice for a back-end database.
>>>
>>>Kevin