How to Optimize SQL Query

See more:

SQL

SQL-Server-2008R2

Hi, I need some help on how to optimize my query on its speed performance.

This is my query:

select Deleted = CASE WHEN a.GMC <> isnull(c.gmc,'')
                    OR (UPPER(a.OrderUOM) <> ISNULL((select top 1 UPPER(order_uom) from iPLEXMAT_SUPPLIER where ad_mat_id = b.ad_mat_id and season_id = b.Season_id and ISNULL(order_uom,'') <> ''),'')
                    OR UPPER(a.BaseUOM) <> ISNULL((select top 1 UPPER(base_uom) from iPLEXMAT_SUPPLIER where ad_mat_id = b.ad_mat_id and season_id = b.Season_id and ISNULL(base_uom,'') <> ''),'')
                    or isnull(c.gmc,'') = '')
                  THEN 1 ELSE a.Deleted END,c.Mat_ref_id,a.Desc1,c.Usage_Class,a.GMC,b.Season_id,c.Run_id,c.Content_Class,b.Ad_mat_id
    FROM (select gmc,MatNo,Desc1,mat_ref_id,BaseUOM,OrderUOM,Deleted from YTI_ERP..Material)a,dbo.iPLEXMAT_MATERIAL_PARM b
      INNER JOIN dbo.iPLEXMRP_USAGE c ON c.Mat_ref_id = b.Mat_ref_id AND B.Season_id = C.Season_id
    WHERE a.Deleted <> 1 --AND c.Content_class = @content_class
         and a.Mat_ref_id = c.Mat_ref_id and UPPER(a.desc1) = UPPER(c.material_desc)
         and c.Run_id = @run_id
         and c.Usage_Class IN('AUV','MUV')


UNION ALL

--ASUV

    select Deleted = CASE WHEN a.GMC <> c.gmc
                    OR (UPPER(a.OrderUOM) <> ISNULL((select top 1 UPPER(order_uom) from iPLEXMAT_SUPPLIER where ad_mat_id = b.ad_mat_id and season_id = b.Season_id and ISNULL(order_uom,'') <> ''),'')
                    OR UPPER(a.BaseUOM) <> ISNULL((select top 1 UPPER(base_uom) from iPLEXMAT_SUPPLIER where ad_mat_id = b.ad_mat_id and season_id = b.Season_id and ISNULL(base_uom,'') <> ''),'')
                    or isnull(c.gmc,'') = '' )
                  THEN 1 ELSE a.Deleted END,c.Mat_ref_id,a.Desc1,c.Usage_Class,a.GMC,b.Season_id,c.Run_id,c.Content_Class,b.Ad_mat_id
    FROM (select gmc,MatNo,Desc1,mat_ref_id,BaseUOM,OrderUOM,Deleted from YTI_ERP..Material)a,dbo.iPLEXMAT_MATERIAL_PARM b
      INNER JOIN dbo.iPLEXMRP_USAGE c ON c.Mat_ref_id = b.Mat_ref_id AND B.Season_id = C.Season_id
      INNER JOIN dbo.iPLEXMRP_ASUV_DTL d ON c.Run_id = d.Run_id and c.Mat_ref_id = d.Mat_ref_id AND C.Season_id = D.Season_id
    WHERE a.Deleted <> 1 --AND c.Content_class = @content_class
         and a.Mat_ref_id = c.Mat_ref_id and UPPER(a.desc1) = UPPER(d.material_desc)
         and c.Run_id = @run_id
         and c.Usage_Class = 'ASUV'


UNION ALL

--ASDUV

    select Deleted = CASE WHEN a.GMC <> c.gmc
                    OR (UPPER(a.OrderUOM) <> ISNULL((select top 1 UPPER(order_uom) from iPLEXMAT_SUPPLIER where ad_mat_id = b.ad_mat_id and season_id = b.Season_id and ISNULL(order_uom,'') <> ''),'')
                    OR UPPER(a.BaseUOM) <> ISNULL((select top 1 UPPER(base_uom) from iPLEXMAT_SUPPLIER where ad_mat_id = b.ad_mat_id and season_id = b.Season_id and ISNULL(base_uom,'') <> ''),'')
                    or isnull(c.gmc,'') = '' )
                  THEN 1 ELSE a.Deleted END,c.Mat_ref_id,a.Desc1,c.Usage_Class,a.GMC,b.Season_id,c.Run_id,c.Content_Class,b.Ad_mat_id
    FROM (select gmc,MatNo,Desc1,mat_ref_id,BaseUOM,OrderUOM,Deleted from YTI_ERP..Material)a,dbo.iPLEXMAT_MATERIAL_PARM b
      INNER JOIN dbo.iPLEXMRP_USAGE c ON c.Mat_ref_id = b.Mat_ref_id AND C.Season_id = B.Season_id
      INNER JOIN dbo.iPLEXMRP_ASCUV_DTL d ON c.Run_id = d.Run_id  and c.Mat_ref_id = d.Mat_ref_id AND C.Season_id = D.Season_id
    WHERE a.Deleted <> 1 --AND c.Content_class = @content_class
         and a.Mat_ref_id = c.Mat_ref_id and UPPER(a.desc1) = UPPER(d.material_desc)
         and c.Run_id = @run_id
         and c.Usage_Class = 'ASDUV'

Could you give me any suggestions on how can I optimized it to make its speed performance faster?
Or could you please tell me what should I do?
Thanks in advance :)