Reports

One thing I might do is create a persisted and indexed precomputed column to filter on:

FirstNameLastName AS (concat(FirstName, LastName)) PERSISTED

-- …

CREATE NONCLUSTERED INDEX IDX_CustomerContact_FirstNameLastName
    ON dbo.CustomerContact(FirstNameLastName ASC);

Possibly even a second one for LastNameFirstName, since you want to enable both ways.

I would probably also do some further normalisation on these, such as stripping all whitespace, hyphens etc., and whatever else may be relevant for Hebrew specifically. Perhaps do something about diacritics and letter case. Apply the same normalisation to the input.

You could then do something like this:

with OffCon as (
    select Id, concat(FirstName, ' ', LastName) AS CustomerName
    from CustomerContact
    where FirstNameLastName like concat(dbo.Normalise(@CustomerName), '%')
    
    union
    
    select Id, concat(FirstName, ' ', LastName) AS CustomerName
    from CustomerContact
    where LastNameFirstName like concat(dbo.Normalise(@CustomerName), '%')
)
select
    -- …
from BillingInfo B
    inner join OfficeCustomers OffCus on OffCus.Id = B.CustomerId
    inner join OffCon on OffCon.Id = OffCus.ContactId
    -- …

What also seems to be killing you is GN.eMobility. Not sure what to do about that. How fast is it without those joins?

79796699