Query tuning required for expensive query-CodePudding

Can someone help me to optimize the code? I have other way to optimize it by using compute column but we can not change the schema on prod as we are not sure how many API's are used to push data into this table. This table has millions of rows and adding a non-clustered index is not helping due to the query cost and it's going for a scan.

create table testcts(
name varchar(100)
)
go
insert into testcts(
name
)
select 'VK.cts.com'
union
select 'GK.ms.com'

go

DECLARE @list varchar(100) = 'VK,GK'

select * from testcts where replace(replace(name,'.cts.com',''),'.ms.com','') in (select value from string_split(@list,','))

drop table testcts

CodePudding user response：

One possibility might be to strip off the .cts.com and .ms.com subdomain/domain endings before you insert or store the name data in your table. Then, use the following query instead:

SELECT *
FROM testcts
WHERE name IN (SELECT value FROM STRING_SPLIT(@list, ','));

Now SQL Server should be able to use an index on the name column.

CodePudding user response：

If your values are always suffixed by cts.com or ms.com you could add that to the search pattern:

SELECT {YourColumns} --Don't use *
FROM dbo.testcts t
     JOIN (SELECT CONCAT(SS.[value], V.Suffix) AS [value]
           FROM STRING_SPLIT(@list, ',') SS
                CROSS APPLY (VALUES ('.cts.com'),
                                    ('.ms.com')) V (Suffix) ) L ON t.[name] = L.[value];