Performance tuning

1

Hi Experts,

below table has more than 100 million record and have non-clustered index on column Cname.

query is to search customer and its being called in SP. its is taking 11 seconds to search customer because of COLLATE SQL_Latin1_General_CP1_CS_AS (reason behind using this is we have customers for example Marad and marad these two are different) instead of COLLATE do we have any other solution ?

SELECT Cname FROM dbo.Customers WHERE Cname = 'name' COLLATE SQL_Latin1_General_CP1_CS_AS

2 
WITH FindName
AS
(
	SELECT Cname
	FROM dbo.Customers
	WHERE Cname = 'name'
)
SELECT Cname
FROM FindName
WHERE Cname = 'name' COLLATE SQL_Latin1_General_CP1_CS_AS;