TOAD Pro 3.6 Cross connection queries Performace is Low.

Toad Data Point
1

Hi,

Tool used : TOAD Pro Edtion 3.6.

I am using Heterogeneous query for Joining the 4 tables. The 2 DBs are :

(1) Oracle

(2) ODBC Generic - Informatica

The records present in these tables are huge. Now while executing the simple DML queries after joining these tables taking a lot of time(almost 4-5hrs) . Could you please let me know whether there is any limitations with number of records that should be used in heterogeneous queries or do you have any other ways or setttings to change to optimize the queries so that the output will come faster.

Number of records present in the tables are below:

table-1 : 164168 records

table-2 : 1256526 records

table-3 : 4366394 records

table-4 : 6941 records

Thanks,

Bharat

2

Can you publish your query here?

Thanks,

Igor.

3

Select * from TAR.VS_Leones.VT_DEAL_PRICING_B a, SRC.OCDW.FIN_ARR_PRICING_B b, SRC.OCDW.LOAN_B c, SRC.OCDW.TIME_B d

where

a.deal_id = c.LOAN_NUM and

b.FIN_ARR_ID = c.LOAN_ID and

d.curr_day_ind = ‘Y’ and b.record_type_cd=‘D’ and

d.calendar_dt between b.begin_dt and b.end_dt

4

The queries that is used :

Select * from TAR.VS_Leones.VT_DEAL_PRICING_B a, SRC.OCDW.FIN_ARR_PRICING_B b, SRC.OCDW.LOAN_B c, SRC.OCDW.TIME_B d

where

a.deal_id = c.LOAN_NUM and

b.FIN_ARR_ID = c.LOAN_ID and

d.curr_day_ind = ‘Y’ and b.record_type_cd=‘D’ and

d.calendar_dt between b.begin_dt and b.end_dt

5

Please published your query we are waiting .

6

Select * from TAR.VS_Leones.VT_DEAL_PRICING_B a, SRC.OCDW.FIN_ARR_PRICING_B b, SRC.OCDW.LOAN_B c, SRC.OCDW.TIME_B d

where

a.deal_id = c.LOAN_NUM and

b.FIN_ARR_ID = c.LOAN_ID and

d.curr_day_ind = ‘Y’ and b.record_type_cd=‘D’ and

d.calendar_dt between b.begin_dt and b.end_dt

7

Hi,

i am not able to paste the query in this editor. not sure why.

I have already mail the query to Igor to his Dell mail id.

Thanks,

bharat

8

This is the query that was used :

Select * from TAR.VS_Leones.VT_DEAL_PRICING_B a, SRC.OCDW.FIN_ARR_PRICING_B b, SRC.OCDW.LOAN_B c, SRC.OCDW.TIME_B d
where
a.deal_id = c.LOAN_NUM and
b.FIN_ARR_ID = c.LOAN_ID and
d.curr_day_ind = ‘Y’ and b.record_type_cd=‘D’ and
d.calendar_dt between b.begin_dt and b.end_dt

9

There are a couple of things you can do with filters and subqueries to optimize this query. Please check out this white paper. Right now you have to change the query on your own but in future releases we are looking at doing some of these SQL rewrites for the user.

http://www.toadworld.com/products/toad-data-point/b/weblog/archive/2012/11/21/how-to-write-a-cross-database-query.aspx

10

Hi bharat,

The way the tables are joined is most likely causing the slow performance. Try the below SQL.

Stewart.

SELECT *

FROM tar.vs_leones.vt_deal_pricing_b a

JOIN

(

SELECT *

FROM src.ocdw.fin_arr_pricing_b b,

src.ocdw.loan_b c,

src.ocdw.time_b d

WHERE

b.fin_arr_id = c.loan_id

AND d.curr_day_ind = ‘Y’

AND b.record_type_cd=‘D’

AND d.calendar_dt BETWEEN b.begin_dt AND b.end_dt

) sub_query

ON a.deal_id = sub_query.loan_num

11

Hi bharat,

The way the tables are joined is most likely causing the performance issue. Try this SQL.

Stewart.

SELECT *

FROM tar.vs_leones.vt_deal_pricing_b a

JOIN

(

SELECT *

FROM src.ocdw.fin_arr_pricing_b b,

src.ocdw.loan_b c,

src.ocdw.time_b d

WHERE

b.fin_arr_id = c.loan_id

AND d.curr_day_ind = ‘Y’

AND b.record_type_cd=‘D’

AND d.calendar_dt BETWEEN b.begin_dt AND b.end_dt

) sub_query

ON a.deal_id = sub_query.loan_num

12

Hi bharat,

The way the tables are joined is most likely causing the performance issue. Try this SQL.

Stewart.

SELECT *

FROM tar.vs_leones.vt_deal_pricing_b a

JOIN

(

SELECT *

FROM src.ocdw.fin_arr_pricing_b b,

src.ocdw.loan_b c,

src.ocdw.time_b d

WHERE

b.fin_arr_id = c.loan_id

AND d.curr_day_ind = ‘Y’

AND b.record_type_cd=‘D’

AND d.calendar_dt BETWEEN b.begin_dt AND b.end_dt

) sub_query

ON a.deal_id = sub_query.loan_num

13

Hi bharat,

The way the tables are joined is most likely causing the performance issue. Try this SQL.

Stewart.

SELECT *

FROM tar.vs_leones.vt_deal_pricing_b a

JOIN

(

SELECT *

FROM src.ocdw.fin_arr_pricing_b b,

src.ocdw.loan_b c,

src.ocdw.time_b d

WHERE

b.fin_arr_id = c.loan_id

AND d.curr_day_ind = ‘Y’

AND b.record_type_cd=‘D’

AND d.calendar_dt BETWEEN b.begin_dt AND b.end_dt

) sub_query

ON a.deal_id = sub_query.loan_num

14

It also would be helpful to know which tables, table-1, table-2, table-3 and table-4 actually are. We may be able to further optimize with this knowledge.

15

Have you published your query?