Using SQL Pass - Through (SPT) particularly using SPT calling Stored Procedures (SP) has been touted as the holy grail for client server development, particularly in comparison to remote views. As is usually the case when choosing development techniques, there are trade-offs and choosing which techniques to use requires knowledege of the trade-offs. This topic is an attempt to summarize the pros & cons of Remote Views, Stored Procedures, using SPT contructed on the fly and using ADO. Note that XML is not discussed here yet, but much of what has been said about ADO applies equally to ?XML.
Scalability
Remote views are the least scalable of all of the various techniques available because remote views require that a connection is kept open to the database the entire time the remote view is in use. This can be minimized if a middle-tier is used to provide the data, but serving data retrieved via remote views from the middle tier requires additional work.
Actually, remote views can share a single connection. In most situations all remote views share one connection and all spt traffic can be conducted over a second connection to avoid confusion on the part of the programmer and the application.

SPT, SPT with Stored Procs & ADO are all very scalable. Connections only need to be kept open when data is retrieved and updated. In addition, ADO is the only remote data access technique that natively provides a way to pass data between tiers.
The problem with ADO is that it requires COM calls for each usage.
Compare performance between a "scan" loop in VFP going thru a local cursor and doing something with each record and ADO doing the same thing. A very common scenario. The ADO calls mount quickly into the hundreds or even thousands. This causes orders of magnitude performance differences.


Note: Using any technique other than remote views does not necessarily increase scalability. The developer must write code to close connections when they aren't needed and to re-establish connections when they are needed again in order to realize any scalability gains.

Hope you don't mind my commenting here.. I'd be interested to know how many developers actually do open and close connections like this. IME against big oracle patient management databases using SPT, connections can take >2 seconds to establish when all I want is a tiny query to get the patient's address and GP. Users complain. Once the connection is open the query takes <.2 seconds. Users are happy with that. So it is left open. Am I alone? - John Ryan
You are NOT alone. Let's not confuse true client-server with internet based solutions.

John, I agree and disagree with you. I used this approach, but in general it is not reliable. What if the connection is broken from the server side? You will required to reconnect. Both SPT and RV have no ability to use re-connected connection handles (ConnectHandle property of both could not be set). So, for reliability reasons, this might be useful. As per performance, you might be interested to read about MTS caching. COM objects under MTS may connect and disconnect any number of times. MTS catches all ODBC functions and caches a lot of ODBC resources including connections to SQL Server. At least MS tells about this in their articles. This promises to be the most useful thing for such kind of applications. Another solution is to use exactly as you mentioned, but never use native VFP tools to update data, just prepare and send 'UPDATE' commands to SQL server directly. this way you will be able to maintain connection, but also will be able to reconnect in case when connection is broken. -- Vlad Grynchyshyn
In our shop, we have found that at least with DB2 on the AS/400, if the connection is lost, VFP will not know it. So, we call a function that tests the viability of the connection before each usage. It takes a few milliseconds. See the code in Visual Max Frame 4.0 Professional which was derived from our code for this purpose.


Vlad, we have very big systems in hospitals and rarely see or hear of such disconnection problems. Maybe that is partly because we use tiers but it seems to be a theoretical risk in our environment. Maybe I'm lucky. - John Ryan

John, besides Vlad's comments, you are correct that your users will be happier with faster connections (i.e. connection already open) but the issue here is scalability which suffers tremendously with trying to maintain open connections. It all depends on how many concurrent users you have and what the expected growth is. -- Alex Feldstein

The first time I did a VFP/SQL Server project, I ran some informal tests, and concluded that the trade-off favoured keeping the connection open for a long period (the whole session even) rather than constantly opening and closing it. Out of habit, I followed that same policy in later projects. I'm not saying it is the right approach for everyone, but it seems to work fine for me.

To help manage this situation, I wrote a class to manage the connection. This tests to see if a connection is available, and opens it if it isn't. This brought some extra benefits, such as the ability to make settings that are scoped to the connection (things like the date format), and also to do some generic error-handling. Mike Lewis

Perhaps the word SOME needs to be installed here as it is further down. In my example, opening and closing connections does not yield better scaling, end of story. - Johnryan

Architecture / N-Tier
Remote Views require a Visual FoxPro .DBC. Many view this as a big disadvantage of Remote Views however, a .DBC that contains nothing but Remote Views is really nothing more than a procedure file in a .DBF format. Big System s that use remote views and do not utilize a middle tier for data access require Creating DBCfor Views or else they face performance and locking problems. Typically the developer needs to add code to the application startup routine to insure that each work station is using the latest version of the DBC. Unless a middle tier mechanism is provided to serve up data from Remote Views, Remote Views are a VFP only mechanism. Other front-ends could access data via VFP remote views via ODBC, but it normally wouldn't make sense to do so.

Using Remote Views, SPT and SPT with Stored Procs in the middle tier requires that the middle tier provides a mechanism for passing data between the tiers. Among these mechanisms are converting VFP cursors to ADO record sets, converting VFP cursors to XML, converting VFP cursors to SCATTER NAME Objects, Converting VFP cursors to arrays and others.

ADO is the only data access mechanism that can retrieve data from the server and pass it to the client statelessly without performing any data conversions.

What about retrieving XML from SQL Server 2000 via HTTP? Be carefull when you make such definate statments, technology is changing every day.

IMHO with SOAP et al, xml will very soon be a *communication* but hopefully not a direct *data access* mechanism of choice. IMHO It is well worth distinguishing data access from communication mechanisms to help with this judgement. - John Ryan

Deployment
As stated above, when not used with a middle-tier, Remote Views may require a local copy of the DBC on each work station.

SPT and SPT with Stored Procs place no addiitonal requirements on the work station.

- So, is this good, bad, or just fact? You have to deploy an .EXE if you use VFP, so is this a bad thing. Should all aps be deployed on a server? What about OLE controls? You don't use them because you have to deploy and register files?

Using ADO requires that ADO is installed on each work station.

Security
Remote Views require that a user account exists that provides access to data for retrieval, insertions, updates and deletions.

SPT requires that a user account exists that provides access to data for retrieval, insertions, updates and deletions.

Using SPT with Stored Procs requires that a user account exists that provides execute rights on the procedures being called.

In 7.0 you can connect to the server with a guest account and use an ap role to upgrade your security to query the data you need.

If ADO is used to construct SQL on the fly, it has the same security requirements as SPT. If ADO is used to call stored procedures it has the same security requirements as using SPT with Stored Procs.

Flexibility / Ease of Use
Within their limitations, Remote Views are the only data access technique that can be used throughout VFP. Using remote views standard data binding can be used, including VFP grids, the VFP Report Writer can be used and functions like REQUERY() can be used. TABLEUPDATE(), TABLEREVERT() and other functions related to buffering can be used on Remote View cursors without any additional work.

In addition, OLDVAL(), GETFLDSTATE() and other tools are available to help with lock contention resolution, buffering issues, etc.. These would have to somehow be hand-coded for SPT. Ray Kirk

Simple remote views can be created using the VFP View Designer. More complex remote views require hand coding and/or the use of tools like xCase and EView.

The same remote views with little change can sometimes be used to access different back-ends.

Remote Views are the only mechanism that make remote data appear native to VFP. Remote Views can be USEd just like a table, can be used in form & report data environments, have properties set for them and their members in the DBC, have native drag and drop available for form and report construction, etc.

SPT, SPT with Stored Procs and ADO require more extensive knowledge of the back-end than Remote Views.
More to the point: you need to write specifically for each backend to make SP or SPT work. A vanilla RV that works against SQL server is very likely to work against Oracle with no app code changes. To use an Oracle SP versus a SQL Server SP requires distinct code code in the calling app. Same with SPT. - John Ryan

Um, not true. If your SPT is sending queries, perhaps. Also, you could stick with ANSI Standard stuff which will work with many servers. Of course, if you are only useing SPT to call a SP that selects, updates, or deletes data, then your client code can work with many back ends, it is the SP's that must be specific to that database. ANd, you can use ODBC escape codes to run your SP's so even the SQLExec statement is the same for both (all) backends.

Assuming we can agree that SPT is used to send queries or updates, I'm afraid it *is* true because of backend differences.
Even calling SP's requires code differences in your app. Simple example: to call an Oracle SP in one of our clients requires

SQLExec(pnConnection,"{Call mySP(par1,par2,@par3) }")

You will agree the Call and squiggly bracket is quite different from calling a SP of same name and parameters in SQL Server. QED. - John Ryan
Oops, it appears the squiggle call is actually an ODBC format that is permitted though not required for SQL Server and other databases. -JohnRyan

TABLEUPDATE() and TABLEREVERT() can be used on cursors created with SP or SPT after issuing the appropriate CURSORSETPROP() calls to allow the cursor to send the updates to the back-end.

Cursors created with SPT cannot be requeried. Changing or refreshing the data set requires that a new query is executed. This can be problematic, particularly if there are grids bound to the cursor.

Agreed. That's why we normally put the spt sql in the refresh of the grid. Unbind the control source, execute the spt, then rebind. Works pretty fast.

Remote Views, SPT and SPT with Stored Procs may encounter difficulties with data types supported in the back-end that are not supported in VFP. Because ADO can keep all data types in their native format this is often a lesser issue when working with ADO.
Can you give us an example, please?
And how does my VFP code deal with these ADO data types? Data type conversion in client-server environments is always problematic and must be dealt with by hand in most cases.

Simple Remote Views can be created with the VFP view designer. All other techiques require hand coding or using a tool that's not supplied by VFP to generate code.

The VFP report writer cannot be used directly on ADO RecordSet objects. When ADO is used for data access either the sometimes unreliable RSToCursor() function must be used or other reporting techniques need to be utilized.

For Ad-Hoc Queries Remote Views and Stored Procedures are limited by their definitions. Using SPT and ADO any valid SQL Statement can be created and executed against the back-end on the fly.

Maintenance
Using SPT, SPT with Stored Procs and ADO all require a great deal more code than remote views. This code can often be purchased and/or generated (see Data Clas), so this is not necessarily an issue.

Changes to the underlying database schema require maintenance with any technique. If data access code exists throughout your applications, changes to the database schema may require a great deal of maintenance work to locate and change all affected code. Using standard data classes should minimize this issue. With Remote Views all of the data access code should be in a single location, so it should be easier to deal with, however; changes to the database schema will most likely require redistribution of the DBC to each work station.
I recommend a case tool such as xCase. xCase makes "backend" and database maintenance much less of a hurdle, also allows you to move data from one database to another with ease whether you use VFP or not. Worth every cent. - John Ryan

Performance
The performance differences between the various techniques appear to be subtle and for the most part inconsequential. Large updates via ADO may be slower. See Client / Server Techniques Performance for the results of some limited tests. (Note, the ADO code isn't necessarily using the best techniques available for ADO.)

Additional Issues
Remote Views only update the data that has actually been changed with no additional effort on the part of the developer. With the other techniques the developer must choose between always updating all fields or checking each field for changes before updating it. Remote views require far less code to handle updates and VFP has native techniques available to detect and resolve conflicts. All of these things must either be hand-coded or purchased for the other techniques. Updating all of the fields all of the time may not be an issue in many systems.
How can you say that?! Updating all fields if just one has changed is just as much of a "scalability" issue as the connection issue that you cover in detail. SP in particular is poor in this area, typically loading the server unnecessarily almost every time you edit a row. - John Ryan

John, you can write an SP that will only update the fields sent to it. But, it would require dynamic SQL which may take the same overhead or more as it would to update all fields. This is why with VFP it is recomended that you use ONE replace statement with a list of updates, rather than many replace statements. Since each replace causes a new write. I would expect the SQL Server works this way also. If you have a very large view you could always partition your SP's.

Actually, VFP and it's predecessor FoxPro have been smart enough to cache sequential REPLACE statements since dBase IV, version 1.1. Ray Kirk

RV's work differently from local replaces against an unbuffered table which is what you describe. No matter how many replaces you do, a RV does not update the source until you move the record pointer, close the view or issue a tableupdate(). At that point, it updates only the altered fields. To replicate this in a SP would require vast coding effort in the SP, also stress the server when the RV does all the work at the client. You could replicate the functionality with SPT and I believe some people do. - John Ryan


I said that and qualified it with some systems because some CS systems run on LANs with just a few users and don't have large tables and don't have all of the scalability concerns of systems that access larger tables, have more users, run on WANs and the Internet, etc. In most systems I do think SPs that sacrifice scalability are dangerous.
OK, well we can agree that opening/closing connections may assist scaling in some systems as well. - John Ryan

Remote Views are a VFP only solution. Remote Views aren't even available in other development environments that don't have a local database solution. Using SPT & SPs to create local fox cursors are also largely VFP only solutions. ADO and XML are the data access techniques of the future (and largely the present). If you are creating systems that might eventually be moved out of VFP or that will need to expose their data to other front-ends, ADO and XML should be given strong consideration.

I think that, although a valid argument could be made to keep the SQL standard, the data types generic, etc, extending that argument to giving up the VFP data engine in order to make the code portable is carrying the concept beyond usefulness. If you want to port VFP to another language, DO IT. But, while using VFP, why give up one of the main reasons for using VFP in the first place?
Ray Kirk

Again IMO we need to distinguish data *access* from communication mechanisms, also decide whether we are talking Tiered or Client/Server systems. For C/S where the client queries the database directly, the "best" way to get data to the client is "the most efficient way" which is not ADO or XML for VFP. Getting data from a database into a VFP cursor makes perfect sense for a VFP developer in a C/S app. For a Tier, as long as you can package data effectively it does not matter a jot whether you retrieved it via VFP cursors, via HTTP from an interbase system, via ADO or even (if you are silly) as raw xml generated by the database, as long as you can pass it on using an agreed format. Worth observing that while ADO and XML are standards for communication, in health the "approved" transfer formats are things like HL7 and edifact, something that pulling data as ADO or XML does not help with at all. There are lots of other industry comms mechanisms like this as well for online payments, ordering systems etc etc... yes they are converging on XML but frankly converting VFP data to XML is pretty easy in 2001 and not a reason to pull it from the database in that format.

That's kind of the point, that these are VFP only mechanisms. Obviously if you're writing a pure VB/SQL Server app, remote views (as well as SPT cursors) aren't an option. In other environments ADO/XML are both the data access technique and the communicate technique. -- Mike Feltman

I'm not getting this, Mike. If you are using VFP, RV is an important consideration whether VB can use it or not.

I'd also suggest that XML as a data access technique is not that great an idea. Different topic though.- John Ryan

IMO it is a lot more important to consider what you need to do with the data. If you need to massage it, getting it as a VFP cursor is the best idea for a VFP developer- easy, reliable, fast. If you need to pass it on as ADO or XML, you need to decide whether you will ever need to massage the data in the tier or just pass it on. My pick is to keep using VFP cursors for now to keep my options open. - John Ryan

I agree here with John. IMHO the best way is to mix the way data are transferred. For example, when VFP client makes request for data from COM object or IIS server, it passes '1' as first parameter and gets binary data - VFP cursor. ASP page passes '2' as first parameter and gets ADO recordset as result that is most sutable for asp pages. Other application passes '3' as parameter and gets a result data as XML. Such mixed approach allows to eliminate performance issues related to XML on the one side and on the other side allows to make very scalable COM or IIS server. As about performance, see my "DCOM Tests" document in the files section at the Universal Thread site, where I compare different approaches for data transferring and their advantages/disadvantages. -- Vlad Grynchyshyn.

Tiep tuc de!!!

Conclusion
There are pros & cons to each technique and most applications will probably use a combination of some or all of these techniques to get the job done. Be aware of the pros & cons and pick the right tool for the job.

See More On Remote Views for further discussion of the pros and cons of these techniques.

---

Contributors: Mike Feltman, John Ryan, Vlad Grynchyshyn, Alex Feldstein, Ray Kirk

---

Category Client / Server Category Big System