Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team ,
OverView
I have Developed a QVD File (E151_QVD.qvd , size : 261MB , Records : 4 MM ) .
This QVD file gets populated from a Load QVW file ( Pull_Evenet_151.qvw ) , Scheduled to run every day at 12 PM in the Windows Server.
I am checking a Time Stamp Field called CRTDATE . I fetch the Max date from the QVD file and pull the rest data after that date (CRTDATE) from Oracle database.
I am also pulling 3 extra fields called Year , Month , Day from the database by the to_Char method of Oracle on one of the Table Fields called EVENT_TIME. I am pulling it to show a uase friendly calendar in the QVW report File (Event_151.QVW)
Once Pulled the data from the database i concatenate it with the Existing data in the QVD file.
and store it back in the QVD file.
Problem : It seems that Qlikview when Loads the data from the QVD file and concatenates it to the pulled data from Oracle is very slow. I do not understand , if there is any performance issue in my Script.
Please find my attached QVW file.
Regards
Debashish.
Do you see "QVD Optimized" when it's loading from QVD file?
No I do not see the QVD Optimized .
It shows
QVTable << E151_QVD 3,167,507
How should i make the QVD optimized ??
Regrads
Debashish
When you are loading from QVD, load it as same field names as in QVTable:
LOAD J_NO as OASIS.INSTRUMENT. J_NO ......
And it's always a good practice to mention which table to concatenate: CONCATENATE (QVTable) LOAD .....
Hi Rakesh ,
I could not get the Solution.Can u please elaborate Sir.
Before that i want to mention that , when i load just the QVD file from a diffrent QVW file , i am able to see the " QVD Optimized " .
//---------------------------------------------ONLY THIS SCRIPTS RUN and Shows QVD Optimized.
load J_NO,CYCLE_NO,ZIP_FILE_ID,LEVENTID,IEVENTTYPE,EVENT_TIME,EVENT_TIME_NANOSEC, LASSAYNUMBER,CRTDATE,P1_TIME_STAMP,LRESULTID,DASSAYNUMBER,SREPINDEXINTEST,P69_DESTINATION_SLOT_NUMBER,D111_STATUS, P144_CAL_KIT_BOTTLE_NUMBER_1_F,LCALGROUPRUNID,P335_CURRENT_STEP_COUNT_DEVIAT,P358_SUPPLY_NUMBER,P556_STEP_COUNT_DEVIATION_LIMI,[Year],[Month] ,[Day]
from E151_QVD.qvd (qvd);
//---------------------------------------------
Try this - just for the first 3 fields and let me know what you see. If works, do the same for all the fields:
QVTable:
LOAD OASIS.INSTRUMENT.J_NO as J_NO,
OASIS.EVT_EVENT.CYCLE_NO as CYCLE_NO,
OASIS.EVT_EVENT.ZIP_FILE_ID as ZIP_FILE_ID;
SQL select OASIS.INSTRUMENT.J_NO,
OASIS.EVT_EVENT.CYCLE_NO,
OASIS.EVT_EVENT.ZIP_FILE_ID,
OASIS.EVT_EVENT.LEVENTID,
OASIS.EVT_EVENT.IEVENTTYPE,
OASIS.EVT_EVENT.EVENT_TIME,
OASIS.EVT_EVENT.EVENT_TIME_NANOSEC,
OASIS.EVT_EVENT.LASSAYNUMBER,
ECON.ZIP_FILE.CRTDATE,
TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'YYYY') Year,
TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'MON') Month,
TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'DD') Day,
max(case when IPARAMETERTYPE=1 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P1_TIME_STAMP,
max(case when IPARAMETERTYPE=2 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) LRESULTID,
max(case when IPARAMETERTYPE=3 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) DASSAYNUMBER,
max(case when IPARAMETERTYPE=4 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) SREPINDEXINTEST,
max(case when IPARAMETERTYPE=69 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P69_DESTINATION_SLOT_NUMBER,
max(case when IPARAMETERTYPE=111 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) D111_STATUS,
max(case when IPARAMETERTYPE=144 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P144_CAL_KIT_BOTTLE_NUMBER_1_F,
max(case when IPARAMETERTYPE=259 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) LCALGROUPRUNID,
max(case when IPARAMETERTYPE=335 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P335_CURRENT_STEP_COUNT_DEVIAT,
max(case when IPARAMETERTYPE=358 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P358_SUPPLY_NUMBER,
max(case when IPARAMETERTYPE=556 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P556_STEP_COUNT_DEVIATION_LIMI
from OASIS.EVT_EVENT_PARAMETER,OASIS.EVT_EVENT,OASIS.INSTRUMENT,ECON.ZIP_FILE
where ECON.ZIP_FILE.CRTDATE <= sysdate
AND ECON.ZIP_FILE.CRTDATE > to_date('$(MostRecentUpdate)','YYYYMMDD HH24:MI:SS')
AND ECON.ZIP_FILE.ZIP_FILE_ID = OASIS.EVT_EVENT.ZIP_FILE_ID
AND ECON.ZIP_FILE.ZIP_FILE_ID = OASIS.EVT_EVENT_PARAMETER.ZIP_FILE_ID
AND OASIS.EVT_EVENT.INSTRUMENT_ID = OASIS.EVT_EVENT_PARAMETER.INSTRUMENT_ID
and OASIS.EVT_EVENT.CYCLE_NO = OASIS.EVT_EVENT_PARAMETER.CYCLE_NO
and OASIS.EVT_EVENT.LEVENTID = OASIS.EVT_EVENT_PARAMETER.LEVENTID
and OASIS.INSTRUMENT.INSTRUMENT_ID = OASIS.EVT_EVENT.INSTRUMENT_ID
and OASIS.EVT_EVENT.IEVENTTYPE = 151
group by OASIS.INSTRUMENT.J_NO,
OASIS.EVT_EVENT.CYCLE_NO,
OASIS.EVT_EVENT.ZIP_FILE_ID,
OASIS.EVT_EVENT.LEVENTID,
OASIS.EVT_EVENT.IEVENTTYPE,
OASIS.EVT_EVENT.EVENT_TIME,
OASIS.EVT_EVENT.EVENT_TIME_NANOSEC,
OASIS.EVT_EVENT.LASSAYNUMBER,
ECON.ZIP_FILE.CRTDATE,TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'YYYY'),TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'MON'),TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'DD');
Concatenate (QVTable)
load
J_NO,
CYCLE_NO,
ZIP_FILE_ID
from E151_QVD.qvd (qvd);
Hi Rakesh ,
As u have suggested , i did write " as " in all the fields , it worked faster . But i do not see the "QVD Optimized Statement".
and the Concatenation (QVTable) Load ..... also been modified.
Any ways thanks for ur suggestion (for good tips on writing the Scripts).
Now i can see faster upload.
Regards
Debashish.