yea, but my peer is always busy as he has more responsibility. I try not to disturb him too much if possible.
Can i ask how to optimize the query. I just feel dizzy running the query, it takes decades to get my report. I'm still running the query :X when i'm suppose to finish by today late.
I got 70K records on table <tbl_BID> and it went timeout after 2 hours very sad.
INNER JOIN tbl_BID tbd --- changed to Inner Join
ON a.user_id = tbd.userid -- updated
SELECT DISTINCT a.user_id EID,
a.other_name otherName1,
c.country1 Citizenship,
c1.country2 rCountry,
c2.country2 import_country,
D1.desc1name Description_1,
D2.desc2name Description_2,
D3.desc3name Description_3,
e.c_type C_TYPE,
e.cat1 CAT1,
e.c_title AS C_TITLE,
e.since_date AS Occupation_SINCE_Date,
e.to_date AS Occupation_TO_Date,
List.lname list_reference,
a.is_active inactive1,
a.dec DEC,
a.last_updated_date rDate,
f.NAME rel,
pu.eid r_EID,
pu.other_name name1,
pu.country1 Citizenship1,
pu.rcountry Residence1,
pu.import_country r_import_country,
pu.desc1name RD_1,
pu.desc2name RD_2,
pu.desc3name RD_3,
pu.c_type C_TYPE,
pu.cat1 CAT1,
pu.c_title AS C_TITLE,
pu.since_date AS Occupation_SINCE_Date,
pu.to_date AS Occupation_TO_Date,
RList.r_ref r_ref,
pu.is_active inactive1,
pu.dec dec1,
pu.last_updated_date RAC_last_review_date
FROM tbl1.tbdata a
LEFT OUTER JOIN tbl1.tbccd c
ON a.row_id = c.row_id
AND c.country_type = 'Citizen'
AND a.bdate = c.bdate
LEFT OUTER JOIN tbl1.tbcd C1
ON a.row_id = c1.row_id
AND c1.country_type = 'Res'
AND a.bdate = c1.bdate
LEFT OUTER JOIN tbl1.tbcd C2
ON a.row_id = c2.row_id
AND c2.country_type = 'JC'
AND a.bdate = c2.bdate
INNER JOIN tbl1.tbdd D1
ON a.row_id = d1.row_id
AND a.bdate = D1.bdate
LEFT OUTER JOIN tbl1.tbdd2 D2
ON a.row_id = d2.row_id
AND a.bdate = D2.bdate
LEFT OUTER JOIN tbl1.tbdd3 D3
ON a.row_id = d3.row_id
AND a.bdate = D3.bdate
LEFT OUTER JOIN tbl1.tbrd e
ON a.row_id = e.row_id
AND a.bdate = e.bdate
LEFT OUTER JOIN tbl1.tbl_ad fa
ON a.user_id = fa.user_id
AND a.row_id = fa.row_id
LEFT OUTER JOIN tbl1.tbl_lr f
ON fa.code = f.code
LEFT OUTER JOIN tbl1.tblr r
ON a.row_id = r.row_id
LEFT OUTER JOIN (SELECT ld.id,
ld.lname
FROM tbl1.ld ld
WHERE ld.bdate = '9999-12-31'
AND Trim(ld.lname) NOT IN (
'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP',
'TS - KR', 'NA Local' )) List
ON r.tbl_ldi = List.id
LEFT OUTER JOIN (SELECT DISTINCT a.user_id EID,
a.other_name,
c.country1,
c1.country2 rCountry,
c2.country2 import_country,
D1.desc1name,
D2.desc2name,
D3.desc3name,
e.c_type C_TYPE,
e.cat1,
e.c_title,
e.since_date,
e.to_date,
a.is_active,
a.dec,
a.last_updated_date,
r.tbl_ldi LDI
FROM tbl1.tbdata a
LEFT OUTER JOIN tbl1.tbccd c
ON a.row_id = c.row_id
AND
c.country_type = 'Citizenship'
AND a.bdate = c.bdate
LEFT OUTER JOIN tbl1.tbcd C1
ON a.row_id = c1.row_id
AND c1.country_type = 'Residence'
AND a.bdate = c1.bdate
LEFT OUTER JOIN tbl1.tbcd C2
ON a.row_id = c2.row_id
AND c2.country_type = 'JC'
AND a.bdate = c2.bdate
LEFT OUTER JOIN tbl1.tbdd D1
ON a.row_id = d1.row_id
AND a.bdate = D1.bdate
LEFT OUTER JOIN tbl1.tbdd2 D2
ON a.row_id = d2.row_id
AND a.bdate = D2.bdate
LEFT OUTER JOIN tbl1.tbdd3 D3
ON a.row_id = d3.row_id
AND a.bdate = D3.bdate
LEFT OUTER JOIN tbl1.tbrd e
ON a.row_id = e.row_id
AND a.bdate = e.bdate
LEFT OUTER JOIN tbl1.tblr r
ON a.row_id = r.row_id
WHERE 1 = 1
AND a.row_id IN (SELECT Max(x.row_id) row_id
FROM tbl1.tbdata x
WHERE x.user_id = a.user_id))
pu
ON fa.aid = pu.eid
LEFT OUTER JOIN (SELECT ld.id,
ld.lname r_ref
FROM tbl1.ld ld
WHERE ld.bdate = '9999-12-31'
AND Trim(ld.lname) NOT IN (
'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP',
'TS - KR', 'NA Local' )) RList
ON pu.ldi = RList.id
INNER JOIN tbl_BID tbd --- changed to Inner Join
ON a.user_id = tbd.userid -- updated
WHERE 1 = 1
AND a.status = ''
AND a.row_id IN (SELECT Max(x.row_id) row_id
FROM tbl1.tbdata x
WHERE x.user_id = a.user_id)
ORDER BY 1;