14 Replies Latest reply: Sep 27, 2013 8:06 AM by Mohamed Ellougani RSS

    Qlikview Server Performance Issue (Preceding Loads)

    Eamonn O'Brien

      Hi

       

      I was wondering if someone could please help me in figuring out this perfomance problem. We currently have Qlikview 10 SR4 installed on our Sever with the following hardware and settings

       

      • 40 Physical Core Processors (Intel(R) Xeon CPU E7-4870 @ 2.4GHz)
      • 512 GB of RAM
      • 64 Bit Operating System
      • Hyper Threading disabled

       

      The problem lies in the fact that my local PC (4 Cores Only) can load un-optimized QVDs faster than the Server. Some load time differences could be over 2 mins compared to 30 seconds on the local PC.

       

      The problem seems to lie in Qlikview's Preceding Loads. For Example loading data from the Data Warehouse is almost ten times faster on the Server when you do not have a preceding load in front of the SQL query. You would expect way better performance from such a powerful Server.

       

      I would greatly appreciate it if anyone can help me solve this performance problem. Thanks again.

       

      Kind Regards

       

      Eamonn

        • Qlikview Server Performance Issue (Preceding Loads)
          Daniel Rozental

          Eamonn, if you post your script we can help you optimize it.

            • Qlikview Server Performance Issue (Preceding Loads)
              Eamonn O'Brien

               

              Hi Daniel

               

              Thanks very much for the reply. I think the issue lies in the way the Server handles the Preceding Loads whether it's optimized or not. For example the sample code below takes 1 hour to run with the preceding load and only 5 minutes without the preceding load applied. Why is this happening? Any help would be appreciated.

               

              Thanks again.

              Eamonn

               

              [BET_ID]:

               

              LOAD     IDFOSOOFFER
              , IDFOBET
              , IDFOBETUNIT
              , AMOUNT
              , EVALUATIONTYPE
              ';

               

              SQL

              SELECT   BAO.IDFOSOOFFER
              , BAO.IDFOBET
              , BAO.IDFOBETUNIT
              , BAO.AMOUNT
              , BAO.EVALUATIONTYPE

              FROM   EPOS.REPORTING.FOBETAPPLIEDOFFER  BAO

                • Qlikview Server Performance Issue (Preceding Loads)
                  Daniel Rozental

                  Sorry, that doesn't make any sense to me.

                   

                  Maybe you're reading the log wrong? can you post it?

                   

                  If it's like that I would definitely bring it to support's attention, as a workaround you can read it without the LOAD, store it and then read it from a file or do a resident LOAD and see how much time that takes.

                    • Qlikview Server Performance Issue (Preceding Loads)
                      Eamonn O'Brien

                      Hi Daniel

                       

                      Please find attached the two logs below. One with the Preceding load and Other without.

                       

                      We currently have what you have suggested implemented but I can't understand why we have to do it this way on the server when it runs perfectly on my PC with the Preceding load included. I'm just wondering has anyone else have ever come across it? Thanks again.

                       

                      Kind Regards

                       

                      Eamonn

                      1 - With Out Preceding Load

                       

                      2012-04-27 17:04:37:    Execution started.
                      2012-04-27 17:04:37:      QlikView Version:10.00.9282.8
                      2012-04-27 17:04:37:      CPU Target                    x64
                      2012-04-27 17:04:37:      Operating System              Windows Server 2008 R2 Enterprise Service Pack 1 (64 bit edition)
                      2012-04-27 17:04:37:      Wow64 mode                    Not using Wow64
                      2012-04-27 17:04:37:      MDAC Version                  6.1.7601.17514
                      2012-04-27 17:04:37:      MDAC Full Install Version     6.1.7601.17514
                      2012-04-27 17:04:37:      PreferredCompression          2
                      2012-04-27 17:04:37:      EnableParallelReload          1
                      2012-04-27 17:04:37:      ParallelizeQvdLoads           1
                      2012-04-27 17:04:37:      AutoSaveAfterReload           0
                      2012-04-27 17:04:37:      BackupBeforeReload            0
                      2012-04-27 17:04:37:      EnableFlushLog                0
                      2012-04-27 17:04:37:      SaveInfoWhenSavingFile        0
                      2012-04-27 17:04:37:      UserLogfileCharset            0
                      2012-04-27 17:04:37:      OdbcLoginTimeout              -1
                      2012-04-27 17:04:37:      OdbcConnectionTimeout         -1
                      2012-04-27 17:04:37:      ScriptWantsDbWrite            false
                      2012-04-27 17:04:37:      ScriptWantsExe                false
                      2012-04-27 17:04:37:      LogFile CodePage Used:        1252
                      2012-04-27 17:04:37: 0003  SET ThousandSep=','
                      2012-04-27 17:04:37: 0002  SET DecimalSep='.'
                      2012-04-27 17:04:37: 0003  SET MoneyThousandSep=','
                      2012-04-27 17:04:37: 0004  SET MoneyDecimalSep='.'
                      2012-04-27 17:04:37: 0005  SET MoneyFormat='€#,##0.00;-€#,##0.00'
                      2012-04-27 17:04:37: 0006  SET TimeFormat='hh:mm:ss'
                      2012-04-27 17:04:37: 0007  SET DateFormat='YYYY-MM-DD'
                      2012-04-27 17:04:37: 0008  SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]'
                      2012-04-27 17:04:37: 0009  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
                      2012-04-27 17:04:37: 0010  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
                      2012-04-27 17:04:37: 0012  Let q = Chr(39)
                      2012-04-27 17:04:37: 0013  Let iStartYear = Year(Today()-5)
                      2012-04-27 17:04:37: 0014  Let iEndYear = Year(Today()-1)
                      2012-04-27 17:04:37: 0015  Let iDaysToBackload = 3
                      2012-04-27 17:04:37: 0017  Let dtLoadBegin = Now()
                      2012-04-27 17:04:37: 0018  Let QVScriptName = 'EPOS Sportsbook'
                      2012-04-27 17:04:37: 0019  Let LoadStartTime = Now()
                      2012-04-27 17:04:37: 0020  Let LoadFinishTime = Now()
                      2012-04-27 17:04:37: 0021  Let DeltaLoadDuration = 0
                      2012-04-27 17:04:37: 0022  Let QVDLoadDuration = 0
                      2012-04-27 17:04:37: 0023  Let DeltaRowCount = 0
                      2012-04-27 17:04:37: 0024  Let TotalRowCount = 0
                      2012-04-27 17:04:37: 0025  Let FieldCount = 0
                      2012-04-27 17:04:37: 0026  Let OSUser = OSUser()
                      2012-04-27 17:04:37: 0027  Let ComputerName = ComputerName()
                      2012-04-27 17:04:37: 0030  IF Hour(Now()) >= 9 and Hour(Now()) <= 16 THEN
                      2012-04-27 17:04:37: 0034    SET EPOS_CxString = '[Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=mmfixedodds;Data Source=PPPRHQ.LBO.PADDYPOWER.COM;Extended Properties=""]'
                      2012-04-27 17:04:37: 0035    Let TableName = 'Connect EPOS Live'
                      2012-04-27 17:04:37: 0036  END IF
                      2012-04-27 17:04:37: 0038  SET EPOS_Password = 'dSZRVWVNGLaQXSRMPDZIWZdF'
                      2012-04-27 17:04:37: 0006  Sub LoadSub (LoadDB, TableName, UniqueKeyName, SubFolderName, QVDLoadFlag, DateSuffix, Preceed_Load_Ind)
                      2012-04-27 17:04:37: 0017  OLEDB CONNECT*Provider*
                      2012-04-27 17:04:37: 0020  [Bet_ID]:
                      2012-04-27 17:04:37: 0021  SQL
                      2012-04-27 17:04:37: 0022  SELECT   BAO.IDFOSOOFFER
                      2012-04-27 17:04:37: 0023        , BAO.IDFOBET
                      2012-04-27 17:04:37: 0024        , BAO.IDFOBETUNIT
                      2012-04-27 17:04:37: 0025        , BAO.AMOUNT
                      2012-04-27 17:04:37: 0026        , BAO.EVALUATIONTYPE
                      2012-04-27 17:04:37: 0027       
                      2012-04-27 17:04:37: 0028  FROM   EPOS.REPORTING.FOBETAPPLIEDOFFER  BAO

                      2012-04-27 17:04:54:        5 fields found: IDFOSOOFFER, IDFOBET, IDFOBETUNIT, AMOUNT, EVALUATIONTYPE, 28,180,305 lines fetched
                      2012-04-27 17:06:32: 0032  STORE [Bet_ID] INTO FILES\Bet_Without_PreceedingLoad.qvd (qvd)
                      2012-04-27 17:06:32: 0033 
                      2012-04-27 17:06:32: 0034 
                      2012-04-27 17:06:32: 0035

                      2012-04-27 17:06:44:      Execution finished.

                      2 - With Preceding Load

                       

                      2012-04-27 17:05:37:      Execution started.
                      2012-04-27 17:05:37:      QlikView Version:10.00.9282.8
                      2012-04-27 17:05:37:      CPU Target                    x64
                      2012-04-27 17:05:37:      Operating System              Windows Server 2008 R2 Enterprise Service Pack 1 (64 bit edition)
                      2012-04-27 17:05:37:      Wow64 mode                    Not using Wow64
                      2012-04-27 17:05:37:      MDAC Version                  6.1.7601.17514
                      2012-04-27 17:05:37:      MDAC Full Install Version     6.1.7601.17514
                      2012-04-27 17:05:37:      PreferredCompression          2
                      2012-04-27 17:05:37:      EnableParallelReload          1
                      2012-04-27 17:05:37:      ParallelizeQvdLoads           1
                      2012-04-27 17:05:37:      AutoSaveAfterReload           0
                      2012-04-27 17:05:37:      BackupBeforeReload            0
                      2012-04-27 17:05:37:      EnableFlushLog                0
                      2012-04-27 17:05:37:      SaveInfoWhenSavingFile        0
                      2012-04-27 17:05:37:      UserLogfileCharset            0
                      2012-04-27 17:05:37:      OdbcLoginTimeout              -1
                      2012-04-27 17:05:37:      OdbcConnectionTimeout         -1
                      2012-04-27 17:05:37:      ScriptWantsDbWrite            false
                      2012-04-27 17:05:37:      ScriptWantsExe                false
                      2012-04-27 17:05:37:      LogFile CodePage Used:        1252
                      2012-04-27 17:05:37: 0003  SET ThousandSep=','
                      2012-04-27 17:05:37: 0002  SET DecimalSep='.'
                      2012-04-27 17:05:37: 0003  SET MoneyThousandSep=','
                      2012-04-27 17:05:37: 0004  SET MoneyDecimalSep='.'
                      2012-04-27 17:05:37: 0005  SET MoneyFormat='€#,##0.00;-€#,##0.00'
                      2012-04-27 17:05:37: 0006  SET TimeFormat='hh:mm:ss'
                      2012-04-27 17:05:37: 0007  SET DateFormat='YYYY-MM-DD'
                      2012-04-27 17:05:37: 0008  SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]'
                      2012-04-27 17:05:37: 0009  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
                      2012-04-27 17:05:37: 0010  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
                      2012-04-27 17:05:37: 0012  Let q = Chr(39)
                      2012-04-27 17:05:37: 0013  Let iStartYear = Year(Today()-5)
                      2012-04-27 17:05:37: 0014  Let iEndYear = Year(Today()-1)
                      2012-04-27 17:05:37: 0015  Let iDaysToBackload = 3
                      2012-04-27 17:05:37: 0017  Let dtLoadBegin = Now()
                      2012-04-27 17:05:37: 0018  Let QVScriptName = 'EPOS Sportsbook'
                      2012-04-27 17:05:37: 0019  Let LoadStartTime = Now()
                      2012-04-27 17:05:37: 0020  Let LoadFinishTime = Now()
                      2012-04-27 17:05:37: 0021  Let DeltaLoadDuration = 0
                      2012-04-27 17:05:37: 0022  Let QVDLoadDuration = 0
                      2012-04-27 17:05:37: 0023  Let DeltaRowCount = 0
                      2012-04-27 17:05:37: 0024  Let TotalRowCount = 0
                      2012-04-27 17:05:37: 0025  Let FieldCount = 0
                      2012-04-27 17:05:37: 0026  Let OSUser = OSUser()
                      2012-04-27 17:05:37: 0027  Let ComputerName = ComputerName()
                      2012-04-27 17:05:37: 0030  IF Hour(Now()) >= 9 and Hour(Now()) <= 16 THEN
                      2012-04-27 17:05:37: 0034    SET EPOS_CxString = '[Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=mmfixedodds;Data Source=PPPRHQ.LBO.PADDYPOWER.COM;Extended Properties=""]'
                      2012-04-27 17:05:37: 0035    Let TableName = 'Connect EPOS Live'
                      2012-04-27 17:05:37: 0036  END IF
                      2012-04-27 17:05:37: 0038  SET EPOS_Password = 'dSZRVWVNGLaQXSRMPDZIWZdF'
                      2012-04-27 17:05:37: 0006  Sub LoadSub (LoadDB, TableName, UniqueKeyName, SubFolderName, QVDLoadFlag, DateSuffix, Preceed_Load_Ind)
                      2012-04-27 17:05:37: 0017  OLEDB CONNECT*Provider*
                      2012-04-27 17:05:37: 0020  [Bet_ID]:
                      2012-04-27 17:05:37: 0021  SQL
                      2012-04-27 17:05:37: 0022  SELECT   BAO.IDFOSOOFFER
                      2012-04-27 17:05:37: 0023        , BAO.IDFOBET
                      2012-04-27 17:05:37: 0024        , BAO.IDFOBETUNIT
                      2012-04-27 17:05:37: 0025        , BAO.AMOUNT
                      2012-04-27 17:05:37: 0026        , BAO.EVALUATIONTYPE
                      2012-04-27 17:05:37: 0027       
                      2012-04-27 17:05:37: 0028  FROM   EPOS.REPORTING.FOBETAPPLIEDOFFER  BAO

                      2012-04-27 17:05:54:        5 fields found: IDFOSOOFFER, IDFOBET, IDFOBETUNIT, AMOUNT, EVALUATIONTYPE, 28,180,305 lines fetched
                      2012-04-27 17:55:32: 0032  STORE [Bet_ID] INTO FILES\Bet_With_PreceedingLoad.qvd (qvd)
                      2012-04-27 17:55:32: 0033 
                      2012-04-27 17:55:32: 0034 
                      2012-04-27 17:55:32: 0035

                      2012-04-27 17:55:44:      Execution finished.

                • Re: Qlikview Server Performance Issue (Preceding Loads)

                  Hi everyone,

                   

                  I am having same issue, poor perfomance with the following server.

                  QlikView Version 11.20 SR2.

                   

                  IBM HX5 BLADE in COMPLEX Mode

                  SSD 50GB RAID 1

                  128GB DDR3

                  Windows 2008 R2 Enterprise (All fix pack/updated) & Tried Windows 2012 Standard (All fix pack/updated)

                  CPU E7-4807 1.86 X 4 CPU (6 Cores) = (24 Cores total) with Hyper threading deactivated

                  http://ark.intel.com/products/53569/Intel-Xeon-Processor-E7-4807-18M-Cache-1_86-GHz-4_80-GTs-Intel-QPI

                  Simple statistic calculation from a file size of 600MB Test took 4.30 Minutes

                   

                  Over a less powerful blade it's took 30% less time to achieve same operation.

                  IBM HS22 Blade

                  SAS 146GB RAID 1

                  48GB DDR3

                  Windows 2008 R2 Enterprise (All fix pack/updated)

                  E5530 2.40 X 2 CPU (4 Cores) = (8 Cores total) with Hyper threading deactivated

                  http://ark.intel.com/products/37103/

                  Simple statistic calculation from a file size of 600MB Test took 3:06 Minutes

                   

                  I followed all the possible document online about hardware setting and so on but wasn't able to improve the performance.

                  Quick tips #8 - Server Settings For Best Performance

                   

                  I even tried running a VM on the HX5 over the SAN to rule out the internal SSD drive but that made absolutly no difference at all.

                   

                   

                  Any suggestion will be welcomed.

                   

                  Thanks in advance!