Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Eamonn, if you post your script we can help you optimize it.
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
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.
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.
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?
It might be related to this post by Vlad http://community.qlik.com/message/215075#215075 you should try that
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
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
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!
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