A scenario of database index failure: Analysis of the Problem ideas and strategies, index analysis problems

Source: Internet
Author: User

A scenario of database index failure: Analysis of the Problem ideas and strategies, index analysis problems

This is a problem about database index failure discovered by the company's R & D team. Our engineers analyzed and solved the problem and wrote this summary. In the final analysis, there is still a flaw in understanding the development framework and technical applications, however, I personally think that this article can be used as a reference in analyzing the problem-> finding out the cause-> confirming the solution and strategy, so I will share it with you a little bit.

The current problem is that the tester reported that a function operation took a long time (more than 20 seconds), and the developer checked the code and found that there was no problem in both business logic and code implementation, the involved data query and other functions have also created appropriate indexes in the database to ensure query efficiency, so our engineers will study the process as follows:

1. First, the application is embedded in a system monitoring platform JavaMelody (this is a google open source artifact https://code.google.com/p/javamelody)

2. the most time-consuming step is found by observing the frame information executed by this operation method through this artifact:


3. drill down to the inside of the method, and the SQL statement called is:


4. Analyze the SQL statement, extract it into the PLSQL Developer tool, and verify and execute the variable assignment. Repeated verification shows that each time the speed is less than 1 second:


5. Why does it take more than 20 seconds to execute on the server, but the client tool is fast? The guess is related to JDBC. In order to confirm this idea, we did a unit test. It would take more than 20 seconds in the unit test. Is it so slow for Mao to query through JDBC?


6. The biggest difference between JDBC and client queries is that the former uses bind variables, while the latter is a static SQL statement. For further verification, the SQL statement in the unit test is changed to static (in the form of non-variable binding), and it is much faster, as it is executed in step 1 PLSQL Developer client tool, wronged JDBC:

The original problem was variable binding..

If the problem is found, analyze the cause of the result. Simply put, our story is as follows:

MapSqlParameterSource parameters = new MapSqlParameterSource();parameters.addValue("queryTimeFrom", queryTimeFrom );parameters.addValue("queryTimeTo", queryTimeTo);
Among them, queryTimeFrom and queryTimeTo are parameters passed in by the method. The type is java. util. Date.
The query is called:

List<DrugOrderAdmin> result = getJdbcTemplate().query(sql.toString(), parameters, new DrugOrderAdminMapper());
I thought that the parameter passed into the SQL is of the Date type, but it actually went through the internal code of the spring framework and finally changed to the Timestamp type:


The isDateValue method is defined:


In oracle, AND PLAN_TIME> =? AND PLAN_TIME <=? Because the input parameter is of the Timestamp type, the oracle interpreter interprets the SQL statement:

AND TO_TIMESTAMP (PLAN_TIME) >=? AND TO_TIMESTAMP (PLAN_TIME) <=?

In this way, the index on the PLAN_TIME field is ignored, and the whole table is scanned (unless another function index TO_TIMESTAMP (PLAN_TIME) is created )), as a result, the final query speed is significantly different.

If the cause is found, the solution is clear, that is, when binding a variable, force the JDBC Type to DATE:

Parameters. addValue ("queryTimeFrom", queryTimeFrom); changed to parameters. addValue ("queryTimeFrom", queryTimeFrom, Types. DATE); parameters. addValue ("queryTimeTo", queryTimeTo); changed to parameters. addValue ("queryTimeTo", queryTimeTo, Types. DATE );
Return to PLSQL Developer and simulate the input parameter of the Timestamp type. This time, variables are bound, that is, a variable is reserved for all the places where parameters need to be passed in (names starting with &). The variable input dialog box is automatically displayed on the client during execution:



This time, we can see that it takes a long time for PLSQL Developer to execute a query by binding a variable and the bound variable is of the Timestamp type. The previous questions are not explained:


Conclusion: when using bind variables for SQL queries, you must note that the type of the input parameter is the same as that of the column type. If you specify the parameter type explicitly, try to specify it as clearly as possible, otherwise, the column index becomes invalid due to improper type.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.