October 23, 2011 at 12:37 am
SELECT * FROM OPENQUERY( PATCH,'(
SELECT msi.segment1 Item_Number
,msi.description Item_Description
,msi.item_type item_type_code
,flv.meaning item_type_description
,msi.inventory_item_status_code item_status
,cic.item_cost IHQ_ConsolGAAP
,cic1.item_cost IHQ_USGAAP
-- ,nvl(cic2.item_cost,0) GRU_USGAAP_BRL
,cic2.item_cost * gdr.CONVERSION_RATE GRU_USGAAP_USD --New
,msiz.attribute9 Fcst_Item_Type
,mc.segment1 Product_Line
,ffv1.description Product_Line_Description
,mc.segment2 Product_Group
,ffv2.description Product_Group_Description
,mc.segment3 Product_Family
,ffv3.description Product_Family_Description
,mc.segment4 Product_Code
,ffv4.description Product_Code_Description
,ffv13.description Platform
,msiz.ATTRIBUTE13 Communication_Type
FROM mtl_system_items_b msi
,xxvfi_mtl_system_item_zoom msiz
,mtl_item_categories mic
,mtl_categories mc
,mtl_category_sets mcs
,apps.fnd_flex_values_vl ffv1
,apps.fnd_flex_value_sets fvs1
,apps.fnd_flex_values_vl ffv2
,apps.fnd_flex_value_sets fvs2
,apps.fnd_flex_values_vl ffv3
,apps.fnd_flex_value_sets fvs3
,apps.fnd_flex_values_vl ffv4
,apps.fnd_flex_value_sets fvs4
,apps.mtl_item_categories mic1
,apps.mtl_categories mc1
,apps.mtl_category_sets mcs1
,apps.fnd_flex_values_vl ffv13
,apps.fnd_flex_value_sets fvs13
,cst_item_costs cic
,cst_item_costs cic1
,cst_item_costs cic2
,fnd_lookup_values flv
,gl_daily_rates gdr
WHERE msi.organization_id = 161
AND msi.inventory_item_id = msiz.inventory_item_id
AND msiz.organization_id = 161
AND msiz.attribute_category = ''VFI Item Information''.............................and so on which is neary taking more than 1 hr to pull the data from oracle.
here i have set up the linked server also when i m trigerring this query from toad then it will execute in 29 sec can anybody help me to optimise this query and reduce the time in fraction of seconds...
October 23, 2011 at 2:46 am
Create a view on the Oracle side and query the data based on the view.
October 23, 2011 at 5:47 am
How long does the oracle query take if you just run it on oracle? And how much data is being returned?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2011 at 6:00 am
Oracle side it took only 29 sec and pull the more than 68k records.
October 23, 2011 at 6:02 am
Hi Lutz,
I think , Yeah view will be good option! Thanks I will try and post the result here!
October 23, 2011 at 6:10 am
What are you using the 68k records for? That's going to take a while to move into SQL Server... a long while. If you're doing an import you might want to consider exporting from Oracle to a flat file and then importing to SQL Server. I've found that to be the fastest way for straight imports. If you're querying the data, I'd strongly suggest filtering it on the Oracle side and then moving it across to SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2011 at 7:21 am
LutzM (10/23/2011)
Create a view on the Oracle side and query the data based on the view.
To be a little more specific (and to expand on what Grant mentioned): the view should also include the WHERE conditions in order to improve performance. I'm not sure if there's something similar to an in-line-table valued function in Oracle. If not, it might be an option to call an Oracle sproc.
October 23, 2011 at 9:30 am
It's probably not just the fact that you're looking at Oracle through a Linked Server... I see lots and lots of joins there. This might be a prime candidate for a bit of "Divide'n'Conquer". Rewrite it so the "base" rows are stored in a Temp Table and then join against that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply