Search query slow down performance due to lots of records in product table , please provide nice query in sql server to optimize given query

Question

SELECT  distinct tb_Ecomm_Product.SKU as ImageName,
        tb_Ecomm_Product.MainCategory,
        tb_Ecomm_Product.SEName,
        tb_Ecomm_Product.SKU,
        tb_Ecomm_Product.ProductID, 
        tb_Ecomm_Product.Name AS PName,
        (Case When (SalePrice Is Not Null And SalePrice!=0) Then 
            SalePrice 
        Else 
            Price 
        End) As SalePrice,
        (
            (20 * dbo.WordCount('HWD AquaBug', tb_Ecomm_Product.Name)) +
            (20 * dbo.WordCount('AquaBug HWD', tb_Ecomm_Product.Name)) +
            (20 * dbo.WordCount('', tb_Ecomm_Product.Name)) +
            (20 * dbo.WordCount('', tb_Ecomm_Product.Name)) +
            (20 * dbo.WordCount('', tb_Ecomm_Product.Name)) +
            (20 * dbo.WordCount('', tb_Ecomm_Product.Name)) +
            (12 * dbo.WordCount('HWD', tb_Ecomm_Product.Name)) +
            (12 * dbo.wordcount('',tb_Ecomm_Product.SKU)) +
            (12 * dbo.WordCount('AquaBug', tb_Ecomm_Product.Name)) +
            (12 * dbo.wordcount('',tb_Ecomm_Product.SKU)) +
            (12 * dbo.WordCount('', tb_Ecomm_Product.Name)) +
            (12 * dbo.wordcount('',tb_Ecomm_Product.SKU)) +
            (12 * dbo.WordCount('', tb_Ecomm_Product.Name)) +
            (12 * dbo.wordcount('',tb_Ecomm_Product.SKU)) +
            (12 * dbo.WordCount('', tb_Ecomm_Product.Name)) +
            (13 * dbo.wordcount('',tb_Ecomm_Product.SKU))  )  as rank 
FROM tb_Ecomm_Product 
Left join tb_Ecomm_Manufacture 
    on tb_Ecomm_Manufacture.ManufactureID = tb_Ecomm_Product.ManufactureID 
INNER JOIN tb_Ecomm_ProductCategory 
    ON tb_Ecomm_Product.ProductID = tb_Ecomm_ProductCategory.ProductID 
INNER JOIN tb_Ecomm_Category 
    ON tb_Ecomm_ProductCategory.CategoryID = tb_Ecomm_Category.CategoryID
Where tb_Ecomm_Product.StoreID = 1 
    And tb_Ecomm_Category.StoreID = 1 
    And Price != 0 
    And 
    (
        (Case When (SalePrice Is Not Null And SalePrice != 0) Then 
            SalePrice
         Else Price End) != 0 
    Or 
        (Case When (SalePrice Is Not Null And SalePrice != 0) Then 
            SalePrice 
         Else Price End) != null 
    ) 
    And tb_Ecomm_Product.Status = 1
    And tb_Ecomm_Product.Deleted = 0 
    And tb_Ecomm_Category.Status=1
    And tb_Ecomm_Category.Deleted = 0  
    and 
    (
        (tb_Ecomm_Product.name like '%HWD%' 
         or tb_Ecomm_Product.Description like '%HWD%' )  
    or 
        (tb_Ecomm_Product.name like '%AquaBug%' 
         or tb_Ecomm_Product.Description like '%AquaBug%' )  
    ) 
Order by rank desc

Answer 1

Remove the join to tb_Ecomm_Manufacture, it's not used anywhere in the query.

It looks like you are using distinct because you have joined to tb_Ecomm_Category via tb_Ecomm_ProductCategory in order to do some filtering, but it is having the side effect of multiplying rows in the resultset. The best way of doing this is to remove distinct, remove both inner joins, and add an exists filter instead.

You are calling a user-defined function dbo.wordcount 16 times. It almost doesn't matter what this function does, calling it 16 times on every row is virtually guaranteed to be slow. Since the result of the UDF is only used for sorting and returning, why not return the raw data, and do the rank calculation and sorting on the middle tier instead?

The query then becomes:

select
    tb_Ecomm_Product.SKU,
    tb_Ecomm_Product.MainCategory,
    tb_Ecomm_Product.SEName,
    tb_Ecomm_Product.ProductID, 
    tb_Ecomm_Product.Name,
    (case when isnull(SalePrice, 0) != 0 then SalePrice else Price end) as SalePrice
from tb_Ecomm_Product 
where tb_Ecomm_Product.StoreID = 1
and exists (
    select null
    from tb_Ecomm_Category
    inner join tb_Ecomm_ProductCategory on tb_Ecomm_ProductCategory.CategoryID = tb_Ecomm_Category.CategoryID
    where tb_Ecomm_Product.ProductID = tb_Ecomm_ProductCategory.ProductID
    and tb_Ecomm_Category.StoreID = 1 
    and tb_Ecomm_Category.Status = 1
    and tb_Ecomm_Category.Deleted = 0
)
and Price != 0 
and isnull(case when isnull(SalePrice, 0) != 0 then SalePrice else Price end, 0) != 0 
and tb_Ecomm_Product.Status = 1
and tb_Ecomm_Product.Deleted = 0
and (
    (tb_Ecomm_Product.name like '%HWD%' or tb_Ecomm_Product.Description like '%HWD%') or
    (tb_Ecomm_Product.name like '%AquaBug%' or tb_Ecomm_Product.Description like '%AquaBug%' )
)

If I have misunderstood any aspect of your original query, please let me know and I'll edit my answer.