search for text across multiple tables 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 &lt;= 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.
You have a few different challenges that are affecting performance.


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 

Using a scalar valued function in your resultset

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

Using a scalar valued function in a join

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

Using a wildcard at the start of the search string

car.Descr LIKE '%' + @SearchText

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.