Re: SQL Optimizer - just wondering

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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