SQL Query taking too much time to execute on production

Question

SQL Query taking too much time to execute. Working fine at UAT. I need to compare data of two tables and want to get difference. Below mention is my query.

Select *
from tblBrandDetailUsers tbdu 
inner join tblBrands tbs on tbs.BrandId = tbdu.BrandId
left join tblBrandDetails tbd on tbd.CategoryId = tbdu.CategoryId
    and tbd.BrandId = tbdu.BrandId
    and tbd.CityId = tbdu.CityId
inner join tblCategory tc on tbdu.CategoryId = tc.CategoryId
inner join tblCity tcc on tcc.CityId = tbdu.CityId  
where isnull(tbdu.SaleAmount,-1) <> isnull(tbd.SaleAmount,-1)
and isnull(tbdu.CityId,0) =  3
and isnull(tbdu.TopLevelCategoryId,0) = 2;

Need to optimize query.

Answer 1

a number of things you need to check:

1. number of rows for each table. the more rows you have the slower it gets. Do you have the same size of data with UAT?
2. SELECT * : avoid the * and only retrieve columns you need.
3. ISNULL function on left side of the WHERE predicate will scan the index because it is non-sargable. you can check the answer here and rewrite your predicate without any function on the left side of WHERE clause.

You need to provide a detailed information like actual execution plan. I can only give you a generic answer because not much detail was provided.

Remember the UAT is very different in PROD. the hardware you used, the number of rows, etc..

Answer 2

Every advises in comment looks right. The difference between UAT and Prod should be the volume of data. Your issue should come of lack or inefficient indices. You should add compound index on

tblBrandDetails.CategoryId,tblBrandDetails.BrandId, tblBrandDetails.CityId

and on

tblBrandDetailUsers.CategoryId,tblBrandDetailUsers.BrandId, tblBrandDetailUsers.CityId

ensure that all unique ids have a btree index (or similar type of index depending on your DB)

You can also add conditional indices to filter quicker the null values: https://www.brentozar.com/archive/2015/09/filtered-indexes-and-is-not-null/

Answer 3

Rewrite your query like this :

Select *--> AVOID "*" put all the necessary columns
from   tblBrandDetailUsers AS tbdu 
       inner join tblBrands AS tbs on tbs.BrandId = tbdu.BrandId
       left join tblBrandDetails AS tbd on tbd.CategoryId = tbdu.CategoryId
                                     and tbd.BrandId = tbdu.BrandId
                                     and tbd.CityId = tbdu.CityId
       inner join tblCategory AS tc on tbdu.CategoryId = tc.CategoryId
       inner join tblCity AS tcc on tcc.CityId = tbdu.CityId  
where  tbdu.SaleAmount <> tbd.SaleAmount
  and  tbdu.CityId =  3
  and  tbdu.TopLevelCategoryId = 2
UNION ALL
SELECT * --> AVOID "*" put all the necessary columns
from   tblBrandDetailUsers AS tbdu 
       inner join tblBrands AS tbs on tbs.BrandId = tbdu.BrandId
       left join tblBrandDetails AS tbd on tbd.CategoryId = tbdu.CategoryId
                                     and tbd.BrandId = tbdu.BrandId
                                     and tbd.CityId = tbdu.CityId
       inner join tblCategory AS tc on tbdu.CategoryId = tc.CategoryId
       inner join tblCity AS tcc on tcc.CityId = tbdu.CityId  
where  tbdu.SaleAmount IS NULL
  AND  tbd.SaleAmount IS NULL
  and  tbdu.CityId =  3
  and  tbdu.TopLevelCategoryId = 2;

Modify the SELECT clause to have only the necessary columns and not *

Be sure that you have index that are close to :

For tblBrandDetailUsers TABLE :

1. index KEY (CityId, TopLevelCategoryId, BrandId, CategoryId) INCLUDE (SaleAmount)

2. index KEY (CityId, TopLevelCategoryId, CategoryId) INCLUDE (SaleAmount)

For tblBrandDetails TABLE :

1. index (CityId, BrandId, CategoryId)

And also :

1. tblCategory (CategoryId)

2. tblCity (CityId)

3. tblBrands (BrandId)

When you will rectify the query especially the SELECT clause, we can give you more accurate indexes, because selected columns have a big weight on indexes performances !

Answer 4

as other suggested, try to add index on columns used for joins