Checking the applications, real error cases
An application intended to be run in network should be checked in network in conditions similar to its real use (with concurrent accesses and real-size files).
The performance of a code executed locally or on a "single-user" network can be completely different in a real use case on a multi-user network.
Non-exhaustive list of common errors found in the existing applications. All these examples come from real reported incidents.
1. 90% of slowness issues are due to inadequate keys:
Resolution: Define the adequate keys in the analysis
In your applications, check the criteria of filters, views and queries. To get good performance at runtime, the files must have the right keys and composite keys. The keys required to get good performance depend on the sort conditions. You should take a close look at the analysis, filters, views and queries in order to select the best possible keys.
This is the reason why WINDEV proposes a query optimizer that analyzes your queries and finds the best keys for them. To optimize a query, when a query is opened in WINDEV, on the "Query" pane, in the "Analyze" group, click "Optimize the query".
It is an automatic mechanism that provides good results. However, for the complex queries or for the queries that use several optional parameters, this options is no substitute for an astute database specialist.
It is hard to say what performance gain can be achieved by using the right keys because situations can change a lot. This gain can go from a 10% improvement in processing time to a 100% improvement and even more.
Caution: if the performance test of a multi-user application is performed in single-user mode, the results of the test are not realistic. Obviously, an application should be tested in its runtime configuration.
2. The files are not "optimized"
Resolution: Start WDOptimizer or plan for an optimization process in the application (
HIndex or
HStatCalculate)
Files for which statistical calculations (or a re-index operation) have been recently performed will be more efficient. This operation does not require any change in the application. However, regular maintenance operation are required if you want to keep fast accesses to a database. To optimize the access to the files, use WDOptimizer that is provided with WINDEV or use
HStatCalculate,
HIndex.
3. Excessive use of HCreationIfNotFound
Resolution: Don't use
HCreationIfNotFound unless it is necessary, and use the
hDelayedOpening constant when
HCreationIfNotFound is required.
It is common to find in the applications a HCreationIfNotFound("*") statement or a series of HCreationIfNotFound in the initialization code of the project. This function performs a large number of checks and searches, and therefore it uses a lot of computer time. To avoid wasting time, ask for a delayed check by using the hDelayedOpening constant with HCreationIfNotFound. Even better, you can use this statement only for the files that may be deleted or re-created.
For applications that use a large number of files, this change can save a significant amount of time.
4. Initializations not required or wrongly placed
Resolution: Remove useless initializations, or move them to a more appropriate location.
When initializing the windows that contain several planes or tabs, do not initialize all the planes and tabs as soon as the window is opened. Only run the views and queries of the other planes when the user accesses them.
On the contrary, use
HOptimizeQuery in the opening code of window in order for the future execution of these queries to be even faster.
5. Wrongly defined dynamic data sources (not defined in the query editor or in the data model editors)
Resolution: Use the "Data Source" type for each dynamic data source.
When views are created by
HCreateView or when queries are created by
HExecuteSQLQuery, a name must be specified. This name allows you to use the data source (view or query) like a file. To retrieve the content of the items, use ViewName.ItemName or QueryName.ItemName.
In the code editor, ViewName (or ItemName) is not recognized. In order for the WLanguage compiler to recognize your views and queries, you must declare them. We recommend that you use the "
Data Source" type to declare them.
6. Using indirection on an item without specifying its type
Resolution: Specify the type of the indirection
You can use the indirection (braces { }) to dynamically build an item name (or a variable name...). The indirections are used to create generic code but these codes are a little bit slower. Therefore, code that performs read operations, modifications and additions and that uses the indirection can be slower when resolving the indirections than when performing the real operations on the HFSQL data files. To speed up the processes that use the indirection, you have the ability to specify the type of indirection. For more details, see
Indirection operators.
Example:{FileName+"."+ItemName,indItem}=5
7. Using the performance profiler to detect the slowdowns
Resolution: Improve the specific code deemed too slow
There are other types of errors to avoid (useless multiple read operations or initializations, ...) or other types of possible optimization (for example, we strongly recommend that you use HFSQL Client/Server on a slow network or with a DSL access).
Each case is different but the general rule is that when a slowdown is detected, it can be eliminated by finding its origin.
The performance profiler provided with WINDEV is a great tool allowing you to identify the codes that take a long time to run.