how can i optimize query

Posted by Santosh4u under Sql Server on 9/26/2018 | Points: 10 | Views : 458 | Status : [Member] | Replies : 1
help me to optimize this query , its taking long time to get result. any better idea to replace like condition in case statment

Insert into tbl_Details
SELECT a.[COST],a.[Ch_Group] as 'CHGROUP',a.[PRODHIR],d.[MATERIAL],
a.[UCC],
CASE WHEN SUM(CAST(d.[Qty3] AS FLOAT))<='0.00000' THEN '0.00000'
ELSE
(
SUM(CAST(d.[Qty3] AS FLOAT))/
(SELECT (SUM(CAST(p.[Qty3] AS FLOAT))) AS 'SUM'
FROM [tbl_OP6] p WHERE CAST(p.[Qty3] as FLOAT)>0 AND p.[PROD_HIER] LIKE a.[PRODHIR]+'%'
AND CONCAT(p.[Qty2],p.[Qty3A],p.[Qty4A]) LIKE case when a.[Ch_Group] like 'PM60%' then '%' else a.[Ch_Group]+'%' end and p.[zsdistrib] like a.[UCC]+'%'
AND p.[PROD_HIER]LIKE a.[PRODHIR]+'%' AND CAST(p.[Qty3] AS FLOAT)>'0.00000')
)
end
AS 'GSV'
,'' as 'BUFCG'
FROM TEMP2_New a
INNER JOIN [tbl_OP6] d ON CONCAT(d.[Qty2],d.[Qty3A],d.[Qty4A]) LIKE case when a.[Ch_Group] like 'PM60%' then '%' else a.[Ch_Group]+'%' end and d.[zsdistrib] like a.[UCC]+'%'
AND d.[PROD_HIER]LIKE a.[PRODHIR]+'%' AND CAST(d.[Qty3] AS FLOAT)>'0.00000'
WHERE a.[RULE]='4'
GROUP BY a.[COST],a.[Ch_Group],a.[PRODHIR],d.[MATERIAL],a.[ORDERTYPE],
a.[VALUEFIELD],a.[UCC],
CONCAT(d.[Qty2],d.[Qty3A],d.[Qty4A]),a.[COORDER],a.[PLANT],a.[SEND_CCTR]




Responses

Posted by: Sankalppatil on: 11/1/2018 [Member] Starter | Points: 25

Up
0
Down
Follow the below the steps.
Index all the predicates in JOIN, WHERE, ORDER BY and GROUP BY clauses.
Avoid using functions in predicates.
Avoid using wildcard (%) at the beginning of a predicate.
Avoid unnecessary columns in SELECT clause.
Use inner join, instead of outer join if possible.

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response