slow query time when using dblink between EDB server

[ariefhandany]

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

[nshah]

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

[asotolongo]

hi @ariefhandany

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!!!

[ariefhandany]

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; 

[nshah]

Hi Arief,

Can you please also share output of explain analyze for the query?

Thanks,

Ninad