SQL Server's like '%xxx% ' optimizations, full-text indexing

Source: Internet
Author: User

20 million rows of data table, first of the Address field to do '%xxx% ' fuzzy query

This is the estimated query plan

This is the estimated actual query results, took 37 seconds to complete the query

Or the previous data, but this time using ' xxx% ' to do the query, and now do not index

Query speed of 10 seconds, still do a full table scan

The next one is not a fuzzy query, direct =, query a step more "parallelism"

Three seconds to complete the query, is also very slow, should have gone all the table scan

Now create a normal index for the address field

After the normal index is built, try to find '%xxx% ', from the query plan, '%xxx% ' is not available to the normal index

Sure enough, the query is time-consuming and not indexed as before, basically unchanged

Now try to query ' xxx% ', according to the query plan can see, this query can walk just the normal index we built

The result of the query is 4 seconds, and the query result is 10 seconds before the index is indexed.

Next, Direct = lookup, you can see the use of the index

The query took 0 seconds to the millisecond level, and from this point, it can be seen that the normal nonclustered indexes support the direct match (=) query with the best, then the like ' xxxx% ', and the like '%xxx% ' does not support

Then we build a full-text index on the Address field

The following is the usage syntax for full-text indexing, and the query process

After the full-text index is added, the query time is 2 seconds, or a little slow, and then tested several times, usually in a second or so

In addition, SQLServer2008 's full-text index does not seem to be completed immediately, but need to wait in the background for a period of time to fully establish, in this period of time the results of the query returned is not the same.

For example, after two queries, the result is more than the previous one, the full-text index is being established, and finally there is a stable state.

SQL Server's like '%xxx% ' optimizations, full-text indexing

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.