QVD Reload Problem

[sarfaraz_sheikh]

Dear All,

I am reloading one QVD in my App ...but after optimizing some records the timer on Script Execution process is continue to flow till u will not click on Abort and remaining records in QVD is not optimizing.

This is happening only in the case when i am giving condition in where clause in Script. Without condition the QVD is optimizing Successfully.

But i want to extract the records as per my condition/expectation. I do not know what to do in this case

Kindly help to resolve the same .

Sarfaraz

[Peter_Cammaert]

A single parameter Exists() will still do an optimized load. A two-parameter Exists() will force an unoptimized load.

RawQVD:

LOAD * FROM [.\MyQVD.QVD] (qvd)

WHERE Exists(YourFieldnameToCheckInThisTable);    // Optimized LOAD

[sarfaraz_sheikh]

Dear Joe,

the below is my condition ...how i can put in in Exists()

Resident Helios
     where (((year(CREATE_DATE)*10000+month(CREATE_DATE)*100+day(CREATE_DATE))>='20130401') and ((year(CREATE_DATE)*10000+month(CREATE_DATE)*100+day(CREATE_DATE))<='20141130'));

Sarfaraz

[#4]

Hi Sarfaraz,

exists probably isn't the best solution for that then. I would suggest going with Peter's post further up the thread and during your QVD creation, reduce and take your required subset into a second QVD, which you can then just load straight in to your end application.

Hope that helps

Joe

[Peter_Cammaert]

You can't. Doing more complex things than a simple Exists() will cause an unoptimized QVD LOAD.

But you can simplify your WHERE condition to this:

WHERE (CREATE_DATE >= '1/04/2003') AND

            (CREATE_DATE <= '30/11/2014');

Add this clause to the two-step example in my post above. (change the strings to your local date format)

[#6]

How dates (like 'DD/MM/YYYY' or 'DD/MM/YY' or 'YYYYMMDD' etc.) are stored in CREATED_DATE field in database?

[#7]

you could do this with a simple exists Peter, but as you probably not the best thing for this example

To do it you'd need to create a new field in your QVD, to flag where the required condition was met.

Then could just do an inline load of that value, followed by a concatenate of your QVD and a simple exists, to keep optimised.

Fact:

Load * Inline [

ConditionField

True

];

Concatenate(Fact)

Load * From myqvd Where Exists(ConditionField)

Like I say, not the best for this example, ideally I would go with split daily QVD's and a looped auto concatenate load based on an input parameter

[kuba_michalik]

Fuh, that's easy (not really, and not that clear either )

Disclaimer: make sure that no field "CREATE_DATE" in created in the script before those steps.

First step: load distinct values of the field you want to filter with with. This is optimized (because we use Exist instead of DISTINCT or something) so should be lightning fast:

Values:

LOAD CREATE_DATE

From yourqvd.qvd(qvd)

Where not Exists(CREATE_DATE);

Now you have a list of distinct values, hopefully less of them than records in the QVD.

Second step, load a filtered list of values. That's unoptimized, but you are filtering a single-column table with hopefully less records than in the QVD, so should be reasonably fast (hopefully, at least faster than filtering a whole pre-loaded QVD):

FilteredValues:

NoConcatenate

LOAD CREATE_DATE

Resident Values

Where (some condition);

Third, drop the original list of values:

DROP Table Values;

Fourth, actually load the QVD (finally!). The only condition is Exists(), so the load will be optimized:

Table:

LOAD *

From yourqvd.qvd(qvd)

Where Exists(CREATE_DATE);

Fifth, drop the value list as it is not needed anymore.

I'm attaching an app which demonstrates how to do it on some autogenerated data.