How to check on which column to create Index to optimize performance

Question

I have below query which is costing too much time and i have to optimize the query performance. There is no index on any of the table.

But now for query performance optimization i am thinking to create index. But not sure on particulary which filtered column i have to create index. I am thinking i will do group by and count the number of distinct records for all the filtered column condition and then decide on which column i should create index but not sure about this.

    Select * from ORDER_MART FOL where FOL.PARENT_PROD_SRCID 
IN 
(
select e.PARENT_PROD_SRCID
from SRC_GRP a 
JOIN MAR_GRP b ON a.h_lpgrp_id = b.h_lpgrp_id    
JOIN DATA_GRP e ON e.parent_prod_srcid = b.H_LOCPR_ID
WHERE a.CHILD_LOCPR_ID != 0 
AND dt_id BETWEEN 20170101 AND 20170731 
AND valid_order = 1  
AND a.PROD_TP_CODE like 'C%'    
)
AND FOL.PROD_SRCID = 0 and IS_CAPS = 1;

Below is my query execution plan:

What tables are partitioned on what keys? What's the explain plan? Why can't you add indexes? — Mat, Aug 22 '17 at 13:07
I have added the query execution plan and i cannot add indexes because of permission restrictions in PROD — Andrew, Aug 22 '17 at 13:21
You have `exists` but no correlation clause. That looks suspicious. — Gordon Linoff, Aug 22 '17 at 13:31
actually previously i used IN instead on exists just to give try if it improves the performance. I dont understand abt correlation clause ? — Andrew, Aug 22 '17 at 13:37
Just an added note: without any index, you know, as much as you try, you won't be able to get the best performance, right? — Renato Afonso, Aug 23 '17 at 08:08
@RenatoAfonso yes i know but i am just trying to reduce the amount of time its taking to run the query. — Andrew, Aug 23 '17 at 11:19

score 1 · Accepted Answer · answered Aug 22 '17 at 14:14

1

Select * 
from ORDER_MART FOL 
     INNER JOIN (
           select distinct e.PARENT_PROD_SRCID
           from SRC_GRP a 
           JOIN MAR_GRP b ON a.h_lpgrp_id = b.h_lpgrp_id    
           JOIN DATA_GRP e ON e.parent_prod_srcid = b.H_LOCPR_ID
           WHERE a.CHILD_LOCPR_ID != 0 -- remove the lines from                               INT_CDW_DV.S_LOCAL_PROD_GRP_MAIN  with child prod srcid equal to 0
           AND dt_id BETWEEN 20170101 AND 20170731 
           AND valid_order = 1 --and is_caps=1 
           AND a.PROD_TP_CODE like 'C%'    
          ) sub ON sub.PARENT_PROD_SRCID=FOL.PARENT_PROD_SRCID 
where FOL.PROD_SRCID = 0 and IS_CAPS = 1;

What if you use JOIN instead of IN and add distinct to reduce amount of rows in the subquery.

answered Aug 22 '17 at 14:14

StanislavL

56,971
9
68
98

yes its little bit faster. My query runs in 523.968 seconds and you query is running in 438.493 seconds. But still its taking little bit. Cant we optimze it more ? – Andrew Aug 22 '17 at 14:27
1

Unfortunately I have no magic to optimize it blindly:-). Post the DB tables (columns/types). Is it possible to apply more restriction to the subquery. E.g. `FOL.PROD_SRCID = 0 and IS_CAPS = 1` could it be also applied to the subquery to reduce amount of rows? – StanislavL Aug 22 '17 at 14:32
Unfortunately there is no additionaly condition i can put in the sub query. And there is no special column like CLOB used in the query tables. So its just normal varchar,number data type used – Andrew Aug 22 '17 at 15:16

How to check on which column to create Index to optimize performance

1 Answers1