Re: Need some help with SQL case/when performance

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Are you doing an
  insert into ... (select .. from)

Or are you doing
  update ...

What's the RPG program doing?

I'm highly suspicious of the idea that SQL case statement of the form
case when vendor = ' ' then ' ' else '0' || vendor end

takes 12hrs and the RPG equivalent takes only 20min.

Is the file being written to journaled by chance?
Are you (unintentially?) running the SQL under commitment control?
68M records in a single transaction is going to be _very_ slow...

For the case of a mass update with either SQL or RPG, I'd make sure the
file being written to is _NOT_ journaled.
Unless you happen to have 5117-HA Journal Performance installed and turned
on.

Charles


On Thu, Feb 25, 2021 at 6:49 AM <smith5646midrange@xxxxxxxxx> wrote:

> We are converting files and I have a BIG performance problem.
>
>
>
> The files are being converted via CHGPF.  Some of the fields contain
> numeric
> values stored in alpha fields.  The client does not like to use null
> capable
> fields so I think they did this to identify "no value".  I also know this
> file is pretty old so null might not have been widely used back when it was
> created.
>
>
>
> For an example of the data.our 5 digit vendor is stored in a 5A field.
> After the CHGPF, it is a 6A field so the vendor is stored as "12345 ".  I
> am
> running an SQL that inserts a 0 in front so it will be "012345".  However,
> not all vendors have a value.  Some are blank and we don't want to create
> the value "0     ".
>
>
>
> I used the logic (forgive the syntax if it is wrong)
>
>
>
> Vendor = case when vendor = ' ' then ' ' else '0' || vendor end
>
>
>
> The problem that I am having is the performance on this seems horrible.  We
> have a file of 68M records and it has 5 of these types of fields.
> Therefore
> the case/when exists 5 times in the update.  The SQL update ran for about
> 12
> hours.  When I created an RPGLE with normal F Specs and reads and writes,
> it
> ran in 20 minutes.  I have run this several times on several different
> machines and the performance seems similar on all runs.
>
>
>
> The dilemma with this is the values in the records vary.  Of the 5 fields,
> how many of them in each record will be populated and which ones will vary.
> So, my options as I saw them were either using the case/when or running 5
> different SQL updates each updating a single field where a clause selecting
> that field running over 68M rows each time.  At the time, the case/when
> seemed like the better option.
>
>
>
> Does anyone have any thoughts on what might be going on that would cause
> such an extreme difference between the SQL case/when and native RPGLE?
> Does
> anyone have a different idea how to approach this using SQL?  I can go back
> to the RPG logic but it seems "old school".
>
>
>
> This request is strictly for ideas on conversion.  I can't change the field
> types or allow null or change the programs that are using them.
>
>
>
> Thanks in advance.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com