0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.