Use the trim () method with caution in the where statement when associating Multiple SQL Server 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.