Query slowness with joins and like operator

Question

This is my SQL query:

SELECT B.TransactionId 
FROM BillingInfo B
INNER JOIN OfficeCustomers OC ON B.CustomerId = OC.Id
INNER JOIN CustomerContact CC ON CC.Id = OC.ContactId
WHERE CC.FirstName + ' ' + CC.LastName LIKE '%yog%'
ORDER BY B.TransactionId 
    OFFSET (0) ROWS FETCH NEXT (50) ROWS ONLY

Execution plan

This query takes around 6 seconds to complete. What can be done to improve the performance?

Answer 1

The two comments are accurate.

If possible, replace LIKE '%yog% by LIKE 'yog%.

You can also try the following query:

WITH contacts AS
(
    SELECT CC.Id
    FROM CustomerContact CC
    WHERE CC.FirstName LIKE '%yog%' OR CC.LastName LIKE '%yog%'
)
SELECT B.TransactionId 
FROM BillingInfo B
    INNER JOIN OfficeCustomers OC ON B.CustomerId = OC.Id
    INNER JOIN contacts CC ON CC.Id = OC.ContactId
ORDER BY B.TransactionId
OFFSET (0) ROWS FETCH NEXT (50) ROWS ONLY;

You can also create the following index:

CREATE NONCLUSTERED INDE IX_BillingInfo_Customers ON BillingInfo
(
    CustomerId
)
INCLUDE
(
    TransactionId
)