topic Re: How to make this load more quickly in QlikView

How to make this load more quickly

Mon, 08 Apr 2013 02:08:20 GMT

Hi All,

I have one big qvd about 1GB size.

And I use load script to extract data from that qvd.

However, when I use the where clause to filter the records to get what I want, the whole process takes some much time.

How can I speed up that process like adding the index in the SQL table?

Thanks.

Re: How to make this load more quickly

Anonymous — Mon, 08 Apr 2013 03:31:12 GMT

Hey Isaac,

Here's a blog post explaining what will and what will not break the optimized QVD load:

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Re: How to make this load more quickly

Mon, 08 Apr 2013 05:55:06 GMT

HI ,

You can create the View in the DataBase with the filter what ever you have.

And then try to Export that view into Qvd .

So you can use that Exported Qvd wihtout any clause ( i.e By Optimised Load) .

Please let me know if there is anything .

Re: How to make this load more quickly

sujeetsingh — Mon, 08 Apr 2013 06:04:10 GMT

try to resolve the Synthetic keys if they are more!!

Re: How to make this load more quickly

deepakk — Mon, 08 Apr 2013 07:13:27 GMT

Hi,

You can try out the below ways

1) use only required fields

2) Add where clause on resident statement

eg:

Test:

Load a,bc, from qvd;

Load * resident Test where a=9;

I hope this helps you out.

Regards

Deepak

Re: How to make this load more quickly

pauljohansson — Mon, 08 Apr 2013 07:53:39 GMT

Hi,

If you don't perform any calculations and replace your WHERE clause with:

WHERE Exists(Field)

your load will be optimized,

br Paul

Re: How to make this load more quickly

Jason_Michaelides — Mon, 08 Apr 2013 08:49:49 GMT

Adding a WHERE clause breaks the optimized load. If you only have a single field in the WHERE clause you can replace it with WHERE EXISTS - please post your WHERE clasue and I'll help further.

Jason

Re: How to make this load more quickly

swuehl — Mon, 08 Apr 2013 09:13:49 GMT

First, please try not to start multiple threads with the same topic, it makes it hard to follow a topic and clutters the forum.

Coming back to your question: I assume your qvd load changes from optimized to non-optimized, when you added the where clause. This can lead to orders of magnitude slower load times.

I believe the only condition you can use in a where clause to keep getting an optimized qvd load is a single exists() statement.

So maybe you can remodel your load to consider that, maybe like suggested here:

http://community.qlik.com/message/41463#41463

Regards,

Stefan

Re: How to make this load more quickly

jonathandienst — Mon, 08 Apr 2013 10:01:52 GMT

Using a where clause when loading from a QVD will prevent an optimised load. Therefore it will take much longer to load.

Regards

Jonathan

Re: How to make this load more quickly

IAMDV — Mon, 08 Apr 2013 10:57:01 GMT

Hi,

You need to use optimized load which is much faster than normal QVD load. I have used optimized load with 7GB QVD files and it loads in few minutes. Please use Where Exists() instead of simple Where Clause. If you have more than one field in where clause then please build a concatenated key preferably a number and not hashed key or text based key. Then use the where clause with this key along with the Exists() function and this way you always perform optimized load.

I hope this helps!

Cheers,

www.QlikShare.com

Re: How to make this load more quickly

alexandros17 — Mon, 08 Apr 2013 12:11:23 GMT

But is it a Load or a Select? Index affects only Select (from DB)

Re: How to make this load more quickly

jvitantonio — Mon, 08 Apr 2013 12:26:16 GMT

Hi,

You need to use the Exists function in the where clause.

1) Preload the "where" field in a table like

LOAD

customerID

FROM...

2) Load your file and filter data using exists

LOAD * FROM ...

Where exists (customerID)

This example will filter those customers ID existing in the previous loaded table .

Re: How to make this load more quickly

Mon, 08 Apr 2013 13:25:05 GMT

Hi,

I think there are two options to get better load performances:

either

1) create qvd file based on required where condition; in such case where condition is unnecessary when loading qvd again.

2) load entire qvd in memory, without where condition and from loaded data create in memory new table (as resident), that will hold data filtered with where condition. After resident table is created, drop original qvd table from the memory.

regards

Re: How to make this load more quickly

stevedark — Mon, 08 Apr 2013 17:21:33 GMT

Hi there,

It sounds like when the data is loading quickly it is an optimised load, but when you put the where statement on it becomes non optimised. Please see this blog post on the topic of Optimised Lods: http://bit.ly/YnAMqT

How you deal with this depends on the contents of your where statement. If you want a value or set of values matched in a field you can do this:

Temp_IncludeRegions:

LOAD
Region

INLINE [

Region

North

];

MainData:

LOAD
[... field list ...]

FROM MyData.qvd (qvd)

WHERE EXISTS (Region)

;

DROP TABLE Temp_IncludeRegions;

This will then only bring in records from the QVD where the Region is North. Other regions could be added to the temporary table to bring more regions in.

It is important that the fieldnames match between the temporary table and the main table. You must also ensure that the field in question is included in the list of fields loaded from the QVD.

Hope that helps. Please post back more specifics of what your WHERE statement is doing if the above is not applicable.

Regards,

Steve

http://www.quickintelligence.co.uk/

Re: How to make this load more quickly

Mon, 08 Apr 2013 17:43:16 GMT

HI ,

You can create the View in the DataBase with the filter what ever you have.

And then try to Export that view into Qvd .

So you can use that Exported Qvd wihtout any clause ( i.e By Optimised Load) .

Please let me know if there is anything .

Re: How to make this load more quickly

rkspareek1992 — Fri, 06 Apr 2018 11:25:20 GMT

Kindly suggest optimised query for below-mentioned condition:

Where RBUKRS='4100' and Flag_PnL='Original_PnL' and Flag_BS_PnL='Original'.