Hi all,
Any ideas on tuning the QlikView SAP SQL Connector to increase its speed.
We have been testing a load of 7.5 million records from a single table and it has taken 7 hrs, 22 mins, which is too slow for our needs.
Have tried the following settings on our sap system and used transaction ST02 to check that the import/export buffers have not been overloaded etc.
_____________________________________________________________________________
Initial record buffer IRBD
rsdb/ntab/irbdsize 6000 kB Size of initial record buffer
Data area size for the "Initial record buffer" to store the initial record layout of an R/3 table that
is accessed at runtime.
Change to 12000
rsdb/ntab/entrycount 20000 Max. number / 4 of initial records buffered
Number of directory entries created for the "table definition buffer" and
the "field description buffer". The default appears to be 3.33 * 12000 (value of irbdsize) = 39960
Change to 40000
_____________________________________________________________________________
Program buffer PXA
CUA buffer CUA
rsdb/cua/buffersize 3000 kB Size of CUA buffer
The CUA buffer holds objects from the GUI interface, such as menus, pushbuttons, and so on.
In the data base, these objects are held in tables D342L and D345T.
Recommended settings for large Systems: 3000 - 6000 KB.
change to 6000
affects ipc/shm_psize_40 as the CUA buffer is assigned to an SAP shared memory pool.
Run sappfpar check pf=\\usr\sap\trans\tp_domain_<sid> to check settings
_____________________________________________________________________________
Generic key table buffer TABL
zcsa/table_buffer_area 30000000 Byte Size of generic key table buffer
This buffer holds entries from tables which have been defined in the Data Dictionary with buffer
type X (buffer all entries that belong to active clients) and G (buffer entries according to a generic
key specification).
Change to 60000000 Bytes
Affects ipc/shm_psize_10: In standard systems, the resident-table buffer is stored in shared
memory pool 10. If you change the size of the buffer, then you must change the amount of memory
in pool 10 by the same amount. You can remove the buffer from the pool by setting the
parameter ipc/shm_psize_19=0.
Removing the buffer from pool 10 simplifies your profile management. The buffer is stored in its own
shared memory segment, which is allocated automatically when the server is started. You do not have
to change ipc/shm_psize_10 when you change the buffer size.
Run sappfpar check pf=\\usr\sap\trans\tp_domain_<sid> to check settings
zcsa/db_max_buftab 5000 Max. number of buffered objects
Specifies the number of directory entries in the resident-table buffer. This is the maximum number of
tables that can be buffered. Set the parameter to a large enough value to ensure that there are always
free directory entries.
No Changes needed.
______________________________________________________________________________________
Single record table buffer TABLP
rtbb/buffer_length 10000 kB Size of single record table buffer
Specifies the size of the partial table buffer. The partial table buffer holds individual records from tables
that are defined in the data dictionary with buffer type P.
Effects upon other parameters: rtbb/max_tables. The efficiency of the partial table buffer depends not only
upon its size but also upon the number of directory entries in the buffer (the maximum number of tables that
can be buffered). You can specify the number of directory entries
in profile parameter rtbb/max_tables.
Change to 20000 KB
rtbb/max_tables 500 Max. number of buffered tables
Specifies the number of directory entries in the partial table buffer. This is the maximum number of tables that
can be buffered. Set the parameter to a large enough value to ensure that there are always free directory entries.
Change to 1000 KB
_____________________________________________________________________________
Export/import buffer EIBUF
rsdb/obj/buffersize 40000 kb Size of export/import buffer [kB]
This parameter sets the size of the import /export buffer (KB). The buffer is used for data storage
in the shared memory and is accessed using the ABAP commands EXPORT TO/IMPORT FROM
SHARED BUFFER and DELETE FROM SHARED BUFFER. The commands only affect the local
system. Buffers on external application servers are not changed. If the buffer is full, old buffer
objects are kicked out when new objects are exported to the buffer.
Rule of thumb seems to be to double the current figure.
This should directly affect the QlikView loads.
Change to 400000.
rsdb/obj/max_objects 20000 Maximum number of objects in export / import buffer
According to note 702728, as a rule of thumb the value of rsdb/obj/max_objects should
not be larger than one quarter of the value of rsdb/obj/buffersize.
Change to 100000.
__________________________________________________________________________________
Having carried out the above, we restarted the SAP system.
In the connection string I used Packetsize = 100000 and BufferPercent=12. A higher BufferPercent causes the sap system to issue the following message in the SM37 log file for job name /QTQVC/READ_DATA
'Not enough buffer space available - decrease buffer percentage used to 10 or a lower value. Include a string like: ;bufferpercentage=10; in the connection string.'
The control record in table /QTQVC/CONTROL (via transaction SE16) shows
Packetsize 43,124, Tablename = Data1024, buffer_percent = 12
__________________________________________________________________________________________________________
What's the best practise for speeding up the connector.I currently doing the testing on my PC - Is the QV SAP connector throttling the load
due to the PC's spec (Pentium Dual Core 2.8, 2GB Ram)
Is it possible to do parallel loads? How do we synchronise parallel loads so we can produce a single QVD file from them?
Many Thanks for your time.
Dave.
