Fine tuning qvd files?

[siva_boggarapu]

Hi All,

I have two table which are QVD load InvoiceXtable(20M Rows) & CallData(6M  Rows) are two big tables in the datalayer.

I want to tune the above two table. what is the best solution for tuning the table..

For reference, please find the attached screenshot

[Anonymous]

There is not much one can do re tuning QVD files.

Except maybe partition them into seperate monthly QVD's, but could well mess up any incremental loads.

[Anonymous]

Uses Exists and not Exists condition or  Group by aggregation function to reduce the Rows and size.

[rittermd]

You can make sure that the QVD doesn't contain fields that you are not using.  Also, and date/time fields where you don't need the time you can make them just dates and drop the times.

Other than that there is not a lot you can do on the front end to make them smaller.

Also, those sizes are not really what I would consider large. 

[siva_boggarapu]

hi

count(distinct IF(InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit), CallerNumber)) / count(distinct CallerNumber)

Is the above expression gives bad performance? If yes, please suggest how we can modify above expression?

Thanks,

Siva

[Anonymous]

I assume InvoiceDateDT and CallDateDT are in different tables in your data model and doing calculations across a join between two tables is resource hungry and hence slow.

Could you post a screenshot of your data model ?

Also what is in your variable vConversionLimit ?

[siva_boggarapu]

!

vConversionLimit=14

[marcus_sommer]

Better would be to put the condition on the outside of the aggregation and not the inside. I mean:

IF(InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit),

     count(distinct  CallerNumber), 0) / count(distinct CallerNumber)

- Marcus

[Anonymous]

If Marcus's suggestion does not give you enough improvement then think about concatenating your tables InvoiceXTable and CallData together, thus creating a classic star schema with a single central fact table.

You will need to think carefully before doing this, as there may be implications dependent on what your front expressions are.