Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

obriened83
Contributor

Qlikview Server Performance Issue (Preceding Loads)

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

14 Replies
danielrozental
Honored Contributor II

Qlikview Server Performance Issue (Preceding Loads)

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

obriened83
Contributor

Qlikview Server Performance Issue (Preceding Loads)

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

danielrozental
Honored Contributor II

Qlikview Server Performance Issue (Preceding Loads)

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.

obriened83
Contributor

Qlikview Server Performance Issue (Preceding Loads)

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.

danielrozental
Honored Contributor II

Qlikview Server Performance Issue (Preceding Loads)

Sorry but I don't see the preceeding load in the 2nd script.

28 million rows is a large enough number of rows, are you sure the first script isn't just faster because the query has been cached by the Oracle Engine?

Highlighted
danielrozental
Honored Contributor II

Qlikview Server Performance Issue (Preceding Loads)

It might be related to this post by Vlad http://community.qlik.com/message/215075#215075 you should try that

obriened83
Contributor

Qlikview Server Performance Issue (Preceding Loads)

Hi Dainel

Thanks again for getting back to me. Sorry about the confusion with the log files above (Careless editing).

The Preceding Load look like this below:

012-04-30 17:04:11: 0013  LOAD      IDFOSOOFFER
2012-04-30 17:05:37: 0014           , IDFOBET
2012-04-30 17:05:37: 0015           , IDFOBETUNIT
2012-04-30 17:05:37: 0016           , AMOUNT
2012-04-30 17:05:37: 0017           , EVALUATIONTYPE

As for the query been cached by the Oracle Engine, unfortunately this is not the case because on my local PC the query is completed in 4 minutes with a preceding load.

I'll have a look into Vlad post and see if there is something I missed in the Serve config. Cheers for the help again.

Kind Regards

Eamonn

Not applicable

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-Q...

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!

Re: Qlikview Server Performance Issue (Preceding Loads)

Mohamed

When you say you were using a 'Simple statistic calculation from a file size of 600MB Test' could you advise what this test comprised ?

Best Regards,     Bill