Our beta database (same server) data is 1 month behind live. However we did not much changes.

What's wrong is query time of stored procedure. Beta has no issue and return results in 2 sec top. Live query takes about 2 minutes.

So I decided to go full-text instead LIKE operator. Now it takes even more. I had to rebuild catalogs. First result was empty.

Procedure:

ALTER PROCEDURE [dbo].[GetShortSearch]
     @keyword nvarchar(32),
     @skip int,
     @take int
      AS
      BEGIN
 SELECT DISTINCT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ProductAttributes pa ON pa.ProductId = p.Id LEFT JOIN Manufacturer m ON m.Id = p.ManufacturerId LEFT JOIN ProductTags pt ON pt.ProductId = p.Id LEFT JOIN Tags t ON t.Id = pt.TagId LEFT JOIN ProductFeatures pf ON pf.ProductId = p.Id LEFT JOIN Features f ON f.Id = pf.FeatureId LEFT JOIN FeaturesPredefinedValues fpv ON fpv.Id = pf.FeatureValueId LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId)

WHERE p.Enabled = 1 AND ((p.ManufacturerId IS NOT NULL AND m.Name = @keyword) OR CONTAINS(p.Name, @keyword) OR p.SellCode = @keyword OR p.StoreCode = @keyword OR CONTAINS(p.ShortDescription, @keyword) OR CONTAINS(fpv.Value, @keyword) OR CONTAINS(pf.Value, @keyword) OR t.Name = @keyword)
ORDER BY Id OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;
END

Calling via

GetShortSearch N'item', 0, 10;

Products items in database: 45 000+-

Product Features: 140 000+-

Product tags: 400+-

Manufacturers: 350+-

Features predefined values: 32

I dont exactly know how live db can be slower with same query and 99% same data. Database structure is generated by EF Core 3.1.

Almost forgot to mention that I dropped last night original db and reimported all data back in. No change :/

EDIT: If I rewrote stored procedure OR clause into UNION clause. Result is now 3 seconds.

Following script (not in stored procedure):

DECLARE @keyword nvarchar(32) = N'item';

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND CONTAINS(p.Name, @keyword)

UNION

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND p.SellCode = @keyword

UNION

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND p.StoreCode = @keyword

UNION

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND CONTAINS(p.ShortDescription, @keyword)

UNION

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN Manufacturer m ON m.Id = p.ManufacturerId LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND m.Name = @keyword

UNION

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ProductFeatures pf ON pf.ProductId = p.Id LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND CONTAINS(pf.Value, @keyword)

UNION

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ProductFeatures pf ON pf.ProductId = p.Id LEFT JOIN Features f ON f.Id = pf.FeatureId LEFT JOIN FeaturesPredefinedValues fpv ON fpv.Id = pf.FeatureValueId LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND CONTAINS(fpv.Value, @keyword)

UNION

SELECT p.Id, p.BruttoPrice as 'Price', p.Name, s.FriendlyUrl as Link, i.ThumbnailPath as 'Image', i.AlternativeName as 'ImageAlt'
 FROM Products p INNER JOIN SEO s ON s.ProductId = p.Id LEFT JOIN ProductTags pt ON pt.ProductId = p.Id LEFT JOIN Tags t ON t.Id = pt.TagId LEFT JOIN ImageFiles i ON i.Id = (SELECT TOP 1 pim.ImageId FROM ProductImages pim WHERE pim.ProductId = p.Id ORDER BY pim.DisplayOrder, pim.ImageId) WHERE p.Enabled = 1 AND t.Name = @keyword

ORDER BY Id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Any advice how to optimize more?
Where should I create indexes? EF Core created just indexes for Primary Keys