I want to optimized the below query

SQL++
1

select (case when child_Item_type=‘CO’ then ‘Component’ when child_Item_type=‘PP’ then ‘Abstract Price’ when child_Item_type=‘PR’ then ‘ProductSpec’
when child_Item_type=‘EG’ then ‘ActivityCharge’ End) Type ,name_max(root_cid)Product_Offer_Name,name_max(child_id)Child_Name,‘Mobile Product’ ProductSpec,
inclusion(relation_id,pcversion_id) Inclusion,MINIMUM_QUANTITY, MAXIMUM_QUANTITY ,DEFAULT_QUANTITY,refpath_l(relation_id) Path From Tbrel Tbr Where Root_Cid != Parent_Id And Child_Item_Type = ‘PP’
And Root_Cid in (select PO_ID from POLISTFORREFINEMENT where upper(FILE_NAME)=‘YELLOW’)
and (tbr.root_cid,tbr.pcversion_id) in (Select Tbc.Cid ,Max(Tbc.Pcversion_Id) From Tbcatalog Tbc where tbc.item_type =‘OF’ and tbc.cid = tbr.root_cid group by tbc.cid)
Union
select (case when child_Item_type=‘CO’ then ‘Component’ when child_Item_type=‘PP’ then ‘Abstract Price’ when child_Item_type=‘PR’ then ‘ProductSpec’
when child_Item_type=‘EG’ then ‘ActivityCharge’ End) Type ,name_max(root_cid)Product_Offer_Name,name_max(child_id)Child_Name,‘Mobile Product’ ProductSpec,
Inclusion(Relation_Id,Pcversion_Id) Inclusion,Minimum_Quantity, Maximum_Quantity ,Default_Quantity,Refpath_L(Relation_Id) Path
From Tbrel Tbr Where Root_Cid != Parent_Id And Child_Item_Type = ‘CO’
And Root_Cid In (Select Cid From Tbcatalog Where Item_Type = ‘PR’)
And Tbr.Root_Cid in (select cid from Tbna where name_text=‘Mobile Product’)
and (tbr.root_cid,tbr.pcversion_id) in (Select Tbc.Cid,Max(Tbc.Pcversion_Id) From Tbcatalog Tbc where tbc.item_type =‘PR’ and tbc.cid = tbr.root_cid group by tbc.cid);

2

Hi Neeta, it’s hard to optimize queries without knowledge of the business domain model. You can use our query workbench Advise button, here showed in the Jetbrains plugin. It’s also available on VSCode and Couchbase Capella’s workbench.

image
image988×303 31.7 KB