Because of the unreasonable preliminary database design, the length of the primary and Foreign keys associated with multiple tables is inconsistent. As a result, when the fields that are too long in the primary and Foreign keys are filled with the same field, the extra part is filled with null characters. The trim () method is used in the where statement associated with multiple tables.
Because of the unreasonable preliminary database design, the length of the primary and Foreign keys associated with multiple tables is inconsistent. As a result, when the fields that are too long in the primary and Foreign keys are filled with the same field, the extra part is filled with null characters. The trim () method is used in the where statement associated with multiple tables.
For example:
Select. key, B. key, C. key from A, B, C where trim (. key) = trim (B. fk) and trim (. col) = trim (C. pk ).
It takes one second to associate primary Table A (more than 200 records) with Appendix B (more than 40 thousand records). This value may vary with machines, however, it is a little slower than trim, but not especially obvious.
Its SQL statement is similar to the following:
Select A. key, B. key from A, B where trim (A. key) = trim (B. fk)
However, after adding the third table C (two records) to the preceding SQL statement, the SQL statement is as follows:
Select. key, B. key, C. key from A, B, C where trim (. key) = trim (B. fk) and trim (. col) = trim (C. pk)
The entire SQL statement has been executed for more than 70 seconds. The trim () method is more than 60 seconds.
Later, we found through several experiments that this multi-table join condition in the where clause can be equivalent to the general multi-table join efficiency if the trim () method is not added to the left join condition.
The improved SQL statement is as follows:
Select A. key, B. key, C. key from A, B, C where A. key = trim (B. fk) and A. col = trim (C. pk)
The execution efficiency of this SQL statement is basically equivalent to the speed of SQL statements without trim () in the where statement.
