topic Re: Log file help in QlikView

Log file help

amit_saini — Thu, 30 Apr 2015 04:40:35 GMT

Hi Folks,

I'm sharing log file of one of my qvw. Usually this take 2 hours to finish the task , but from past 3-4 days it is taking 10-12 hours.

Not sure what could be the reason, please see the log file and suggest.

Thanks,

Re: Log file help

Gysbert_Wassenaar — Fri, 01 May 2015 08:04:33 GMT

Check the log file \\TXKAPPHUT003\SourceDocuments\Operation\SCAQ\Lavonia\#App\Datamodel\2_SCAQ_Datenmodell - Lavonia_PayPoint.qvw.log. That log file will show you a timeline of the script execution. That should allow you to pinpoint which parts took up most of the time.

Re: Log file help

amit_saini — Mon, 04 May 2015 04:06:23 GMT

Gysbert,

I checked log files . After 2:33 PM next update is 6:27 PM and again after 6:27 PM next was at 11:19 PM

5/3/2015 2:33:42 PM: 0472 TankCalc_temp:

5/3/2015 2:33:42 PM: 0473 load distinct

5/3/2015 2:33:42 PM: 0474

5/3/2015 2:33:42 PM: 0475

5/3/2015 2:33:42 PM: 0476 %Tank_Calc_Key,

5/3/2015 2:33:42 PM: 0477

5/3/2015 2:33:42 PM: 0478 count(DISTINCT Acquisition_Date) as Fehler,

5/3/2015 2:33:42 PM: 0479 sum(SCRAP_FLAG) as SCRAP_STATUS

5/3/2015 2:33:42 PM: 0480 resident Stichprobe

5/3/2015 2:33:42 PM: 0481 where Acquisition_Status_No = 1 and not isnull(Tank_SNr)

5/3/2015 2:33:42 PM: 0482 group by %Tank_Calc_Key

5/3/2015 6:27:16 PM: 3 fields found: %Tank_Calc_Key, Fehler, SCRAP_STATUS, 261,145 lines fetched

5/3/2015 6:27:18 PM: 0483 join

5/3/2015 6:27:18 PM: 0484 load distinct

5/3/2015 6:27:18 PM: 0485

5/3/2015 6:27:18 PM: 0486

5/3/2015 6:27:18 PM: 0487 %Tank_Calc_Key,

5/3/2015 6:27:18 PM: 0488 FirstValue(Acquisition_Date) as Acquisition_Date_Final,

5/3/2015 6:27:18 PM: 0489 FirstValue(Acquisition_Status_No) as StatMax

5/3/2015 6:27:18 PM: 0490

5/3/2015 6:27:18 PM: 0491 resident Stichprobe

5/3/2015 6:27:18 PM: 0492 where not isnull(Tank_SNr)

5/3/2015 6:27:18 PM: 0493 group by %Tank_Calc_Key order by Tank_SNr,Acquisition_Date desc

5/3/2015 11:19:20 PM: 3 fields found: %Tank_Calc_Key, Acquisition_Date_Final, StatMax, 2,200,492 lines fetched

5/3/2015 11:19:31 PM: Joining/Keeping

5/3/2015 11:19:35 PM: 0496 TankCalc:

5/3/2015 11:19:35 PM: 0497

5/3/2015 11:19:35 PM: 0498 LOAD distinct

5/3/2015 11:19:35 PM: 0499

5/3/2015 11:19:35 PM: 0500 %Tank_Calc_Key,

5/3/2015 11:19:35 PM: 0501

5/3/2015 11:19:35 PM: 0502 Acquisition_Date_Final,

5/3/2015 11:19:35 PM: 0503 1 as Tank_Sum,

5/3/2015 11:19:35 PM: 0504 if(isnull(Fehler) or (Fehler > 0 and StatMax = 0) , 1, 0) as OK,

5/3/2015 11:19:35 PM: 0505 if(Fehler > 0 and (StatMax = 1 or isnull(StatMax)) , 1, 0) as NOK,

5/3/2015 11:19:35 PM: 0506 if(isnull(Fehler) or (Fehler > 0 and StatMax = 0) and SCRAP_STATUS < 1, 1, 0) as OK_WS,

5/3/2015 11:19:35 PM: 0507 if(Fehler > 0 and (StatMax = 1 or isnull(StatMax)) and SCRAP_STATUS < 1, 1, 0) as NOK_WS,

5/3/2015 11:19:35 PM: 0508 if(Fehler > 0 and StatMax = 0 , 1, 0) as REW,

5/3/2015 11:19:35 PM: 0509 if(Fehler > 0 and StatMax = 0,Fehler, 0) as REWT,

5/3/2015 11:19:35 PM: 0510 IF(SCRAP_STATUS >0 ,1,0) as SCRP,

5/3/2015 11:19:35 PM: 0511 Fehler,

5/3/2015 11:19:35 PM: 0512 SCRAP_STATUS,

5/3/2015 11:19:35 PM: 0513 StatMax

5/3/2015 11:19:35 PM: 0514 resident TankCalc_temp

5/3/2015 11:19:35 PM: 13 fields found: %Tank_Calc_Key, Acquisition_Date_Final, Tank_Sum, OK, NOK, OK_WS, NOK_WS, REW, REWT, SCRP, Fehler, SCRAP_STATUS, StatMax, 2,200,492 lines fetched

5/3/2015 11:19:38 PM: 0527 drop table TankCalc

Please help me to understand the reason for this.

Thanks,

Re: Log file help

Gysbert_Wassenaar — Mon, 04 May 2015 07:40:30 GMT

Well, you're doing some expensive calculations which may require lots of ram memory. Check the ram usage of your server during the reload. If so much memory is needed that the pagefile is used the server will slow down to a crawl.

The distinct is not necessary if you use aggregations like sum and count and a group by clause. The order by in the second load statement won't do anything useful because of the aggregating/grouping and then joining it to the first load. Loading from a resident table can be slower than loading from a qvd. You can try storing Stichprobe into a qvd and load that qvd instead of doing a resident load. That may or may not make any difference. Testing it will tell.

STORE Stichprobe INTO Stichprobe_Temp.qvd (qvd);

TankCalc_temp:

load distinct

%Tank_Calc_Key,

count(DISTINCT Acquisition_Date) as Fehler,

sum(SCRAP_FLAG) as SCRAP_STATUS

FROM Stichprobe_Temp.qvd (qvd);

where Acquisition_Status_No = 1 and not isnull(Tank_SNr)

group by %Tank_Calc_Key;

join

load distinct

%Tank_Calc_Key,

FirstValue(Acquisition_Date) as Acquisition_Date_Final,

FirstValue(Acquisition_Status_No) as StatMax

FROM Stichprobe_Temp.qvd (qvd);

where not isnull(Tank_SNr)

group by %Tank_Calc_Key

order by Tank_SNr,Acquisition_Date desc

;

Re: Log file help

amit_saini — Mon, 04 May 2015 08:43:29 GMT

Thank you so much for your reply , let me check if this is the case.

But one think I don't understand that usually this data model took 2-3 Hours to finish the task but from last week it is taking around 10 Hours.

Thanks,

Re: Log file help

Peter_Cammaert — Mon, 04 May 2015 08:51:45 GMT

Well, if nothing has changed in your script, you'll have to look at the environment. There are two main influencers to check:

More data is being loaded in your document?
More memory is being used by QVS (=therefore unavailable to the QVB process) or by other server programs?

The first you can verify by checking the logs from april 22 & 23. The second can be monitored by using Windows instrumentation tools (like Performance Monitor)

Re: Log file help

marcus_sommer — Mon, 04 May 2015 10:33:21 GMT

Maybe you could speed up your loads by splitting those statements into several steps:

1. load with where-clause + (optional) store & drop the table

2. load with group by from the 1. qvd (or per resident)

- Marcus