Insert statement performance

[Former Member]

Hi,

I am facing a performance problem in insert stament . the statement is inserting aroung 200000 records in ztable from internal table and it takes more than an hour . Is there any way to optimize this insert statement . ?

Thanks

Madhumati

[RaymondGiuseppi]

• How did you analyze performance, insure the performance problem is related to SQL insertions.
• Upgrade the database server...
• Drop secondary indexes before insert and rebuild them afterwards.
• Parallelism may or not improve performance, depend on database (perform some tests, more process may improve performance but lock management may, and will, hinder performance.)

Regards,

Raymond

[Former Member]

none of these options are viable .. for indexses- ther isnt any index in table . its a simple ztable with 7-8 columns.

is there any way to use native sql ?

[ThomasZloch]

Might be problem with redo buffers filling up (not sure about the exact term, varies by database), all DB changes are recorded for potential rollback until you do a COMMIT WORK. So try inserting blocks of e.g. 10K records at a time, do a COMMIT WORK (or call function DB_COMMIT for a database commit only), process the next block etc., and see if this improves things.

Thomas

[yuri_ziryukin]

Well, this was the last post in the thread that made sense.

Good point, Thomas.

Inserting data in packages and committing is a right thing to do.

[Former Member]

Hi Try to use TABLE INDEX. It might do the trick.

Thank you

Vignesh

[volker_borowski2]

Far too less information for a competent answer!

Adding indexes to optimize an INSERT from ITAB ... will not help at all, for no database type.

It will do things worse, because more actions have to be done on inserts.

Packaging and increasing the commit ratio (sorry, I hate to contradict Yuri and Thomas)

will only help for inserts, if you have a concurrent change activity on this table.

If you are processing alone on that table, going for ALL-or-NOTHING is better

for the performance and it might be easier in terms of transaction logic,

because, if you did commit 50 packs but get an error for pack 51,

how do you roll back the 50 committed ones?

An insert is pretty simple. An INSERT form ITAB is even better, because

it is implemented as an array insert, if the underlying DB permits this type of operation.

Now 200k rows in one hour seems extremly slow to me.

How much is that in MB when you did insert this stuff?

Without knowing any more details, I'd assume:

- lousy/defective hardware

- high change concurrency on the table affected (locks involved)

- strange datatypes involved (long raw? LOBs?)

- slow network, if this is running on an application server remote to the DB

- extrem high number of index keys per leaf block

What DB type are you using?

If Oracle what release and in what type of tablespace is your table stored.

As for indexes (2): Every SAP table has at least a primary key (index 0).

If this is oracle, check how many rows the table has and how many blocks the index 0 has.

if you have a significant number of index keys per index block, it might help

to do a rebuild of the PK while adjusting the INITRANS parameter.

It defaults to 2 on SAP-Oracle DBs,

Setting it to 20 might help, but will cost some space (I think it was 24 bytes per slot).

If this is Oracle, watch your statement in transaction ST04/ST04old

and check out if there is a specific wait event listed.

if you find "ITL waits" go for the INITRANS rebuild mentioned above

if you find log waits / logfile switch waits / ... increase redolog size and/or log_buffer

...

Try these for startes, but I could not guarantee for anything based on the data given.

Time to talk to your DBA I think

Volker

[yuri_ziryukin]

Volker Borowski wrote:
Packaging and increasing the commit ratio (sorry, I hate to contradict Yuri and Thomas)
will only help for inserts, if you have a concurrent change activity on this table.
If you are processing alone on that table, going for ALL-or-NOTHING is better 
for the performance and it might be easier in terms of transaction logic, 
because, if you did commit 50 packs but get an error for pack 51, 
how do you roll back the 50 committed ones? 

Hello Volker,

your comment was so interesting that I decided to make some tests out of it.

Here are my results.

The DB table contains 2 fields:

- GUID (RAW16) -> primary key

- DESCR (CHAR40)

Only primary key exists for the table.

After each iteration I have deleted and recreated the table using DB utility.

Source code:

REPORT  ZYZ_TEST.
parameters: p_type type boolean.
types: begin of ts_test,
         guid type guid,
         descr type char40,
       end of ts_test.
data: ls_test type ts_test,
      lt_test type standard table of ts_test,
      lt_test2 type standard table of ts_test,
      lv_start TYPE i,
      lv_stop TYPE i,
      lv_diff type i.
if p_type = 'X'.
  do 10000000 times.
    CALL FUNCTION 'GUID_CREATE'
      IMPORTING
        EV_GUID_16       = ls_test-guid
*     EV_GUID_22       =
*     EV_GUID_32       =
              .
    ls_test-descr = ls_test-guid.
    append ls_test to lt_test.
  enddo.
  GET RUN TIME FIELD lv_start.
  insert zyz_test from table lt_test.
  commit work.
  GET RUN TIME FIELD lv_stop.
  lv_stop = lv_stop - lv_start.
  write: 'Duration = ', lv_stop.
else.
  do 200 times.
    do 50000 times.
      CALL FUNCTION 'GUID_CREATE'
        IMPORTING
          EV_GUID_16       = ls_test-guid
*       EV_GUID_22       =
*       EV_GUID_32       =
                .
      ls_test-descr = ls_test-guid.
      append ls_test to lt_test.
    enddo.
    GET RUN TIME FIELD lv_start.
    insert zyz_test from table lt_test.
    commit work.
    GET RUN TIME FIELD lv_stop.
    lv_stop = lv_stop - lv_start.
    lv_diff = lv_diff + lv_stop.
    refresh lt_test.
  enddo.
  write: 'Duration = ', lv_diff.
endif.

1st run: 200.000 records.

Inserting all together + 1 commit runtimes:

2072 ms

2331 ms

2549 ms

2301 ms

Inserting by packages of 50.000 (4 in total) + 4 commits:

2660 ms

2860 ms

2790 ms

2409 ms

Here we can see that 1 commit and large insert is slightly faster.

2nd run: with 2.000.000 records.

Inserting all together + 1 commit:

40423 ms

32744 ms

Inserting by packages of 50.000 (40 in total) + 40 commits:

43127 ms

35500 ms

Still one large insert is faster.

Then I decided to increase the volume to 10.000.000 records.

Inserting all together + 1 commit:

470.520 ms

386.099 ms

Inserting by packages of 50.000 (200 in total) + 200 commits:

389.897 ms

401.416 ms

Here the results are varying, it's difficult to tell that one large insert is always faster.

I also remember ORA-1555 issue. Long running transactions without COMMIT might cause ORA-1555 if the rollback segment is overwritten...

For the transactional correctness issue, if previous packages should be rolled back, - it depends on the program logic. I can hardly imagine normal transaction that requires inserting so much data. Normally this happens for some kind of initial load or mass processing. And there, it's even better if you do not start from scratch and can continue after fixing your error.

Cheers,

  Yuri

[volker_borowski2]

Hi Yuri,

some interesting results, though I like to direct attention to some aspects:

GUIDs: Guids have been developed to support massive parallel inserts.

Since the "flickering" bits are somewhere in the middle of the field. Looking at

a B-Tree GUID index, an insert will hit the B-tree at very distinct places with each new record,

thus avoiding many transaction "fighting" for the "right" end of the B-Tree.

This is perfect when 50 jobs are trying to insert at the same time, but is

a bit harder when in a single transaction. The Index will utilize more space in the end

because during the insert the leafblocks will be mostly processed be with 50:50 block splits.

With a sequence oracle should detect "right end inserts" for the PK and thus

doing 90:10 splits for the index blocks. This should use less index space and

therefore less undo resources.

Might be irrelevant for low volume transactions, but I like to see how this works

in your big testcase with a normal ascending integer key instead of a guid.

I'd expect both cases to be quicker, and the relation between both shrinking.

Second: Your are using a very short row length. And a very short key. This means, you are

utilizing the space of the table and the index very effectively in terms of a "per block" ratio.

I think you will get more overhead trouble as longer keys and average row length are,

because you are hitting space- and index-management more often then.

Since your rows are so short, I assume your first two examples get away without

any noticeable log-space problems, while the big one might face waits for log information

to be written. I'd like to know with how many blocks you end up for data and index.

Would be a curious thing to see what happens when the average row length goes to ~ 500 byte.

For the "big" testcase, it might be relevant how your logfiles are filled and how big these are.

So you should do a logswitch directly before you start the measurement, because if one of

the candidates would draw a "checkpoint incomplete" wait due to an unfortunate log start point

that would be unfair

The 1555 is indeed a commit related error, but it is rare, that a single modifying transaction

gets it, esp. with new undo managemnt in Oracle.

It always requires concurrency to occur.

Does not seem to be the case here.

And yes, if this is an initial load, or the data contains an identifier that allows to

identify what data needs to be taken back (a pack id, a date, whatever), no problem.

But one should be sure about how to work on this.

Volker

[Former Member]

changing the records to multiple inserts has worked and the performance is much better now.