Micro-tuning database access will consistently consistent with the application and database.
If you are developing a web application, you are likely to be familiar with Java-based approaches: JDBC and SQLJ. But do you know how to get the best performance for applications that use these methods?
If you think that performance is not a problem that developers concerned, please think about it, some system execution efficiency is general, some system execution efficiency is great, what is the reason for this difference? I think this is primarily on the skills and experience of database architect designers, database administrators, and application developers.
Although database optimization should start from the initial design phase, it can also be fine-tuning earlyly established databases and related applications in the production environment. Improve performance is not only included in the database to create indexes and standardization, although these are also required to do, but only this is not enough. Accessing the application design (and how it access the database) is also critical to get the best performance.
To help you improve your application performance, you have provided some optimization techniques for data inventory for data inventory through JDBC (or using JDBC's IBM WebSphere Application Server) and SQLJ.
Optimizing the JDBC almost all enterprise-level web applications use three-layer design: representing layers, application layers, and data layers, in this model, Java-based applications connected to the database is JDBC. To optimize database access using JDBC interface, follow these guidelines: Try to avoid using metadata methods. Although the method of using metadata (describing other data and references) cannot be completely avoided, please use them as little as possible. Since they are adding abstraction, they are accessed relatively slow. Avoid using getColumns. Don't use the getColumns command lookup table details, try to use the getMetadata command for "dumb" query. Avoid using a carpet search. Use database commands in JDBC queries LIKE and AS to reduce performance, especially when querying large tables. The use of empty autoles in the query also reduces performance. Please eliminate these statements as much as possible. Reduce the amount of data to be retrieved. You only query the data you need, don't exceed this range. Do not retrieve long data lists unless absolutely necessary, do not retrieve long data lists. It is best to design a terminal program that can send several sets of data at a time, just like Yahoo! and Google's highly optimized search engine. Unless you use Select * from
, please do not use this way. Reduce the amount of data to return. Set the boundaries of the query through SetMaxRows and SetMaxFieldSize, match the boundary with the maximum data required, which will help improve performance. For example, search engines do not need to return all 600,000 match in a query. Instead, it can set reasonable boundaries that satisfy 99% of users, and for the rest of the 1% user, you need to do additional calls. This approach takes more time to build, and it is necessary to understand the needs and behavior of the user. However, in a very sensitive enterprise database system, it is worthwhile to improve performance like this. Select the appropriate data type. This method may cause too much overhead if the database design changes. However, in general, the data type of the minimum storage length required to save the data is a very good method when the table is built. For example, when an integer is needed, an integer type rather than a real or floating point number (after the two need to spend more time to handle). Use the parameters to call the argument. This rule is best coding in any application. Curing the self-variable is not flexible, often leading to future maintenance issues. At the same time, because all parameters are sent to the database as a string when calling the stored procedure, the use of fixed values will also reduce database performance. It will be better during remote process call (RPC). Select the correct cursor for the job. To make clear the type of cursor required, use a cursor that can provide the highest performance. For situations where only sequential read, you should choose a forward cursor. For applications that require query data, it is best to use the Insensitive cursor, which creates a temporary copy of the dataset, which can be traversed in the data copy, which is especially common in the web application. The Sensitive cursor is only used for large data sets and dynamic data retrieval because they are usually overhead in performance. Manage connection. Although the application server like WebSphere can handle a large number of session management, it should also be paid attention to some optimization skills. For example, only one data source is connected at a time. Avoid executing the SQL calls to connect to the database to disconnect the high overhead. And try to share the connection to achieve better performance. DB2 Universal Database (UDB) V8.1 also provides a centralized and simplified mechanism for connectivity, which improves the performance of the application by effectively managing database connections. Close AutoCommit. This is the most important rule in the database.
You don't even have to consider it, just set WSConnection.SetAutocommit (false). Use local transactions as much as possible. Distributed transactions will be slower. Use the updatexxx command to replace the general UPDATE command. This method is not applicable for systems that often change database design. But in the design of a relatively static system, the updatexxx command improves performance because a targeted command is always faster than the general command. Even for the most complex, using JDBC's web applications, the above recommendations can still achieve good performance. Optimization WebSphere Because WebSphere complies with the J2EE specification, many of its connectors use JDBC, so these optimized recommendations for JDBC are equally applicable to it. However, when using WebSphere, you need to know two important optimization techniques:
Use GetBestrowidentifier (). Use this command to get the best column set, which will reduce processing time. Use getPrimaryKeys () and getIndexinfo () to format the WHERE clause, which will help to minimize overhead. Use the Statement object. Time to perform only one-time access to the database, processed with the Statement object. The cost of the PreparedStatement object is larger than Statement, and there is no additional benefit for one-time operation.
Optimizing SQLJ SQLJ can replace JDBC, it has a specific advantage. When the optimal performance is achieved, SQLJ is more efficient than JDBC. However, SQLJ flexibility is not as follows, as it uses more fixed static values without providing a variable alternative method. The difference between the two database access methods is similar to the difference between the compilation program (SQLJ) and the use of interpretation programs (JDBC) (some processing is performed before running, the latter compiles the runtime And execute). SQLJ is a high-level language that covers the functionality of JDBC, but it requires very stringent, specific functions to ensure maximum performance. Like JDBC, SQLJ is one of the standards of the Java program, which maintains Java platform independence. However, SQLJ does not meet J2EE, so it does not support it in all application servers. In many ways, enterprise-level web applications that requires the highest performance should be connected to DB2 efficiently, and SQLJ is one of the key factors. Other databases do not support SQLJ at this level, so it usually makes DB2's operating performance better than its competitors. Supporting Web DB2 should be difficult to avoid performance loss, especially for transaction processing systems. However, in real static implementations, SQLJ and DB2 have performance advantages over other databases. The following guidelines will help you use this cutting-edge technology: explicitly close and release resources. If an application connected to a database requires long run, one of the most important rules is to clear the event session at the correct timing. Remember that anyone is not desirable to re-boot the system. Close the result set after completion. It usually misses this step, but if you do not perform this step, the Java Virtual Machine (JVM) garbage collection mechanism cannot be recycled. Final application may deplete JDBC resources, and may even deplete memory. Update and access the required fields. Returning reiterating that only the required data should be access, do not exceed this range. Custom SQLJ PROFILER and request online check. This step prevents SQLJ from dynamically access data, thereby reducing performance. This rule will ensure that SQLJ is still running static. Tuning the size of the JVM heap. 1MB of default heap is often not big enough, which will affect performance. Increase the size of the pile to meet the needs of the application. However, pay attention to: Setting the size of the stack to the maximum frequency of the garbage, which will affect performance. Keep the version up to date. Try to use the new version. Although it takes some energy to transplant, it is usually worth doing this. SQLJ and JDBC are more new standards, and each new version will have a more prominent performance improvement. Open the cache. This rule helps you avoid the overhead to prepare dynamic SQL. Setting cachedyn = YES will help improve the performance of SQLJ code. By fine-tuning SQLJ, the performance of the web application accesses the database can catch up with the performance when using JDBC. Choosing your own way to optimize the database. There are many possible ways. Don't expect every way to apply all methods. If you start with only one way, you should get a performance improvement. By preparing for work and appropriate programs, even the most complex database applications can achieve optimal performance. Don't forget, the journey of a thousand miles begins with a single step.
