Qlik Community

QlikView Connectors

kaisemar
New Contributor III

Terrible performance SAP connector

Hi there

One of our clients is facing terrible performance when extracting data from SAP (SQL-Connector) from test system. Select * from VBAP takes 10 mins for 50'000 records. In total, 7 mio records are available in VBAP (~ 24 hrs for 7 mio records...). All possible parameters in connector are set (JobClass, Buffer Percentage, PacketSize). We use message server host - but same performance problem via application server host. QV support is involved but no progress so far.

SAP core: 700

CPU: 8 cores

RAM: 10 GB

Virtualized server

QVCONN: QvSAPConnector.dll 5, 5, 8698 SR3

Transports: E6DK900099 / E6DK900086

While loading data, only 2 CPUs are busy and 3 GB RAM are free.

SM50: Access to VBAP can be confirmed, but with an interval of 30 seconds only.

The same 30 seconds interval can be monitored on level hard drive access.

Most of the time, SAP is busy with the QV programs.

QlikView gets ~ 5'000 records every 30-50 seconds.

abap/buffersize            500‘000
r
sdb/cua/buffersize       100‘000
rsdb/esm/buffersize_kb            8‘192
rsdb/obj/buffersize       256‘000
rsdb/otr/buffersize_kb              4‘096
rsdb/esm/max_objects             2‘000
rsdb/obj/max_objects   150‘000
rsdb/otr/max_objects    2‘000

Every idea or hint is very much appreciated!

Regards, Marc

7 Replies
danielrozental
Honored Contributor II

Terrible performance SAP connector

There are some setings you could mess with in the connector string, like buffer sizes, etc. Check the connector manual for more information.

That said, you should be doing an incremental load of VBAP table, you should check if there are any VBAK Date field indexed and use that doing a join with VBAP.

kaisemar
New Contributor III

Terrible performance SAP connector

Initially, we didn't touch the connection string. Only after facing the performance issue, we started modifying. e.g. we increased BufferPercentage to 50 (default = 10) but then the job in SAP stopped with errors ("not enough buffer space available - decrease buffer percentage used to 10 or a lower level..."). Also playing around with other parameters didn't increase the performance. So, we went back to default.

danielrozental
Honored Contributor II

Terrible performance SAP connector

have you tried doing an incremental extraction?

pablolabbe
Valued Contributor II

Terrible performance SAP connector

Try to limit the number of columns, selecting only the usefull ones to the Analysis.

Avoid using SELECT * , when extracting data from any datasource. Its a good practice.

suniljain
Honored Contributor

Terrible performance SAP connector

Try to extract only required column only. because in each sap table there are lots of unnecessary column.

in case of increamental load take reference of header table.

E.g

To extract delta from VBAP , take reference of VBAK

kaisemar
New Contributor III

Terrible performance SAP connector

We simulated an increment load - same slow performance but acceptable as we don't need to extract millions of rows (200'000 new / modified rows in VBAP per day). But before loading incremental, we need the initial (full) load. Looks like we slice the full load per month - for each table and month one separate extract. And then schedule 4 to 6 parallel jobs via publisher. that will take a couple of days to (fully) load the data from all the needed tables. And then set up incremental loads.

Not applicable

Terrible performance SAP connector

Hello Marc,

do you have already checked your SQL statement regarding performance issue. For example what kind of keys do you use in your SQL command.

Check 1: Check that, if it is possible, you make a WHERE condition on a full index. Otherwise you will get a bad performance.

Check 2: Check the amount of data in the origin SAP table. If possible reduce the amount of data e.g. WHERE conditon or SAP archive mechanism.

Check 3: Create a performance trace and analyze the QV SAP connector in the SAP system to identfy your problem.

I hope it will help you a little bit

Best regards!

Community Browser