SQL Server 2008 - Worth the Wait
When using Visual FoxPro as a client to access SQL Server data, always do what you can to reduce network traffic between the client and the server. One of the easiest and most common ways to accomplish this is to have the client application call SQL Server stored procedures that run on SQL Server, and only return the data the Visual FoxPro clients needs. [6.5, 7.0, 2000, 2005]
*****
When you need to make secondary updates, such as cascading updates or deletes to data stored on SQL Server, it is more efficient to perform these using a SQL Server trigger than sending multiple commands to SQL Server from your Visual FoxPro client-side application. [6.5, 7.0, 2000, 2005]
Don't use the GOTO BOTTOM command against data stored on SQL Server. While this command works great for locally stored data, using in on remote data stored on SQL Server contributes to unnecessary network traffic, slows down your application because it has to deal with unnecessary data, and it may also reduce the accuracy of the data in a local cursor because changes to the SQL Server data are not automatically reflected in the local cursor, unless you issue a REQUERY. [6.5, 7.0, 2000, 2005]
If your Visual FoxPro application needs to access the same data over and over, and this data is relatively static (such as data used in common lookup tables), consider duplicating this data on both the SQL Server and the client. Design the application so if the data changes on SQL Server, that the local data is automatically updated. This reduces network traffic and the load on SQL Server, boosting the performance of the client application. [6.5, 7.0, 2000, 2005]
Take advantage of SQL Server's data integrity capabilities instead of rewriting them yourself in the client application. If the data submitted to SQL Server by Visual FoxPro violates a rule, then use FoxPro's AERROR() function to capture the message from SQL Server, and then pass it onto the application and user. [6.5, 7.0, 2000, 2005]
Unused connections between the Visual FoxPro client and SQL Server cause unnecessary overhead, so it is important to close unused connections. By default, Visual FoxPro leaves a connection open infinitely, until it is closed by the user. If you want to force a connection to close that is open, but unused, you can do so by setting the maximum amount of idle time using the IdleTimeout property of the DBSETPROP() function. Once the time specified is hit, the connection is broken. If the user then performs some task that needs the broken connection, Visual FoxPro will automatically reconnect. [6.5, 7.0, 2000, 2005] Added 8-17-2000
If you need to retrieve data from remote views or views with extremely large result sets, consider turning off Visual FoxPro's default progressive fetching feature. When you set the FetchAsNeeded property to true, rows are fetched only when needed, which can provide more efficient data retrieval. If you don't, then Visual FoxPro will fetch only 100 rows at a time by default. For large result sets, this is not efficient for your application. [6.5, 7.0, 2000, 2005] Added 8-17-2000
When designing Visual FoxPro forms that access SQL Server data, here are some tips you can follow to help boost performance: 1) don't retrieve data until it is requested by the user; 2) only request rows and columns you need; 3) avoid remote fields; 4) keep the number of forms in your form set as few as possible, because when a form set is opened, all forms in the set get populated at that time; and 5) try to avoid bound controls, especially if the return large numbers of records. [6.5, 7.0, 2000, 2005] Added 8-17-2000
INSERTs, UPDATEs, and DELETEs on SQL Server data can be speed up if the affected table has a TimeStamp field. For example, if you use the Visual FoxPro SQL WhereType update DB_KEYANDTIMESTAMP option, time is saved over not using it because Visual FoxPro only has to compare two fields, the key field and the TimeStamp field, to find any potential update conflicts. Otherwise, all of the updateable fields have to be checked, which takes more time. [6.5, 7.0, 2000, 2005] Added 8-17-2000
When using transactions in Visual FoxPro, avoid using the automatic transaction mode, which can incur substantial overhead because every single data modification is encapsulated into its own transaction. Instead, use manual transaction mode and mange all your transactions yourself through code. Manual transaction mode allows you to decide when you want to commit a transaction, and allows you to group more than one related data modifications into a single transaction, which is generally more efficient than one at a time. On the other hand, if transactions get too large, they could cause locks to be held unnecessary long, resulting in potential performance issues. [6.5, 7.0, 2000, 2005] Added 8-17-2000
If your application needs to INSERT or UPDATE multiple records at the same time on SQL Server from a Visual FoxPro front-end, consider using batch updates. This reduces network traffic overhead and is more efficient from the server's point of view. Batch processing can be set by using the DBSETPROP() function to set the BatchUpdateCount property for a view definition. [6.5, 7.0, 2000, 2005] Added 8-17-2000
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset