NOT EXISTS Aint working

[neoartz237]

I have 2 tables
TKYOKUMD01 and TTOKUKMD01.
So Im supposed to get the records that aint found by this query in the TKYOKUMD01 table:
SELECT a.* FROM TKYOKUMD01 a, TTOKUKMD01 b WHERE a.KYOKUSYOCD & a.BUNSITUCD = b.KYOKUSYOCD & b.BUNSITUCD

Easy right? All I have to do is use NOT EXISTS Right? So I did, hence:
SELECT * FROM TKYOKUMD01
WHERE NOT EXISTS (SELECT a.* FROM TKYOKUMD01 a, TTOKUKMD01 b WHERE a.KYOKUSYOCD & a.BUNSITUCD = b.KYOKUSYOCD & b.BUNSITUCD)

No records returned, so maybe all is good and happy in their relationship interconnected world. So just to check I inputted 99999 for KYOKUSYOCD and 9 FOR BUNSITUCD in the TKYOKUMD01, now clearly it should appear right? Coz its there, on the blessed SQL code. BUt the I ran it, BAM, no results? Whaja...juhh...

Please please please help me with this, is there something wrong with this statement coz Im pretty sure I was right

Oh and I have 24,000 records in the TKYOKUMD01 table and 18,000 records on the TTOKUKMD01 table, weird eh? Every one in the 24K table is in the 18K table? Like, what I said, Whaja...juhhh....

Please help

 

[neileg]

Why not use the unmatched records query wizard?

 

[neoartz237]

I did that but it was extremely slow, I coded it first using NOT IN and it took 6 minutes to load! I mean, WOW
And the Unmached thinga a gigger, it took 5-6 minutes to loadf either

 

[neileg]

I think you need to use a left join, which is how the unmatched query works. Basically you're using a left join and returning those records where the right hand table is null. A subquery approach has to be slower than this.

 

[neoartz237]

I did actually, it worked but still extremely slow
my query was
SELECT a.KYOKUSYOCD, a.BUNSITUCD,a.REREKINO
FROM TTOKUKMD01 b LEFT JOIN TYOKUMD01 a
ON a.KYOKUSYOCD & a.BUNSITUCD = b.KANKYOCD
WHERE b.KANKYOCD IS NULL

Correct right? Well it worked but took 5-6 minutes to load up. I have 24K records on TKYOKUMD01 and 18K records in TTOKUKMD01, is Access really that slow?

One of my first thoughts was that comparing with filed using concatenation makes the query slow. So I tried running this

SELECT a.KYOKUSYOCD, a.BUNSITUCD,a.REREKINO
FROM TTOKUKMD01 b LEFT JOIN TYOKUMD01 a
ON a.KYOKUSYOCD = MID(b.KANKYOCD,1,5) AND a.BUNSITUCD = MID(KANKYOCD,5,1)
WHERE b.KANKYOCD IS NULL

But still extremely slow. HOw am I supposed to make it faster? Please, anyone?

 

[WayneRyan]

neo,

The concatenation (a.KYOKUSYOCD & a.BUNSITUCD) isn't quite optimum,
but I think the real problem is lack of indexes.

Are all of the joined fields indexed?

Access can definitely handle that volume of data much faster.

Wayne

 

[WayneRyan]

neo,

Forgot to add:

Using any kind of nested Selects or the "Not In" and "In" type queries will
bring the Access DB engine to its knees.

The Left Join is your choice here.

Wayne

 

[neoartz237]

Unfortunately there are no indexes to beging with. The datas are from Oracle by this Post Office (yuubinkyoku). Even though there are relationships written for it in Oracle, most of it are impossible to execute in Access so I have to do the restrictions manually. W/c I already did thank God. Now, they want me do add an error check function. No biggie, easy to do right? Well, it may be easy but the process it ridiculously slow.

So I ended up using LEFT JOIN, the MID FUNCTION and IN function in one query and the results were ... fine I guess, talk about making you 5 mins f-inf queries to 10 seconds only.

Here's the code: (for what its worth thanks)

SELECT t1.KYOKUSYOCD AS 局所コード, t1.BUNSITUCD AS 分室コード , t1.REREKINO AS 履歴番号, '局所マスタTKYOKUMD01 の局所コード = ' & t1.KYOKUSYOCD & ' と分室コード = ' & t1.BUNSITUCD & ' のペアが、得意先局所対応マスタTTOKUKMD01 の局所コードと分室コードのペアとして存在していません。' AS エラー内容
FROM TKYOKUMD01 AS t1 LEFT JOIN
(SELECT a.KYOKUSYOCD, a.BUNSITUCD
FROM TTOKUKMD01 b LEFT JOIN TKYOKUMD01 a
ON a.KYOKUSYOCD = b.KYOKUSYOCD
AND a.BUNSITUCD = b.BUNSITUCD) as t2
ON t2.KYOKUSYOCD = t1.KYOKUSYOCD AND t2.BUNSITUCD = t1.BUNSITUCD
WHERE t2.KYOKUSYOCD IS NULL AND t2.BUNSITUCD IS NULL

UNION ALL

SELECT t1.KYOKUSYOCD, t1.BUNSITUCD, t1.REREKINO, '局所マスタTKYOKUMD01 の局所コード = ' & t1.KYOKUSYOCD & ' と分室コード = ' & t1.BUNSITUCD & ' のペアが、支払先マスタTSIHARMD01 の買受担当局所コードとして存在していません。'
AS えらー内容
FROM TKYOKUMD01 AS t1 LEFT JOIN
(SELECT a.KYOKUSYOCD, a.BUNSITUCD
FROM TSIHARMD01 b LEFT JOIN TKYOKUMD01 a
ON MID(B.KANKYOCD,1,5) = a.KYOKUSYOCD AND MID(b.KANKYOCD,6,1) = a.BUNSITUCD
WHERE a.KANIKFLG = 0 AND MID(b.SIHARAICD,1,2) = 'D0') AS t2
ON t1.KYOKUSYOCD = t2.KYOKUSYOCD AND t1.BUNSITUCD = t2.BUNSITUCD
WHERE t2.KYOKUSYOCD IS NULL AND t2.BUNSITUCD IS NULL
AND KANIKFLG = 0

UNION ALL

SELECT a.KYOKUSYOCD, a.BUNSITUCD, a.REREKINO, '局所マスタTKYOKUMD01 の請求／取集担当局所コード = ' & a.SEISYUTKFLG & ' と請求／取集担当局分室コード =' & a.SEISYUTBFLG & ' のペアが、局所マスタTKYOKUMD01 の局所コードと分室コードのペアとして存在していません。' AS えらー内容
FROM TKYOKUMD01 b LEFT JOIN TKYOKUMD01 a
ON b.SEISYUTKFLG = a.KYOKUSYOCD AND b.SEISYUTBFLG = a.BUNSITUCD
WHERE b.SEISYUTKFLG IS NULL AND b.SEISYUTBFLG IS NULL

UNION ALL SELECT a.KYOKUSYOCD, a.BUNSITUCD, a.REREKINO, '局所マスタTKYOKUMD01 の買受担当局所コード = ' & a.KAIKYOCD & ' と買受担当局分室コード = ' & a.KAIKYOBUCD & ' のペアが、局所マスタTKYOKUMD01 の局所コードと分室コードのペアとして存在していません。' AS えらー内容
FROM TKYOKUMD01 b LEFT JOIN TKYOKUMD01 a
ON a.KYOKUSYOCD = b.KAIKYOCD AND a.BUNSITUCD = b.KAIKYOBUCD
WHERE b.KAIKYOBUCD <> '' AND b.KAIKYOCD <> ''
AND b.KAIKYOBUCD IS NULL AND b.KAIKYOCD IS NULL;

UNION ALL

SELECT SIHARAICD AS 支払先コー, REREKINO AS 履歴番号, '支払先マスタTSIHARMD01 の 先頭が [D0] の支払先コード = ' & SIHARAICD & ' の３～８桁目が、局所マスタTKYOKUMD01 の局所コードと分室コードのペアとして存在していません。' AS エラー内容
FROM TSIHARMD01
WHERE SIHARAICD NOT IN (SELECT a.SIHARAICD
FROM TKYOKUMD01 b LEFT JOIN TSIHARMD01 a
ON MID(a.SIHARAICD,3,5) = b.KYOKUSYOCD
WHERE MID(a.SIHARAICD,1,2) = 'D0')
AND MID(SIHARAICD,1,2) = 'D0'

UNION ALL

SELECT SIHARAICD, REREKINO, '支払先マスタTSIHARMD01　の　簡易局コード = ' & KANKYOCD & ' が、局所マスタTKYOKUMD01　に　局所コード　と　分室コード　のペアとして、もしくは、簡易局として存在していません。'
FROM TSIHARMD01
WHERE SIHARAICD NOT IN
(SELECT a.SIHARAICD
FROM TKYOKUMD01 b LEFT JOIN TSIHARMD01 a
ON MID(a.KANKYOCD,1,5) = b.KYOKUSYOCD
AND MID(a.KANKYOCD,6,1) = b.BUNSITUCD
WHERE b.KANIKFLG = 0 AND MID(a.SIHARAICD,1,2) = 'D0')
AND MID(SIHARAICD,1,2) = 'D0'

UNION ALL

SELECT SIHARAICD, REREKINO, '支払先マスタTSIHARMD01 の先頭が [D0] の支払先コード = ' & SIHARAICD & ' の３～８桁目が、支払先マスタTSIHARMD01 の簡易局コードとして存在していません。'
FROM TSIHARMD01
WHERE MID(SIHARAICD,3,6) <> KANKYOCD
AND KANKYOCD <> NULL
AND KANKYOCD <> ''
AND MID(SIHARAICD,1,2) = 'D0'

UNION ALL SELECT SIHARAICD, REREKINO, '支払先マスタTSIHARMD01 の先頭が [D9] の支払先コード = ' & SIHARAICD & ' の３～10桁目が、得意先マスタTTOKUIMD01 の先頭が [B9] の得意先コード ３～10桁目として存在していません。'
FROM TSIHARMD01
WHERE SIHARAICD NOT IN
(SELECT a.SIHARAICD
FROM TTOKUIMD01 b LEFT JOIN TSIHARMD01 a
ON MID(a.SIHARAICD,3,8) = MID(b.TOKUISAKICD,3,8)
WHERE MID(a.SIHARAICD,1,2) = 'D9'
AND MID(b.TOKUISAKICD,1,2) = 'B9')
AND MID(SIHARAICD,1,2) = 'D9';

UNION ALL

SELECT a.TOKUISAKICD, a.REREKINO, '得意先マスタTTOKUIMD01 の得意先コード = ' & a.TOKUISAKICD & ' が、得意先局所対応マスタTTOKUKMD01 の得意先コードとして存在していません。' AS エラー内容
FROM TTOKUIMD01 a LEFT JOIN TTOKUKMD01 b
ON a.TOKUISAKICD = b.TOKUISAKICD
WHERE MID(a.TOKUISAKICD,1,2) = 'B9'
AND b.TOKUISAKICD IS NULL

UNION ALL

SELECT a.TOKUISAKICD, a.REREKINO, '得意先マスタTTOKUIMD01 の得意先コード = ' & a.TOKUISAKICD & ' が、取扱科目マスタTTORIKMD01 の得意先コードとして存在していません。'
FROM TTOKUIMD01 a LEFT JOIN TTORIKMD01 b
ON a.TOKUISAKICD = b.TOKUISITENCD
WHERE MID(a.TOKUISAKICD,1,2) = 'B9'
AND b.TOKUISITENCD IS NULL

UNION ALL SELECT tbl1.TOKUISAKICD, tbl1.REREKINO,'得意先マスタTTOKUIMD01 の先頭が [B9] の得意先コード = ' & tbl1.TOKUISAKICD & ' の３～１０桁が、支払先マスタTSIHARMD01 の先頭が [D9] の支払先コードの３～１０桁として存在していません。'
FROM TTOKUIMD01 tbl1 LEFT JOIN
(SELECT a.TOKUISAKICD, a.REREKINO
FROM TTOKUIMD01 a LEFT JOIN TSIHARMD01 b
ON MID(a.TOKUISAKICD,3,6) = MID(b.SIHARAICD,3,6)
WHERE MID(a.TOKUISAKICD,1,2) = 'B9'
AND MID(b.SIHARAICD,1,2) = 'D9') as tbl2
ON tbl1.TOKUISAKICD = tbl2.TOKUISAKICD AND tbl1.REREKINO = tbl2.REREKINO
WHERE tbl2.TOKUISAKICD IS NULL
AND tbl2.REREKINO IS NULL
AND MID(tbl1.TOKUISAKICD,1,2) = 'B9';


Suprisingly, this code's longer yet, faster to load. Weird