RE: Help about Sql Delete performance

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.