Re: SQL Optimizer - just wondering

Gad

You might not need to guess - if you got into the Database component of Navigator (full client), or look at jobs and find the "SQL statements" section - then right-click a statement and select a Visual Explain option - that should tell you what went on - exactly - you''ll see all the steps.

I am sorry to be so vague as to which option to take in Navigator - I usually end up trying different ones before I get where I want to be. Probably because I don't do this all that often these days.

HTH
Vern

On 3/12/2016 3:32 AM, Gad Miron wrote:

Hello guys

An SQL question, I hope this is the right form

adding a field (NMDSTTYP) to a 50M records DWH file I pondered what is a
better way to populate this new field:

By a RPG-Chain-like way using a subselct
update DWHD.RTDACTBF_R a
set NMDSTTYP = (select DSTTYP from NMLIBD.NMCOMPNYF b
where b.COMPNY = a.COMPNY)

Or

By doing a MERG with a When Matched clause
(imitating MS SQL Server Update from) :
MERGE INTO DWHD.RTDACTBF_R a
USING NMLIBD.NMCOMPNYF b on b.COMPNY = a.COMPNY
WHEN MATCHED THEN
UPDATE SET a.NMDSTTYP =b.DSTTYP

I figured the 2nd way is better as it should do a one time join
compared to the 1st way that does (well, I don't quite know what it does)

both SQL statements were submitted to batch using SBMJOB RUNSQL with
COMMIT(*NONE)

The 1st RPG-Chain-like Update took 10 minutes
The 2nd MERG Update took 4 hours

anybody care to explain?

Thanks
Gad