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

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