Slow Extraction using SAP Connector

[#1]

Hi all,

I'm currently pulling some data from the VBAP table in SAP. And the performance is very slow! The table size is 100mil records and is about 100GB is disk size. I loaded it for more than 12 hours and it only loaded 10mils records! This is with all the columns. I tried to reduce the number of columns to around 10, but the speed is still slow... only 30mils extracted in 5hours. Is this normal?

Is there anyway to improve the speed? Any thing that I can tweak in QlikView or SAP? The QV server is 64core with 64GB of RAM. QlikView's RAM usage is not high... less than 10GB. Plenty of free memory. Using SAP Connector 5.4, QlikView 9 SR5.

[suniljain]

Dear Knim,

Hardware configuration is good. But can you tell me Are you extracting in Idle hour or working hours.

VBAP is Sales order details table. it is indexed in SAP so I think it sohuld not take this much time during extraction.

Because I alredy have lots of experience in this types of issue with SAP R/3 and SAP BW.

Regards

Sunil Jain;

[#3]

Hi Sunil,

I'm extracting during Idle hour... but the extraction took too long that it lasted till working hour. Tried to filter the data based on the Creation Date using WHERE clause, but QV just hang there for more than half hour without extracting anything.

[#4]

Hi khim, are you positive you are extracting with the same version of connector and transports?

[#5]

Hmm... I'm using the latest the version of installer and transport... so I think it should be same. Any way of checking the version of transport installed and the version of the SAP connector? Just want to make sure that I installed the correct version...

[#6]

Hi khim, the version on the connector can be checked on control panel -> add or remove programs. I'm not sure how can you check the version on the transports, I think the easiest way is reinstall the latest transports, since it only take 5 minutes.

Regards

[disqr_rm]

Following are the transports with respect to the SAP Connector release version:

5.4: E6DK900056 & E6DK900051

5.4 SR1: E6DK900064 & E6DK900051

Please check in SE01 transaction which transport shows you log. The ones shows you log have been imported. If you see all 4 of them, it's ok as the SR1 one xx64 will overwrite all objects from xx56. And if you do not have xx56 and xx64 then you may have Connector 5.3 (or old) transports installed. Then work with you BASIS guys to get new transports in.

For the extract speed, you may want to play with buffer & packet size parameter, as they help you. These parameters are documented in the installer help document. If you don't have one, please post your Connector version and I can provide one.

By the way, do you have any WHERE condition in your query? Or you extracting the whole table without any condition?

Hope this helps a little.

[#8]

Hi Rakesh,

Thanks for the info. Will ask the SAP guys to check the log.

I tried to increase the buffer and packet size, but there is no obvious improvement in the speed.

At first, I don't have WHERE clause. But since it take ages do load, it decided to add the WHERE clause. For filtering data based on the document no (VBELN) it is fast, but when I try to filter it using Date (ERDAT), QV just hang there without extracting. But since I have the sales header information, it is more accurate for me to extract using Date instead of doc no. (the document no have different range, so I can't use >=)

[disqr_rm]

Yes, this is correct as VBAP does not have an index for ERDAT (only field) as standard, unless you have got one "Z" index created. That may not be a bad idea to get an index on VBAP-ERDAT. Check with you basis guys what they say. Also start the QV download and look into SAP (SM66, etc) where it's taking long time. I am sure it's sequential read though.

I have used data pulls on the date fields with 50-60 mil records and it takes less than an hour to do the whole cycle - pull data from SAP and delta update to the QVD files.

On the side, I would also recommend you to open a support request with QlikTech (or your partner). They can surely check and verify if you have correct installation in your system.

[#10]

Hi Rakesh,

Just checked with the BASIS team, and we installed the correct version. The BASIS version is 620 so we installed E6DK900051and E6DK900067. But they do get some warning when installing the transport:

tp addtobuffer E6DK900051 SPR pf=/usr/sap/trans/bin/TP_DOMAIN_SPR.PFL
This is tp version 340.16.41 (release 640)
Warning: Parameter DBLIBPATH is no longer used.
Warning: Parameter DBSWPATH is no longer used.
Addtobuffer successful for E6DK900051
tp finished with return code: 0
meaning:
Everything OK

tp import E6DK900051 SPR client=368 pf=/usr/sap/trans/bin/TP_DOMAIN_SPR.PFL
This is tp version 340.16.41 (release 640)
Warning: Parameter DBLIBPATH is no longer used.
Warning: Parameter DBSWPATH is no longer used.
This is R3trans version 6.13 (release 640 - 21.10.08 - 10:02:00).
Database table TCPDB empty. Guessing! See note 42305
Syslog: k CP2 : TCPDB&0& rscpll 17
R3trans finished (0004).
tp finished with return code: 4
meaning:
A tool used by tp produced warnings
ckgsapbk:spradm 26>


tp addtobuffer E6DK900067 SPR pf=/usr/sap/trans/bin/TP_DOMAIN_SPR.PFL
This is tp version 340.16.41 (release 640)
Warning: Parameter DBLIBPATH is no longer used.
Warning: Parameter DBSWPATH is no longer used.
Addtobuffer successful for E6DK900067
tp finished with return code: 0
meaning:
Everything OK

tp import E6DK900067 SPR client=368 pf=/usr/sap/trans/bin/TP_DOMAIN_SPR.PFL
This is tp version 340.16.41 (release 640)
Warning: Parameter DBLIBPATH is no longer used.
Warning: Parameter DBSWPATH is no longer used.
This is R3trans version 6.13 (release 640 - 21.10.08 - 10:02:00).
Database table TCPDB empty. Guessing! See note 42305
Syslog: k CP2 : TCPDB&0& rscpll 17
R3trans finished (0004).
This is R3trans version 6.13 (release 640 - 21.10.08 - 10:02:00).
Database table TCPDB empty. Guessing! See note 42305
Syslog: k CP2 : TCPDB&0& rscpll 17
R3trans finished (0004).
tp finished with return code: 4
meaning:
A tool used by tp produced warnings
ckgsapbk:spradm 28>