sql server - Search query - Searching multiple tables and columns

Question

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:

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

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 + '%'

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.

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.

Thanks for any suggestions.

Answer 1

Some observations first:

• If your tables are the same in structure (as it looks here), it was better not to keep different tables but to keep them all in one with an additional column to set the "DataType".

• The column Addr1 looks, as if you are keeping more than one address within your table. Whenever you feel the need to number a column's name (phone1, phone2), you should think about a related side table.

Back to your issue:

It looks as if you want to search within all column values whether a given string (or a list of given strings) is fullfilled

I do not know If I've got this completely correctly, but you might try this:

Edit: used wrong table name...

--Just two of your tables

DECLARE @tblElectrician TABLE(ElectricianId INT,Company VARCHAR(100),TelNo VARCHAR(100),Mobile VARCHAR(100),Addr1 VARCHAR(100),Postcode VARCHAR(100));
INSERT INTO @tblElectrician VALUES
 (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');

DECLARE @tblPainters TABLE(PainterId INT,Company VARCHAR(100),TelNo VARCHAR(100),Mobile VARCHAR(100),Addr1 VARCHAR(100),Postcode VARCHAR(100));
INSERT INTO @tblPainters VALUES
 (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');

--The search terms (after your cleaning)

DECLARE @SearchTerms TABLE(LookFor VARCHAR(100));
INSERT INTO @SearchTerms VALUES('Spar'),('78');

--The query will start with a UNION on the tables to get them as one. (This is actually the way this should be stored)

WITH UnionedData aS
(
    SELECT 'E' AS DataType, ElectricianId AS [Id],Company,TelNo,Mobile,Addr1,Postcode
    FROM @tblElectrician
    UNION ALL
    SELECT 'P',PainterId,Company,TelNo,Mobile,Addr1,Postcode
    FROM @tblPainters
) 
,AllValues AS
(
    SELECT a.*
          ,(
            SELECT b.* 
            FROM UnionedData AS b
            WHERE a.Id=b.Id
            FOR XML PATH('x'),TYPE
           ).query(N'data(/x/*)').value(N'.',N'nvarchar(max)') AS Concatenated
    FROM UnionedData AS a
)
SELECT AllValues.*
FROM AllValues
WHERE EXISTS(SELECT 1 FROM @SearchTerms AS st 
             WHERE AllValues.Concatenated LIKE '%' + st.LookFor + '%');

The intermediate result of the cte AllValues is this (only columns Id and Concatenated):

id     concatenated (all values as one string)
123    E 123 Sparky 1 01234567 0789078 42 lower ave Ex2345
124    E 124 Sparky 2 01235678 0777777 1 Street Ta6547
125    E 125 Sparky 3 05415644 0799078 4 Air Road Gl4126
333    P 333 Painter 1 01234568 07232444 4 Higher ave Ex2345
334    P 334 Painter 2 01235679 07879879 5 Street Ta6547
335    P 335 Painter 3 05415645 07654654 5 Sky Road Gl4126

The trick is to use XQuery's abilities to deal with generic data. First I create an XML of all columns with SELECT * and then I use .query(N'data(/x/*)'), which will return a list of all values separated by a blank.

With the final SELECT I use EXISTS to check, if there is at least one hit within the table @SearchTerms.

The final result (looking for Spar and 78) will return all "Sparky" rows and "Painter 2", due to the Mobile number with a "78"