Re: Help about Sql Delete performance

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.