Pre-compiled SQL statements on SQL bound variables

Links: https://wenwen.sogou.com/z/q727183268.htm?g_f=11301026
1. Recognize bound variables: binding variables to reduce parsing, such as you have a statement such as select AAA,BBB from the CCC where ddd=eee; If you often query by changing the value of the Eee this predicate, like the following select AAA,BBB from the CCC Where Ddd=fff;select aaa,bbb from the CCC where Ddd=ggg;select aaa,bbb from the CCC where ddd=hhh; Each statement is parsed by the database once, which is a waste of resources if the Eee is swapped for To ": 1" as a binding variable, regardless of the value behind DDD, there is no need to repeatedly parse the Java implementation of bound variables: [Java] view plaincopypreparedstatement pstmt = Con.preparestatement ("UPDATE employees SET Salay =?")  WHERE id =? ");  Pstmt.setbigdecimal (1, 15.00);   Pstmt.setint (2, 110592);  /result statmement:update Employees SET Salay = 15.00 WHERE id = 110592 pstmt.executequery (); Suppose you want to update the payroll for an employee with an ID from 1 to 10000 to $150.00 without using a binding variable: [Java] View plaincopysql.executequery ("update employees SET Salay = 150.00  WHERE id = 1 ");  Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 2");  Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 3");  Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 4");  ..... sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 10000"); Using a bound variable, you:[Java] View plaincopypreparedstatement pstmt;  for (id = 1; ID < 10000; id) {if (null = = pstmt) pstmt = Con.preparestatement ("UPDATE employees SET Salay = ?    WHERE id =? ");    Pstmt.setbigdecimal (1, 150.00);     Pstmt.setint (2, id);  Pstmt.executequery (); The difference is that instead of binding a variable, it is equivalent to parsing and executing 1w SQL statements repeatedly. With a binding variable, the parse SQL statement is used only once, followed by a 9,999-second reuse of the execution plan that was generated for the first time. Obviously, the latter will be more efficient. 2. When you should not/do not need to use binding variable a. If you use a data warehouse, a large query for a few hours, there is no need to do binding variables, because the consumption of the resolution is negligible. B. Variables have a significant impact on the optimizer's execution plan: When a bound variable is used, the query optimizer ignores its specific value, so its estimated accuracy is far less accurate than using literal values, especially if the table has data skew (the data on the table is unevenly distributed) on the column that provides the wrong execution plan. This allows for a non-efficient execution plan to be used. 3. Binding variable implementation in Oceanbase the binding variable is currently implemented in Oceanbase, primarily for programming convenience, rather than for reducing the cost of generating execution plans. Why is it? Because the oceanbase is currently using a "static execution plan", no matter what query, the execution process is the same. OB implements binding variables in the front-end proxy obconnector, to_string () the variables passed in by the user, replacing the corresponding portions of the SQL statement to form a complete SQL. This SQL is then passed to Ms,ms to parse and execute according to the standard process. Believe that in the near future, OB will realize the true meaning of binding variables, so that users enjoy the benefits of binding variables.

Precompiled SQL statements on SQL bound variables