Hey guys,
We are migrating from oracle to EDB however i can't help but notice that the query speed become noticably slower than oracle. However that is not the main problem, when we incorporated dblink connection in our query, the result that should be done in 5 seconds in oracle become 20 minutes in EDB.
Are there any particular configuration/optimization that need to be done so the query can become faster especially when using dblink? Also both server are using EDB with Postgres v10 (we are calling query dblink from EDB server targeting other EDB server)
Thanks,
Arief
Hi Arief,
Thank you for writing us. Could you please share Oracle version?
Secondly, Are source and target databases reside on same machine or different machines?
Thanks,
Ninad
In addition to the information requested by @nshahnash
can you share the explian analyze? or the query text, sometimes rewrite the query improves performance
regards!!!
Thank you for responding @asotolongo & @nshah!
We are using oracle 11 for our old database. The new source and target database are in different machine and both are using EDB with postgres 10.
Here is query that we use as a test:
SELECT d_br.branch_key, d_br.outlet_id, d_br.outlet_code, d_br.outlet, d_br.branch_class_id, d_br.branch_id, d_br.branch_code, d_br.branch, d_br.region_id, d_br.region_code, d_br.region, d_br.smho, bc.code AS branch_class, lc.latitude, lc.longitude, lc.radius_dalam, lc.radius_luar FROM ( SELECT b.outlet_id AS branch_key, b.outlet_id, b.outlet_code, b.outlet, b.branch_class_id, b.branch_id, b.branch_code, b.branch, b.region_id, b.region_code, b.region, COALESCE(u.user_name, 'WITHOUT OUTLET'::character varying) AS smho FROM ( SELECT outlet.branch_id AS outlet_id, outlet.code AS outlet_code, outlet.name AS outlet, branch.branch_class_id, branch.branch_id, branch.code AS branch_code, branch.name AS branch, korwil.branch_id AS region_id, korwil.code AS region_code, korwil.name AS region FROM branch@mfdg outlet JOIN branch@mfdg branch ON branch.branch_id = CASE WHEN outlet.branch_type::text = 'BRANCH'::text THEN outlet.branch_id ELSE outlet.parent_id END JOIN branch@mfdg korwil ON korwil.branch_id = CASE WHEN branch.branch_type::text = 'KORWIL'::text THEN branch.branch_id ELSE branch.parent_id END) b LEFT JOIN ( SELECT ub.branch_id, u.user_name FROM user_branch@mfdg ub JOIN users@mfdg u ON ub.user_id = u.user_id JOIN user_role@mfdg ur ON ur.user_id = u.user_id AND ur.role_id = 92) u ON b.outlet_id = u.branch_id) d_br LEFT JOIN lokasi_cabang@mfdg lc ON d_br.outlet_id = lc.branch_id LEFT JOIN branch_class@mfdg bc ON d_br.branch_class_id = bc.branch_class_id;
Hi Arief,
Can you please also share output of explain analyze for the query?
Thanks,
Ninad