Greetings:

I have a number of questions regarding the use of SQLServer 6.5 as a back-end data server with VFP as a front end client. Here's my situation:

I've developed an application for a client that currently uses native VFP tables for all data storage. The system is very fast as it currently stands. The plan was, initially, to develop the initial system in VFP, then upsize the data to SQLServer 6.5. I ran the Upsizing Wizard then tried to run my application. At that point, things began to detonate.

Here are some of my questions:

1) Is there any way to alter the SQL SELECT within the remote view programatically other than the obvious way with parameters? Specifically, I'm wanting to to change the sort order of the retrieved data without creating an index each time I REQUERY the data,

2) Data retrieval times went to hell in a handbasket. I was getting retrieval of 600 - 3000 or more records in a second or two with VFP; the same thing takes several minutes (or more) with SQLServer. I realize that SQLServer is best suited for retrieving small result sets from large data sets and I've sort of got the opposite situation here -- a relatively small data set (approximately 72,000 records in the main detail table) and comparatively large result sets (600 to 3500 records on the average). Does anyone have any suggestions for speeding this up?

3) To work with the retrieved data in multiple orders, is it better to create indexes with every REQUERY or should I work with multiple remote views?

4) Regarding the retrieval times -- when I saw the results of the SQLServer times, I was very disheartened. I wondered if the problem was SQLServer itself or the speed of the network itself, so I switched around my application to look for the native VFP tables from the network. This proved to be a good exercise, since it proved that the performance problem SEEMS to be with SQLServer, since I was getting retrieval times in the 2 to 5 second range, or up to 20 seconds for some extensive recalculation on the retrieved records -- not as speedy as working on a local drive, but certainly acceptable, knowing what exactly was being performed.

Does anyone have any suggestions where I can get more information about the "nuts and bolts" of integrating SQLServer and VFP? Books? Articles? On-line examples?

I GREATLY appreciate any and all suggestions, answers, and the like regarding the above questions. Thanks in advance!

Jon