搜索查询 - 搜索多个表和列

2021-09-10 00:00:00 tsql sql-server

我有一个数据库，在该数据库中我可以在一次自由文本搜索中返回有关多个实体的信息，这是一个示例数据库:

I have a database in which I return information about several entities in a single free text search, here is an example database:

dbo.Electrician

ElectricianId | Company     | TelNo     | Mobile   | Addr1        | Postcode
123           | Sparky 1    | 01234567  | 0789078  | 42 lower ave | Ex2345
124           | Sparky 2    | 01235678  | 0777777  | 1 Street     | Ta6547
125           | Sparky 3    | 05415644  | 0799078  | 4 Air Road   | Gl4126

dbo.Painters

PainterId     | Company     | TelNo     | Mobile   | Addr1        | Postcode
333           | Painter 1   | 01234568  | 07232444 | 4 Higher ave | Ex2345
334           | Painter 2   | 01235679  | 07879879 | 5 Street     | Ta6547
335           | Painter 3   | 05415645  | 07654654 | 5 Sky Road   | Gl4126

dbo.Clients

ClientId | Name            | TelNo     | Mobile   | Addr1        | Postcode
100333   | Mr Chester      | 0154 5478 | 07878979 | 9 String Rd  | PL41 1X
100334   | Mrs Garrix      | 0254 6511 | 07126344 | 10 String Rd | PL41 1X
100335   | Ms Indy Pendant | 0208 1154 | 07665654 | 11 String Rd | PL41 1X

我目前的方法是这样工作的:

My current method is working as such:

创建临时表(EntityId、DisplayName、LongName、EntityType)

Create Temp Table (EntityId, DisplayName, LongName, EntityType)

在用逗号替换空格并将其用作 CSV 之前，获取搜索词并替换不需要的字符.

Take search terms and replace unwanted characters before replacing spaces with commas and using this as a CSV.

SET @searchTerms = LTRIM(RTRIM(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(LTRIM(RTRIM(@searchTerms)), ',', ' '),
        '[', ''),
        ']', ''),
        '#', ''),
        '&', ''),
        ';', ''),
        '?', ''),
        '`', ''),
        '''', ''),
        '*', ''),
        '"', ''),
        '<', ' '),
        '>', ' '),
        '-', ' '),
        '(', ' '),
        ')', ' '),
        '\', ' '),
        '/', ' ')))

        SET @searchTerms = REPLACE(@searchTerms, ' ', ',')

        DECLARE @SearchTerm AS nvarchar(50);

        DECLARE @DevelopmentCursor AS CURSOR;
        SET @DevelopmentCursor = CURSOR
        FOR
        SELECT
          *
        FROM general.Csvtoquery(@searchTerms)
        WHERE value != ''

接下来，我遍历搜索词，将每个实体插入到我的临时表中:

Next I loop over my search terms inserting each entity into my Temp table:

            INSERT INTO #tempsearchtable (EntityId, Name, LongName, EntityType)
            SELECT
                tc.ClientId,
                tc.Title + ' ' + tc.FirstName + ' ' + tc.LastName,
                tc.Title + ' ' + tc.FirstName + ' ' + tc.LastName + ', ' + COALESCE(a.NameOrNumber, '') + ', ' + COALESCE(a.Street, '') + ', ' + COALESCE(a.Town, '') + ', ' + + ', ' + COALESCE(a.County, '') + ', ' + COALESCE(a.Postcode, '') + ', ' + COALESCE(a.Country, '')  + ', ' + COALESCE(tc.EmailAddress, '')  + ', ' + COALESCE(REPLACE(tc.Telephone, ' ', ''), '')  + ', ' + COALESCE(REPLACE(tc.Mobile, ' ', ''), ''),
                'Client'
            FROM 
                dbo.Clients tc
            LEFT JOIN 
                dbo.[Address] a ON tc.AddressId = a.AddressId
            WHERE 
                tc.FirstName LIKE '%' + @SearchTerm + '%'
                OR tc.LastName LIKE '%' + @SearchTerm + '%'
                OR tc.EmailAddress = @SearchTerm
                OR REPLACE(tc.Telephone, ' ', '') LIKE '%' + @SearchTerm + '%'
                OR REPLACE(tc.Mobile, ' ', '') LIKE '%' + @SearchTerm + '%'
                OR a.NameOrNumber LIKE '%' + @SearchTerm + '%'
                OR a.Street LIKE '%' + @SearchTerm + '%'
                OR a.Postcode LIKE '%' + @SearchTerm + '%'
                OR a.County LIKE '%' + @SearchTerm + '%'
                OR a.Town LIKE '%' + @SearchTerm + '%'
                OR a.Country LIKE '%' + @SearchTerm + '%'

我现在再次循环搜索.这是为了确保我只得到特定的匹配.我删除了 LongName 不包含我的搜索词的任何内容.

I now loop my searches again. This is to ensure I am only getting specific matches. I delete anything where the LongName doesn't contain my search term.

在删除临时表之前，我从临时表中选择了所有内容.

I select all from the temp table before dropping it.

虽然这确实有效，而且效果很好，但搜索速度比我想要的要慢，我一直在寻找加快速度的建议.其中之一是创建一个索引表并将所有实体转储到其中，并且只有 1 个循环获取特定搜索.这稍微快一点，但这也意味着我只有最后一个任务设置为将数据转储到索引时的数据.实时搜索势在必行.

While this does work, and works pretty well, the search is slower than I'd like and I was looking for suggestions to speed this up. One of which was to create an index table and dump all the entities into this, and just have 1 loop getting the specific searches. This is slightly faster but it also means I only have data for when the last task was set to dump the data into the index. Live searches are imperative.

感谢您的任何建议.

推荐答案

我不确定这是否会更快，但是您是否尝试过创建一个字符串并在该字符串上使用 LIKE?

I'm not sure if this would be any faster, but have you tried creating one string and using LIKE on that one string?

类似于:

SELECT
  ...
FROM 
  dbo.Clients tc
  LEFT JOIN 
    dbo.[Address] a ON tc.AddressId = a.AddressId
WHERE 
  REPLACE( tc.FirstName + '|' + tc.LastName + '|' + tc.EmailAddress + tc.Telephone + '|' + ....., ' ', '' ) LIKE '%' + @SearchTerm + '%'

考虑到 SQL 在解析方面并不是那么好，我想知道 LIKE 是否执行了惰性表达式搜索，可以使这种方法比使用大量 OR 语句更快.'|'管道标志是为了防止jared"等搜索词匹配Jar Jar"、Edwards"等的名字 + 姓氏.

Considering that SQL is not that great with parsing, I wonder if the LIKE performs a lazy expression search that could make this approach faster than using a barrage of OR statements. The '|' Pipe signs are to prevent search terms like "jared" from matching a FirstName + LastName of "Jar Jar" "Edwards", etc.

相关文章