7 Replies Latest reply: Sep 22, 2011 5:21 PM by Marc Kaiser RSS

    Terrible performance SAP connector

    Marc Kaiser

      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

        • Terrible performance SAP connector
          Daniel Rozental

          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.

            • Terrible performance SAP connector
              Marc Kaiser

              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.

                • Terrible performance SAP connector
                  Daniel Rozental

                  have you tried doing an incremental extraction?

                    • Terrible performance SAP connector
                      Marc Kaiser

                      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.

                        • 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!

                  • Terrible performance SAP connector
                    Pablo Labbe

                    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.

                    • Terrible performance SAP connector
                      s j

                      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