search for text across multiple tables

Question

I've got the following query.

SELECT xcli.ID_X_Table, cli.ID, cli.Presentation, COALESCE(cli.MobilePhone, cli.BusinessPhone, cli.HomePhone), EmailAddress, cli.Descr
FROM B_Client cli
INNER JOIN X_Table xcli ON xcli.TableName = 'B_Client' AND ISNULL(cli.Flg_Deleted, 0) = 0
WHERE cli.EmailAddress LIKE '%'+@SearchText+'%'
UNION 
SELECT xcli.ID_X_Table
      , cli.ID
      , cli.Presentation
      , ISNULL(cli.MobilePhone, ISNULL(cli.BusinessPhone, cli.HomePhone))
      , cli.EmailAddress
      , cli.Descr
FROM B_Client cli
INNER JOIN X_Table xcli ON xcli.TableName = 'B_Client'
                        AND ISNULL(cli.Flg_Deleted, 0) = 0
WHERE RTRIM(ISNULL(cli.ClientName, '')) + ' ' + LTRIM(ISNULL(cli.FirstMiddleName, '')) LIKE '%'+@SearchText+'%'
    OR RTRIM(ISNULL(cli.FirstMiddleName, '')) + ' ' + LTRIM(ISNULL(cli.ClientName, '')) LIKE '%'+@SearchText+'%'
    OR SUBSTRING(RTRIM(ISNULL(cli.FirstMiddleName, '')), 0, PATINDEX('% %', RTRIM(ISNULL(cli.FirstMiddleName, '')))) + ' ' + LTRIM(ISNULL(cli.ClientName, '')) LIKE '%'+@SearchText+'%' 
UNION 
SELECT xcli.ID_X_Table
       , cli.ID
       , cli.Presentation
       , dbo.Get_B_Car_RegNum(car.ID, GETDATE()) as RegNum
       , car.Presentation
       , cli.Descr
FROM B_Car car
INNER JOIN X_Table xcli on xcli.TableName = 'B_Client'
INNER JOIN B_Client cli on cli.ID = [dbo].[Get_B_Car_ID_B_Client](car.ID, GETDATE())
WHERE car.CarPotential = 0
    AND (car.Descr LIKE '%'+@SearchText
        OR (SELECT TOP 1 RegNum FROM B_Car_RegNum
            WHERE ID_B_Car = car.ID
                AND Dte_Start <= GETDATE() ORDER BY Dte_Start DESC) LIKE '%'+@SearchText+'%') 

This query searches for user-provided text across multiple tables (it's a sort of primitive search engine). The unions overlap because they are added programmatically if the input is appropriate. For example if the input contains letters it won't search for telephone numbers and so on.

The query is quite slow in production. It takes minutes to retrieve the query. The tables contain hundreds of thousands entries. I'm not sure if this query performs poorly due to its design, or because of the magnitude of data.

Tips and critique are welcome.

Answer 1

You have a few different challenges that are affecting performance.

1. Expressions on the field used to filter, rather than converting the filtered result. EX: RTRIM( ISNULL( cli.ClientName, '' )) + ' ' + LTRIM( ISNULL( cli.FirstMiddleName, '' )) LIKE '%' + @SearchText + '%'

   AND  ISNULL( cli.Flg_Deleted, 0 ) = 0 
   

2. Using a scalar valued function in your resultset

   RegNum = dbo.Get_B_Car_RegNum( car.ID, GETDATE()),
   

3. Using a scalar valued function in a join

   ON cli.ID = dbo.Get_B_Car_ID_B_Client( car.ID, GETDATE())
   

4. Using a wildcard at the start of the search string

   car.Descr LIKE '%' + @SearchText
   

5. Possibly, the use of UNION rather than UNION ALL.

The expressions and wildcards negate the use of any indexes and force a table scan on the underlying table. As an example changing AND ISNULL( cli.Flg_Deleted, 0 ) = 0 to AND ( CLI.Flg_Deleted IS NULL OR cli.Flg_Deleted = 0 ) can allow the optimizer to use an index on Flg_Deleted because the desired value is not dependent on an expression

The scalar valued function turns your set based query into a procedural query, (one row at a time) for which SQL Server is not optimized. See if you can convert this into an inline table-valued function. You should also consider dumping the result into a table variable and join on that. That would at least prevent running the udf for every possible combination of your joined table.

Each UNION will check for duplicates in the top and bottom result sets. If the data is guaranteed unique between the result sets, you'll gain some performance by switching to UNION ALL, removing the distinct sort across the combined results.

Answer 2

Not sure if this will be faster but I think it is easier to read

Materialize the CTE(s) may help performance.

with CTEcliB as 
( SELECT cli.ID, cli.Presentation
       , COALESCE(cli.MobilePhone, cli.BusinessPhone, cli.HomePhone) as 'Phone'
       , EmailAddress, cli.Descr  
       , Ltrim(RTRIM(ISNULL(cli.ClientName, '')))      as 'ClientName' 
       , LTRIM(RTRIM(ISNULL(cli.FirstMiddleName, ''))) as 'FirstMiddleName'
    FROM B_Client cli 
   WHERE ISNULL(cli.Flg_Deleted, 0) = 0 
), CTEcar as 
( SELECT ID, RegNum 
       , row_number() over (partition by ID order by Dte_Start desc) as rn 
    FROM B_Car_RegNum 
   WHERE Dte_Start <= GETDATE() ORDER BY Dte_Start DESC 
)

SELECT xcli.ID_X_Table
     , cli.ID, cli.Presentation, cli.Phone, cli.EmailAddress, cli.Descr
  FROM CTEcliB cli
  JOIN X_Table xcli 
    ON xcli.TableName = 'B_Client'
 WHERE cli.EmailAddress LIKE '%'+@SearchText+'%'
    OR cli.ClientName + ' ' + cli.FirstMiddleName LIKE '%'+@SearchText+'%'
    OR cli.FirstMiddleName + ' ' + cli.ClientName LIKE '%'+@SearchText+'%' 
 UNION  
SELECT xcli.ID_X_Table
     , cli.ID, cli.Presentation
     , dbo.Get_B_Car_RegNum(car.ID, GETDATE()) as RegNum
     , car.Presentation, cli.Descr
FROM B_Car car
JOIN X_Table xcli 
  on xcli.TableName = 'B_Client'
JOIN B_Client cli 
  on cli.ID = [dbo].[Get_B_Car_ID_B_Client](car.ID, GETDATE())
WHERE car.CarPotential = 0
  AND (     car.Descr LIKE '%'+@SearchText
        OR (SELECT RegNum 
              FROM CTEcar
             WHERE ID_B_Car = car.ID
               AND RN = 1) LIKE '%'+@SearchText+'%'
      )

I sure hope ON xcli.TableName = 'B_Client' is only returning one row and you have an index on that column.

Answer 3

Your where clauses are causing the query to run slowly

WHERE RTRIM(ISNULL(cli.ClientName, '')) + ' ' + LTRIM(ISNULL(cli.FirstMiddleName, '')) LIKE '%'+@SearchText+'%'
    OR RTRIM(ISNULL(cli.FirstMiddleName, '')) + ' ' + LTRIM(ISNULL(cli.ClientName, '')) LIKE '%'+@SearchText+'%'
    OR SUBSTRING(RTRIM(ISNULL(cli.FirstMiddleName, '')), 0, PATINDEX('% %', RTRIM(ISNULL(cli.FirstMiddleName, '')))) + ' ' + LTRIM(ISNULL(cli.ClientName, '')) LIKE '%'+@SearchText+'%'

The slow down here is that you are performing two trims and using a like comparison on every single row of the data set, and then returning the rows that match.

Execute the Select Statement without the Where clause and you will see how many rows that each trim and like is being performed on.

---

Another way to see where the slow down is occurring is to take the union statements out and see which query is actually running slowly