SQL Server Query slowness with joins and like operator

utugiqy6 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(77)

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?

sql-server

来源：https://stackoverflow.com/questions/77365848/query-slowness-with-joins-and-like-operator

1条答案

按热度按时间

rggaifut1#

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
)

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Query slowness with joins and like operator

1条答案

相关问题

热门标签

最新问答