Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
sekharQV
Creator
Creator

Order by Clause Impacting Script load performance

Hi All,

Greetings of the day.

The Order by Clause that I have defined in the script consuming above 5 to process the data. Can someone help me to optimize this script?

LOAD CraneID & ' ' & Upper (cp_id) as IMEI_cp_id,
CraneID as Parameter_IMEI,
CraneID as Active_Parameter_IMEI,
Upper (cp_id) as Parameter_cp_id,
Dayname (datetime) as Parameter_date,
Dayname (datetime) as Date,
parameter as Parameter_id,
value,
value as Parameter_value_cumulative,
If (Previous (CraneID) = CraneID AND Previous (cp_id) = cp_id AND Previous (parameter) = parameter, value - Previous (value), 0) as Parameter_value,
Rowno () as Rowno
RESIDENT Paramater
WHERE Len (CraneID) > 0
ORDER BY CraneID,
cp_id,
parameter,
datetime;

 

Thanks in Advance,

Rajasekhar P.

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

Sometimes it's faster to split a larger transformation into several logically tasks. In your case it could mean to filter at first the data (applying the where-clause) and ordering the data within another load and maybe even applying the previous() stuff within a following step. In each case you need to keep an eye on the task-manager to check if there is enough RAM available and if not considering to store the intermediate steps into a qvd and then dropping the tables and loading from the qvd again.

I'm not sure if this in your case makes a significantly difference but you may try it. More effect as this might be possible if you could combine your order by fields within the load before - all your fields seems to be numeric so you could develop a logic to add and multiply them. Sounds more complicated as it is - you need only to be carefully that the results are always unique and not longer as 14 digits. Here a practically example from one of my keys:

([StoreID] * pow(10, 4)) + ([OrderID]) * pow(10, 8)) + [OrderlineID]

With each field which you could save from applying in the order by statement the performance should increase.

- Marcus

View solution in original post

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Sometimes it's faster to split a larger transformation into several logically tasks. In your case it could mean to filter at first the data (applying the where-clause) and ordering the data within another load and maybe even applying the previous() stuff within a following step. In each case you need to keep an eye on the task-manager to check if there is enough RAM available and if not considering to store the intermediate steps into a qvd and then dropping the tables and loading from the qvd again.

I'm not sure if this in your case makes a significantly difference but you may try it. More effect as this might be possible if you could combine your order by fields within the load before - all your fields seems to be numeric so you could develop a logic to add and multiply them. Sounds more complicated as it is - you need only to be carefully that the results are always unique and not longer as 14 digits. Here a practically example from one of my keys:

([StoreID] * pow(10, 4)) + ([OrderID]) * pow(10, 8)) + [OrderlineID]

With each field which you could save from applying in the order by statement the performance should increase.

- Marcus

View solution in original post

Brett_Bleess
Support (Former)
Support (Former)

If Marcus' post was what you needed, please be sure to close out your thread by using the Accept as Solution button on his post to give him credit for the help and to let other Members know that worked.

Here are a couple of Help links that may be useful too:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/LoadData/best-p...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/application-per...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.