How to make this load more quickly

[#1]

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.

[alexandros17]

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

[jvitantonio]

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 .

[#4]

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.

or

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

[stevedark]

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/

[#6]

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 .

[rkspareek1992]

Hi

Kindly suggest optimised query for below-mentioned condition:

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