topic Qlikview Server Performance Issue (Preceding Loads) in QlikView

Qlikview Server Performance Issue (Preceding Loads)

obriened83 — Thu, 26 Apr 2012 17:27:21 GMT

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)

danielrozental — Sun, 29 Apr 2012 23:49:03 GMT

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

Qlikview Server Performance Issue (Preceding Loads)

obriened83 — Mon, 30 Apr 2012 13:11:24 GMT

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)

danielrozental — Mon, 30 Apr 2012 14:20:00 GMT

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)

obriened83 — Mon, 30 Apr 2012 16:28:02 GMT

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.

Qlikview Server Performance Issue (Preceding Loads)

danielrozental — Wed, 02 May 2012 19:15:39 GMT

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?

Qlikview Server Performance Issue (Preceding Loads)

danielrozental — Wed, 02 May 2012 19:19:40 GMT

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

Qlikview Server Performance Issue (Preceding Loads)

obriened83 — Thu, 03 May 2012 09:09:56 GMT

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

Re: Qlikview Server Performance Issue (Preceding Loads)

Wed, 25 Sep 2013 23:13:11 GMT

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!

Re: Qlikview Server Performance Issue (Preceding Loads)

Anonymous — Thu, 26 Sep 2013 11:55:12 GMT

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

Re: Qlikview Server Performance Issue (Preceding Loads)

Thu, 26 Sep 2013 12:40:32 GMT

Let me give you a screenshot.

Re: Qlikview Server Performance Issue (Preceding Loads)

Thu, 26 Sep 2013 13:28:43 GMT

It calculate some statistic...the picture show the actual result from the file.

Re: Qlikview Server Performance Issue (Preceding Loads)

Thu, 26 Sep 2013 15:04:20 GMT

Any idea sir?

Thanks

Re: Qlikview Server Performance Issue (Preceding Loads)

Anonymous — Thu, 26 Sep 2013 15:57:50 GMT

Mohamed

I cannot open the screen shot you attached, but no worries, whet I need to know what this calculation is.

Is it a QlikView load script ? If so then then please attach the script and the log file form when it is run.

Is it a QlikView chart ? If so then can you attach the qvw ?

Is it something else ? If so what.

Best Regards, Bill

Re: Qlikview Server Performance Issue (Preceding Loads)

Fri, 27 Sep 2013 12:06:42 GMT

Is the screenshot helpfull sir?