Article ID 1841
Article Title INFO: Checking Performance of Applications
Article URL http://www.dataaccess.com/kbasepublic/kbprint.asp?ArticleID=1841
KBase Category Connectivity
Date Created 06/24/2003
Last Edit Date 10/18/2006


Article Text
Applications accessing DataFlex embedded database performs differently than applications using any other database backend. In order to measure the performance of your application, consider the following:

1. Client/Server: when the client is separated from the server, the performance of Client/Server databases is better.
For DataFlex, the optimal environment is to have a local application accessing a local database. For any Client/Server database, the database will perform better if clients and servers are separated. For the performance comparison to be fair, configure your environment taking into account that difference.

2. Network traffic: Client/Server databases perform better under heavy loads; DataFlex is worse under heavy loads

3. Processor and memory: these may affect Client/Server databases more than DataFlex, they are more sensitive to memory and processor capacity than DataFlex.


Here is a list of variables that may affect the performance of DataFlex applications accessing Client/Server databases (like Microsoft SQL Server and DB2) and should be considered when evaluating performance:


1. Cache files: the speed the files are opened is affected by the use or not of the Connectivity Kits cache files (.CCH). Using cache files affects performance positively.

2. Open method: using driver prefix to open your tables is faster than using Intermediate (.INT) files. If you add the driver name before the tables you open, you can speed up the process.

3. Null value in columns: do not allow NULLs in fields especially if they are used in indexes
From the "Compatibility Issues" white paper:
For a proper understanding, a few things should be clear. First of all, DataFlex does not implement a null concept! Secondly, when we say that a column has the null value we mean that we do not know what the value of the column is. Thirdly, null values greatly complicate logical expressions. The expression outcome changes from a binary value (true, false) to a three-valued value (true, false, unknown). Next to this, there are special syntactical rules for handling null values. Lastly, ordering a column that allows null values is slower than ordering the same column that does not allow null values. In other words, columns that allow nulls in indexes will make finding slower.

4. Transactions: Use Begin_Transaction and End_Transaction in parent/child loops
When submitting a request to the database server, SQL statements are built in three steps: prepare, execute and fetch. The cheapest operation is fetch and the most expensive is prepare. The more number of fetches we have, the better. One way of getting more statements reused (i.e. avoiding prepares) is to have transactions enclosing parent/child loops.

Example:

clear orderhea
find eq orderhea by index.1
showln "Order Number: " orderhea.order_number
move orderhea.order_number to orderdtl.order_number
find eq orderdtl by index.1
begin_transaction
while (orderdtl.order_number = orderhea.order_number)
  showln "Detail number " orderdtl.detail_number
  showln "       item   " orderdtl.item_id
  showln "       qty    " orderdtl.qty_ordered
  find ge orderdtl by index.1
end
end_transaction


5. Find operations: Limit FINDs to the ones that are absolutely necessary -- find is a very costly operation.

6. File_Modes: If you do not use file_mode at all in your program, it would be beneficial to start. When a file is not written to, make sure that its file_mode is Read_Only. Read_only cursors are faster.


More information can be found in the Connectivity Kit User's Guide and in published White Papers.




Contributed By:
Marcia Booth
Company: Data Access Worldwide
Web Site: http://www.dataaccess.com

Links Related to this Article
WhitePaper: Compatibility Issues Between DataFlex Databases and Databases Accessed Through CLI Based
WhitePaper: Transactions, Locking and DataFlex

Email this Article
Email this Article to a Colleague
Send Feedback on this Article to Data Access Worldwide
Copyright ©2024 Data Access Corporation. All rights reserved.

The information provided in the Data Access Technical Knowledge Base is provided "as is" without warranty of any kind. Data Access Corporation disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall Data Access Corporation or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if Data Access Corporation or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply.