SQL Server association query charindex function Query slow, do not use index problem resolution

Source: Internet
Author: User

Problem:

There's a lot of data on both tables.

The A1 field in a table needs to be associated with the B table primary key query

A1 field stores multiple B table primary keys

The format is:

Format 1:B1,B2,B3

Format 2:B4

Format 3:b5,b6

Comma-delimited minority

This leads to the use of like '%...% ', charindex, or replace (A1,B1, ') <> A1 in the context of the associated query, and the use of these methods in SQL Server to query will trigger full table retrieval. Causes the index of the field to fail, the efficiency is very low;

Solution:

Premise: The comma-separated data and directly associated with the amount of data is different, such as a total of 100 data, of which 99 are A1 directly storage B table primary key, there is a store of a number of B table primary key, separated by commas

Then a separate query can be used, and a single record stored in multiple stored values will be merged with the Union keyword, such as:

  

Select Aa.* from A AA
where exists (
Select a.pk from a b where a.a1 like '%,% ' and charindex (B.PK,A.A1) > 0 and aa.pk = a.pk
UNION ALL
Select a.pk from a b where a.a1 =b.pk and aa.pk = a.pk
)

In fact, according to the size of the data will need to use the full table to retrieve the data separately, without affecting other data query speed does not require full table retrieval, so as to solve the problem of query lag

  

  

  

SQL Server association query charindex function Query slow, do not use index problem resolution

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.