[10SR4] Issue with CPU usage on Cast MonthName function

nstefaniuk — Mon, 19 Mar 2012 09:57:32 GMT

Dear all, I have an issue of CPU usage when using the MonthName function.

If I do the following code (no sense, just to show the issue), all my CPU (12) are blocked on 100% usage during 10 to 40s. Sometimes it doesn't happen (but it's rare).

Test:
Load
Today()-RowNo() As TempDate
AutoGenerate(2000);
Test2:
NoConcatenate Load
''&MonthName(TempDate) As MonthName,
TempDate As Date
Resident Test
Order by TempDate Asc;
drop table Test;

It happens ONLY on SR4 64 bits, both in server and standalone version (on the same server).

On SR2 64 bits it's fine

On SR4 32 bits it's fine

The issue comes from the function MonthName: if I use it alone, it works perfectly, but if I try to concatenate it with a text (''&MonthName... ) the issue occurs.

If I try to use Text() to cast instead of ''&, it seems to work better but I am not sure that the result is the same. Of course, to increase difficulty, I have not written this code and I don't really know why the developper has chosen to use ''& instead of Text() and if there is a difference.

Moreover, on the server version, I have defined a CPU affinity on 10 CPU on the 12 that the server has, but Qlikview ignore it and block the 12 CPU...

I think that I need to find 1 solution in the 3 possibilities below:

- a patch to solve this bug

- a better way to cast the result of MonthName

- calculate Month Name by myself instead of using the function MonthName

Here is the log:

19/03/2012 10:41:07: Execution started.

19/03/2012 10:41:07: QlikView Version:10.00.9277.8

19/03/2012 10:41:07: CPU Target x64

19/03/2012 10:41:07: Operating System Microsoft Windows Server 2003 Service Pack 2 (64 bit edition)

19/03/2012 10:41:07: Wow64 mode Not using Wow64

19/03/2012 10:41:07: MDAC Version 2.82.1830.0

19/03/2012 10:41:07: MDAC Full Install Version 2.82.1830.0

19/03/2012 10:41:07: PreferredCompression 2

19/03/2012 10:41:07: EnableParallelReload 1

19/03/2012 10:41:07: ParallelizeQvdLoads 1

19/03/2012 10:41:07: AutoSaveAfterReload 0

19/03/2012 10:41:07: BackupBeforeReload 0

19/03/2012 10:41:07: EnableFlushLog 0

19/03/2012 10:41:07: SaveInfoWhenSavingFile 0

19/03/2012 10:41:07: UserLogfileCharset 0

19/03/2012 10:41:07: OdbcLoginTimeout -1

19/03/2012 10:41:07: OdbcConnectionTimeout -1

19/03/2012 10:41:07: ScriptWantsDbWrite false

19/03/2012 10:41:07: ScriptWantsExe false

19/03/2012 10:41:07: LogFile CodePage Used: 1252

19/03/2012 10:41:07: 0002 SET ThousandSep=' '

19/03/2012 10:41:07: 0003 SET DecimalSep=','

19/03/2012 10:41:07: 0004 SET MoneyThousandSep=' '

19/03/2012 10:41:07: 0005 SET MoneyDecimalSep=','

19/03/2012 10:41:07: 0006 SET MoneyFormat='# ##0,00 €;-# ##0,00 €'

19/03/2012 10:41:07: 0007 SET TimeFormat='hh:mm:ss'

19/03/2012 10:41:07: 0008 SET DateFormat='DD/MM/YYYY'

19/03/2012 10:41:07: 0009 SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'

19/03/2012 10:41:07: 0010 SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.'

19/03/2012 10:41:07: 0011 SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.'

19/03/2012 10:41:07: 0013 Test:

19/03/2012 10:41:07: 0014 Load

19/03/2012 10:41:07: 0015 Today()-RowNo() As TempDate

19/03/2012 10:41:07: 0016 AutoGenerate(2000)

19/03/2012 10:41:07: 1 fields found: TempDate, 2 000 lines fetched

19/03/2012 10:41:07: 0018 Test2:

19/03/2012 10:41:07: 0019 NoConcatenate Load

19/03/2012 10:41:07: 0020 ''&MonthName(TempDate) As MonthName,

19/03/2012 10:41:07: 0021 TempDate As Date

19/03/2012 10:41:07: 0022 Resident Test

19/03/2012 10:41:07: 0023 Order by TempDate Asc

19/03/2012 10:41:07: 2 fields found: MonthName, Date, 2 000 lines fetched

19/03/2012 10:41:45: 0025 drop table Test

19/03/2012 10:41:45: Execution finished.

Thanks for you help

[10SR4] Issue with CPU usage on Cast MonthName function

nstefaniuk — Tue, 17 Apr 2012 14:18:34 GMT

No one has the same issue ? It crash all my environments. I am searching how to avoid to use MonthName, but it's not so easy.

Thanks

Re: [10SR4] Issue with CPU usage on Cast MonthName function

Tue, 17 Apr 2012 14:25:03 GMT

it seems the the code ''&MonthName(TempDate) is just insterting a space before the month and year.

You could try sometime like this:

' ' & Month(Tempdate) & ' ' & Year(Tempdate) as Monthname,

Actually now that I look at it, it could be having issues because you are nameing a field after a function. You could try

''&MonthName(TempDate) as MonthandYear,

[10SR4] Issue with CPU usage on Cast MonthName function

nstefaniuk — Tue, 17 Apr 2012 14:39:33 GMT

You mean that naming the field as the function could by the issue ??

Re: [10SR4] Issue with CPU usage on Cast MonthName function

Tue, 17 Apr 2012 14:43:30 GMT

It could be possible? I have seen others use similar names so it might not be the case.

If you are looking at having the month and year as a text object instead of numerical then using

Month(TempDate) & ' ' & Year(TempDate) as MonthandYear,

Should work fine.

Also, I am not sure if you care, but the format of TempDate is current a number. If in the end you want an actual date you need to do:

Date(TempDate) as Date

[10SR4] Issue with CPU usage on Cast MonthName function

nstefaniuk — Tue, 17 Apr 2012 15:25:37 GMT

It seems you are right. It was obvious but I didn't try it : I have just replaced MonthName by Month() & Year() and the issue doesn't occur anymore.

I will test it more deeply but it seems to be a good pointer, thanks.

I have tried to change the name of the field but it has no impact.

Thanks

topic [10SR4] Issue with CPU usage on Cast MonthName function in QlikView

[10SR4] Issue with CPU usage on Cast MonthName function

[10SR4] Issue with CPU usage on Cast MonthName function

Re: [10SR4] Issue with CPU usage on Cast MonthName function

[10SR4] Issue with CPU usage on Cast MonthName function

Re: [10SR4] Issue with CPU usage on Cast MonthName function

[10SR4] Issue with CPU usage on Cast MonthName function