When writing applications for a multiuser environment, paying attention to performance is particularly important because inefficiencies can multiply. When multiple users access data, your application must handle concurrency and network access issues.
You can use the following suggestions to improve performance and handle these issues:
Use table indexing and sorting appropriately.
Adjust the interval for lock attempts.
Use transaction processing efficiently.
You might also benefit from the suggestions for working with data stored on remote servers. For details, see Optimizing Access to Remote Data.
Indexing and Sorting Tables Appropriately
When the data in a table is relatively static, processing sorted tables sequentially without setting an order improves performance. However, this does not mean that sorted tables cannot or should not take advantage of index files, for example, the SEEK command requires an index and locates records quickly. However, after you find a record using the SEEK command you can turn ordering off.
Adjusting the Interval for Lock Attempts
If your application attempts to lock a record or table and is unsuccessful, you can specify that Visual FoxPro repeat the attempt automatically after a small interval. However, each lock attempt results in network traffic, adding congestion to any existing network traffic, and results in overall diminished performance for all users. To help avoid this scenario, you can call the SYS(3051) - Set Lock Retry Interval function to adjust the interval between lock attempts. Using a larger interval, which results in fewer retries per second, reduces network traffic and increases performance.
For more information, see SYS(3051) - Set Lock Retry Interval.
Using Transaction Processing Efficiently
When using transaction processing, design transactions so that they minimize their impact on other users. Transactions should begin and end as close to the actual data update as possible. An ideal transaction contains only data update statements.
When you append records to a table, Visual FoxPro locks the table header. The header remains locked for the duration of the transaction, preventing other users from also appending records. While a transaction remains open, any locks set during the transaction remain locked until the transaction is committed or rolled back. Even if you call an explicit UNLOCK command, locks remain until you call the END TRANSACTION command or ROLLBACK command. For more information, see UNLOCK Command, END TRANSACTION Command, and ROLLBACK Command.
For example, suppose you add transaction processing to table updates made from a form. Instead of opening a transaction, running the form, and then committing the transaction when the form is closed, put transaction processing statements in the Click event code for a Save command button as shown in the following lines of sample code:
BEGIN TRANSACTION UPDATE Products SET reorder_amt = 0 WHERE discontinued = .T. END TRANSACTION