8 Replies Latest reply: Jun 23, 2016 9:38 PM by JING WEN RSS

    Help: Qlikview SAPSQL connector extraction very slow

    JING WEN

      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.

       

        • Re: Help: Qlikview SAPSQL connector extraction very slow
          Hakan Ronningberg

          Hi,

           

          Which release of the SAP connectors are you using?

           

          Regards,

          Hakan

            • Re: Help: Qlikview SAPSQL connector extraction very slow
              JING WEN

              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

                • Re: Help: Qlikview SAPSQL connector extraction very slow
                  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.

                    • Re: Help: Qlikview SAPSQL connector extraction very slow
                      JING WEN

                      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?

                        • Re: Help: Qlikview SAPSQL connector extraction very slow
                          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

                            • Re: Help: Qlikview SAPSQL connector extraction very slow
                              JING WEN

                              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)

                               

                               

                               

                               

                                • Re: Help: Qlikview SAPSQL connector extraction very slow
                                  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.

                                   

                                  Capture.JPG

                                   

                                  Regards,

                                  Hakan