1.  Home
  2. MIDRANGE-L
  3. January 2025

Re: Help about Sql Delete performance

have you tried when " we remove logical files, triggers and journal."
leaving one logical on each table where 1st key column = LGIDL1 or L1ID..

Jim

On Sun, Jan 26, 2025 at 7:44 AM Marco Facchinetti <
marco.facchinetti@xxxxxxxxx> wrote:

Hi Jim, I understand your points and, usully, I agree about indexes but
this case is diffrent.

The scope of delete is to remove an high percentage of records (sometimes
80/90%). MULTTT400F: 74M rows down to 36M.

So, before deleting, we remove logical files, triggers and journal.
Otherwise performance are terrible.

Best regards
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno dom 26 gen 2025 alle ore 05:30 Jim Franz <franz9000@xxxxxxxxx>
ha
scritto:

(a late observation) I was surprised (or missed it) that no one suggested
building an index over the L1ID column in TSEDAT4/MULTTT100F... or
index
over the column in both tables (if you've not already tried it) - DB2 can
be very efficient doing all the work between the indexes.
There was mention of looking at visual explain, am interested if a db2
temp
index (or temp indexes) was built (time building the index and then time
processing deletes).
Relying on temp or perm indexes will depend upon how your application
using these files, and how often this needs to execute.
I see 38 million rows in MULTTT400F, how many rows in MULTTT100F?
Jim Franz


On Sat, Jan 25, 2025 at 1:27 PM Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx>
wrote:

This seems like one of those minimal use cases where a cycle RPG
program
fits the requirement.

If blocking is not 1:1, then it should be somewhat faster.


Jim Oberholtzer
Agile Technology Architects

On Jan 25, 2025, at 12:08 PM, Marco Facchinetti <
marco.facchinetti@xxxxxxxxx> wrote:

Thanks to Birgitta, Justin, Paul and Charles.

Using a cursor to read the Id and issue a Delete for each record
takes
forever so I didn't finish the test.

I tested 4 diffrent solutions:

267 Sec

Exec sql Truncate WIDX;

Exec Sql Insert into WIDX
SELECT LGID FROM MULTTT400F
WHERE LGIDL1 <> ' ' AND
LGIDL1 NOT IN(Select L1ID FROM MULTTT100F);

Exec Sql DELETE FROM MULTTT400F WHERE LGID IN(
SELECT LGID FROM WIDX) WITH NC;

181 Sec

exec sql DELETE FROM MULTTT400F WHERE LGIDL1 <> ' ' AND
LGIDL1 NOT IN(Select L1ID FROM MULTTT100F) WITH NC;

183 Sec

Txt = 'DELETE FROM MULTTT400F WHERE LGIDL1 <> '' '' AND '
+ 'LGIDL1 NOT IN(Select L1ID FROM MULTTT100F GROUP BY L1ID'
+ ' ORDER BY L1ID) WITH NC';

Exec Sql Execute Immediate :Txt;


336 Sec

Exec Sql DELETE FROM MULTTT400F WHERE LGID IN(
SELECT LGID FROM MULTTT400F
WHERE LGIDL1 <> ' ' AND
LGIDL1 NOT IN(Select L1ID FROM MULTTT100F))
WITH NC;

Best regards
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno sab 25 gen 2025 alle ore 12:28 Birgitta Hauser <
Hauser@xxxxxxxxxxxxxxx> ha scritto:

Just curious:
How fast is it if you read the unique keys of the rows to be deleted
using
a cursor and then deleting one row after the other by secifying the
unique
keys (not with WHERE CURRENT OF)?

Also IMHO the query optimizer cannot use an index (due to the <> and
the
NOT IN operator).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not
training
them
and keeping them!"
"Train people well enough so they can leave, treat them well enough
so
they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!"
(Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf
Of
Marco Facchinetti
Sent: Friday, 24 January 2025 22:22
To: Midrange Systems Technical Discussion <
midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help about Sql Delete performance

It's *NONE
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno ven 24 gen 2025 alle ore 22:05 Charles Wilt <
charles.wilt@xxxxxxxxx> ha scritto:

Do you have FRCRATIO(1) set on the table?

Charles

On Fri, Jan 24, 2025 at 4:59 AM Marco Facchinetti <
marco.facchinetti@xxxxxxxxx> wrote:

Hi we are performing this Sql over a table without triggers, LF
and
journals:

DELETE FROM TSEDAT4/MULTTT400F WHERE LGIDL1 <> ' ' AND LGIDL1 NOT
IN(Select
L1ID FROM TSEDAT4/MULTTT100F)

The statement runs but I see in Wrkactjob opt. 14 that I/O and RRN
almost the same numbers as if each record generate a single I/O.
Since it's an
Sql
statement and not RPG files access I was expecting blocked I/O
operations and thus better performances.

Get Diagnostic resturns:

COMMAND_FUNCTION_CODE=+43
COMMAND_FUNCTION=EXECUTE IMMEDIATE
CONDITION_NUMBER=+1
DB2_MESSAGE_ID=SQL7955
DB2_ORDINAL_TOKEN_1=MULTTT400F
DB2_ORDINAL_TOKEN_2=TSEDAT4
DB2_ORDINAL_TOKEN_3=38952036
DB2_ORDINAL_TOKEN_4=0
DB2_SQL_ATTR_CONCURRENCY=L
DB2_SQL_ATTR_CURSOR_CAPABILITY=D
DB2_SQL_ATTR_CURSOR_HOLD=N
DB2_SQL_ATTR_CURSOR_ROWSET=N
DB2_SQL_ATTR_CURSOR_SCROLLABLE=N
DB2_SQL_ATTR_CURSOR_SENSITIVITY=S
DB2_SQL_ATTR_CURSOR_TYPE=F
DB2_SQLERRD_SET=Y
DB2_SQLERRD3=+38952036
DB2_SQLERRD6=-134613515
DB2_TOKEN_COUNT=+4
DB2_TOKEN_STRING=MULTTT400F TSEDAT4 38952036 0
DYNAMIC_FUNCTION_CODE=+19
DYNAMIC_FUNCTION=DELETE WHERE
MESSAGE_TEXT=38952036 righe rilevate da MULTTT400F in TSEDAT4.
MORE=N
NUMBER=+1
RETURNED_SQLSTATE=00000
ROW_COUNT=+38952036

Any hint?

TIA
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco
--
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.


--
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.


--
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.


--
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.


--
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.

--
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.


--
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.


--
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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.