Multifetch cursor size.

Hi,

I was doing performance tuning for one of the code and implemented the Multi row fetch and Multi row insert in the code.

I did design the cursor size (how many rows to fetch/insert) based on the following criteria.

1) Size of the buffer pool (I've used BP0 -4K Buffer Pool)

2) Size of the row-lengh (80 bytes)

I've decided the number of rows in a cursor should be 45 as

(4k(Size of the bufferpool)/80(Row size) ~ 45).

But we were able to test with the cursor size as 80 also and it worked fine.

I'd like to know if this way of calculation for the row size is correct? Can you please suggest me if any thing else needs to be considered for calculation of the cursor size?.

 

Thanks & Regards,

Vamsi.

You need to be a member of WorldofDb2 to add comments!

Join WorldofDb2

Email me when people reply –

Replies

  • We decided on 35 purely based on application need.    There are some good articles out there from Willie and Susan, which you can take as a reference point to start with. 

  • I would agree with Sreeharsha...don't worry much about calculation...

    i also went sometimes to fetch as much as 1000 rows at times (ofcourse based on data)..

    IBM in general recommends fetching 100 rows at a time..

  • I am not sure about the calculation. If you know that your application retrieves 45 rows, try using this -

    FETCH NEXT ROWSET FROM CUR-01

    FOR 45 ROWS

    I would not be worried about the above calculation that you have posted if you have a method to take care of post fetch activities ( +100 SQL CODE for example )

    Thanks,

    Sree

This reply was deleted.