Use the trim () method with caution in the where statement when associating Multiple SQL Server tables.

Source: Internet
Author: User
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.

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.