what can be the causes that an UPDATE have a high runtime?

[Former Member]

Hi,

i'm checking slow performance on my system and get a strange command, an UPDATE that uses a PK_EQ strategy, but have a runtime of 38 seconds, follow is the result of the diagnose tables:

SYSK;ROWS_READ;ROWS_QUAL;STRATEGY;RUNTIME;PHYSICAL_IO;SQL_STATEMENT;
000000000000000B;2;2;PK_EQ;38.336711;0;UPDATE "PRODUTO" SET "ABREVIACAO"=?,"DESCRICAO"=?,"UNID_MEDIDA"=?,"EMBALAGEM"=?,"QTDE_EMBALAGEM"=?,"PESO"=?,"PRC_FABRICA"=?,"PRC_SUGERIDO"=?,"PRC_GARANTIA"=?,"ALIQ_IPI"=?,"DATA_CADASTRO"=?,"DATA_ALTERACAO"=?,"STATUS"=?,"SUBCLASS"=?,"MY_FABRICANTE"=?,"MY_GRUPO_TRIBUTARIO"=?,"MY_EMPRESA"=? WHERE "OID"=? AND "MY_FABRICANTE" IS NULL AND "MY_GRUPO_TRIBUTARIO"=? AND "MY_EMPRESA"=?

exists a way to discover why this is too slow? an update of this type can be run in less than 1 second, but in this case is 38 times slow.

any path to follow?

best regards

Clóvis

[lbreddemann · Accepted solution]

> Hi,

Hi again - looks like you're up to become the no. 1 active MaxDB user outside the SAP world... great!

> i'm checking slow performance on my system and get a strange command, an UPDATE that uses a PK_EQ strategy, but have a runtime of 38 seconds, follow is the result of the diagnose tables:

>

> exists a way to discover why this is too slow? an update of this type can be run in less than 1 second, but in this case is 38 times slow.

>

> any path to follow?

Hmm... there could be several factors in here.

Let's begin with the easiest thing: checking the access strategy.

Please turn the UPDATE statement into a SELECT statement and run an 'explain' on it:

explain
select * 
from PRODUTO 
where
 OID= <put in the value here>
 AND MY_FABRICANTE IS NULL 
AND MY_GRUPO_TRIBUTARIO= <put in the value here>

The execution plan might be useful - anyhow, since there had been no physical I/O for this UPDATE and only 2 rows were read to update 2 rows , I tend to believe that the access strategy is not the issue here.

I assume that either this measurement is wrong (quite possible - there's a similar bug for DELETEs known, e.g. PTS 1174270) OR that this session had to wait for something.

This could have been row/table-locks or CPU time or the option to write into the log queue.

Since in one of the other threads of you there was a LOG FULL issue, I would guess the last option is a good guess here.

If I may give you a hint concerning performance tuning: try to integrate time measurement into your client application as well.

Check the response times of the database queries from your application point of view.

If you find anything below your target line there - analyze it in more depth.

"Hunting" for badly performing statements in the database is usually just a waste of time, since you can never tell whether the statement you found there is of any importance to your business.

Try to tune the important ones until these are as good as they should be.

Although more based on Oracle databases, I highly recommend the book and the papers of Cary Millsap if you want to learn more about successfull system performance management.

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

Maybe i'm "the last of mohican", i use SapDB since version 7.3, when SAP says that SapDB will be always open and free, and is the only one i used in production, for years, and i hope to continue using. Until SAP cut my fingers, until now SAP just cut the "open" part, i hope that the free part stay alive, (i really tried to found a way to pay for MaxDB, to achieve some support, but cant find anything)

well i cried enough,

here is the explain plan you asked about:

OWNER;TABLENAME;COLUMN_OR_INDEX;STRATEGY;PAGECOUNT;
VENDOR_CONCES;PRODUTO;;EQUAL CONDITION FOR KEY;      4193
;;OID;     (USED KEY COLUMN);
;JDBC_CURSOR_8;;   RESULT IS NOT COPIED , COSTVALUE IS;         1
;JDBC_CURSOR_8;;QUERYREWRITE - APPLIED RULES:;
;JDBC_CURSOR_8;;   DistinctPullUp;         1

i think you are right, solving my thread stuck problem, can solve this problem too, but this one is a try to solve that thread stuck problem, i'm getting in an inner loop here, my try is that i believe that many slow commands can block the database and causes that stuck threads.

about time measurement i already have that, and all important things works very fine, if i never get those stuck threads, maybe i never appears in a forum, , since with SapDB i never got problems. (admin free)

i will follow your tip to read the book and the papers of Cary Millsap, its time to become a DBA on MaxDB, (here we loose the admin free feature, )

best regards.

Clóvis