Help: Qlikview SAPSQL connector extraction very slow

[Anonymous]

I found that the QvSAPSqlConnector is very slow when extracting data from SAP with a HANA DB.

as we just migrate to SAP the volumn of data is not very big.

but it takes much much longer compare to MSSQL server.

Anyone got experienced this as well? Is it normal for SAP connector?

how to improve this?

I connect to Application server with default parameters as below.

[Hakan_Ronningberg]

Hi,

Which release of the SAP connectors are you using?

Regards,

Hakan

[Anonymous]

I'm using the latest version 6.1.2 SR

my SAP version is 7.40, should be the latest as well, as it's a new implementation

[Hakan_Ronningberg]

There are no known performance issues in release 6.1.2.

You could try to increase the Advanced property BufferPercentage from 10 to 15. Also the property PacketSize can be increased.

[Anonymous]

Hi

Even i change the parameters, it doesn't help:

PacketSize=20000000;

FetchBuffers=99999999;

BufferPercentage=30;

I'm using user ID/PWD to log in, not SNC. the user type is SERVICE.

Is there a way to improve the performance?

[Hakan_Ronningberg]

Is it possible for you to attach all corresponding connector log files from C:\ProgramData\QlikTech\Custom Data\QvSAPSqlConnector\Log?

Also the script content if possible?

Thanks,

Hakan

[Anonymous]

Hi Hakan:

This is my latest testing result today. I didn't manage to find how to attach document here (no permission?) so i attached part of the log file and scripts here.

Scripts:

1. Connection String

CUSTOM CONNECT TO

"Provider=QvSAPSqlConnector.exe;servertype=0;

SNC_MODE=false;SNC_QOP=9;advancedProperties=1;ConvRoutine=false;CheckSeparator=false;

Nulldate=true;RemoveAllBlanks=false;ReplaceNullvalue=true;Trace=false;DataTypeLegacyMode=false;

PacketSize=20000000;FetchBuffers=99999999;BufferPercentage=30;TimeOutBatch=6000;

TimeOutFetch=12000;TimeOutStartBatch=24000;BatchJobName=/QTQVC/READ_DATA;";

2. some of the tables have WHERE clause like below (for configuration tabels)

[TVAKT]: // Sales Document Types: Texts
SELECT
AUART,
BEZEI
FROM TVAKT
WHERE SPRAS = 'E' ;

3. Most transaction tables doesn't have any condition except VBRP:

SELECT
VBELN,  // Bill.Doc.
POSNR,  // Item
FKIMG,  // Bill.qty
VRKME,  // Sales unit
KURSK,  // Exch. Rate
NETWR,  // Net value
VGBEL,  // Ref.doc.
VGPOS,  // Ref.item
VGTYP,  // Doc.cat.
AUBEL,  // Sales Doc.
AUPOS,  // Item
MATNR,  // Material
ARKTX,  // Desc.
CHARG,  // Batch
MATKL,  // Matl Group
PSTYV,  // Item cat.
ATPKZ,  // Replacemt
SPART,  // Division
WERKS,  // Plant
ALAND,  // Country
KTGRM,  // AcctAsgnmt
KOSTL,  // Cost Ctr
VKGRP,  // Sales Grp
VKBUR,  // Sales Off.
WAVWR,  // Cost
STCUR,  // ExRateStat
PRCTR,  // Profit Ctr
PS_PSP_PNR,  // WBS Elem.
BRTWR  // Gross val.
FROM VBRP
WHERE NETWR <> 0;

4. Log file: - System Info

SAP .NET Connector 3.0 with file version 3.0.14.0 running on 64-bit .NET Framework 4.0.30319.42000

Program: C:\Program Files\Common Files\QlikTech\Custom Data\QvSAPSqlConnector\QvSAPSqlConnector.exe

Working directory: E:\BI SAP\2. Work Station\Data Extract

Operating system: Windows Server 2008 R2 Standard Server 64-bit Service Pack 1

Processor: 8x AMD64 (or x64)

SAP release: 721, Kernel release: 721, Kernel patch level: 327

Hostname: xxx, IP address: xx.xxx.xxx.x, IP_v6 address:

5. Log file - This error is repeated

>> Error entry 2016-06-23 09:44:56.794 [Thread 4]

[SERVERTYPE=0 ASHOST=xxx-xxx CLIENT=200 SYSNR=00 USER=xxxxPASSWD=xxxx SNC_MODE=0 SNC_QOP=9 ADVANCEDPROPERTIES=1 CONVROUTINE=0 CHECKSEPARATOR=0 NULLDATE=1 REMOVEALLBLANKS=0 REPLACENULLVALUE=1 TRACE=0 DATATYPELEGACYMODE=0 PACKETSIZE=20000000 FETCHBUFFERS=99999999 BUFFERPERCENTAGE=30 TIMEOUTBATCH=6000 TIMEOUTFETCH=12000 TIMEOUTSTARTBATCH=24000 BATCHJOBNAME=/QTQVC/READ_DATA NAME=DefaultDestination]

SAP.Middleware.Connector.RfcAbapException: TIMEOUT_READ_DATA

   at SAP.Middleware.Connector.RfcConnection.ThrowRfcErrorMsg()

   at SAP.Middleware.Connector.RfcConnection.RfcReceive(RfcFunction function)

   at SAP.Middleware.Connector.RfcFunction.RfcDeserialize(RfcConnection conn, IRfcIOStream stream)

   at SAP.Middleware.Connector.RfcFunction.RfcCallReceive(RfcConnection conn, IRfcIOStream stream, RFCID rid)

   at SAP.Middleware.Connector.RfcFunction.Invoke(RfcDestination destination)

[Hakan_Ronningberg]

Hi Jing,

First of all change back the values in the advanced properties. You could even try to decrease the values to:

PacketSize=10000

BufferPercentage=5

However this will probably not solve the problem. My guess is that you are using a busy SAP system. TIMEOUT_READ_DATA indicates that there are no free background processes available for the extraction batch job. The value in the advanced property TimeOutStartBatch (default 2400 seconds) decides how long the connector is waiting for the extraction batch job to start before it aborts the job for timeout. If you try to start a batch job in SAP and there is no available background process, the job will wait and start as soon as there is a free process. Each one of your select statements starts a separate extraction job, so there might be delays while waiting for free processes and you don't see that in the script progress indicator. But it should be visible in the log file.

WHERE conditions can of course also slow down a job. If you use a condition with a field that is not key and not part of an index, the select statement has to read the whole table.

Below is an example of select statements without conditions in an empty SAP HANA 740 system. It is a bit faster.

Regards,

Hakan

[Anonymous]

Hi Hakan:

It really thanks to your explain.

I think the system i use is a QA system, not production server, so the setup and maintenance is not the best.

i also suspect other testing activities takes a lot of buffer as well.

the performnce is much better on production server.