Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Synthetic Field Performance Issue

Hi there,

I am attempting to create a QV model from an ERP-grade application. For this, I have "delegated" joins to QV, thinking it was the best approach. I am running this model on a laptop (with 4GB dedicated memoy to the Windows instance I am working from).

Basically, I am attempting to create a sort of a star-schema. I am simplifying the issue, this is what my script looks like:

qualify *

unqualify A, B, C, D

// Transactional data

[Dataset1]:

SQL SELECT A, B, C, D, <...>

qualify *

unqualify A, B

// 'dimension'

[MasterData1]

SQL SELECT A, B, <...>

qualify *

unqualify A, C

// 'dimension'

[MasterData2]

SQL SELECT A, C, <...>

FYI, this is the data yield:

  • DataSet1: 100k records
  • MasterData1: <50 records
  • MasterData2: <5k records

I am satisfied with the result, it's extracting and processing in less than 5 seconds with a very low processor/memory footprint.

Connecting to XXX
Connected
DataSet1 << AAA 112,026 lines fetched
MasterData1 << BBB 29 lines fetched
MasterData2 << CCC 4,096 lines fetched
$Syn 1 = A+B
$Syn 2 = A+C
$Syn 3 = $Syn 1+$Syn 2

Next I am adding one more MasterData to the script:

// 'dimension'

[MasterData3]

SQL SELECT A, D, <...>

I have tried with a MasterData yielding 1 record or 1M records, I get to the same result: I am maxing out ma machine's memory.

Connecting to XXX
Connected
DataSet1 << AAA 112,026 lines fetched

MasterData1 << BBB 29 lines fetched

MasterData2 << CCC 4,096 lines fetched

MasterData3 << DDD 1 lines fetched
$Syn 1 = A+B
$Syn 2 = A+C
$Syn 3 = A+D

What could explain the sudden explosion in memory usage?

Edit:changed the title for more visibility

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Performance Issue I can't explain

The synthetic keys. These are created when you have more than one key between two tables and are usually a sign of an incorrect data model. Probably you should have just one key between each master table and the fact table. So try to remove keys (by renaming the fields) or try to combine two keys into one (by concatenating them: A & '|' & B as Key).

HIC

8 Replies
Employee
Employee

Re: Performance Issue I can't explain

The synthetic keys. These are created when you have more than one key between two tables and are usually a sign of an incorrect data model. Probably you should have just one key between each master table and the fact table. So try to remove keys (by renaming the fields) or try to combine two keys into one (by concatenating them: A & '|' & B as Key).

HIC

Not applicable

Re: Performance Issue I can't explain

Thanks Henric for taking the time to answer.

I have read everything and its contrary about synthetic vs. composite keys.

Composite keys are fine with me, it's just that it's making the data model harder to read.

I will give it a try though a report back.

These are created when you have more than one key between two tables and are usually a sign of an incorrect data model

You've ever worked with ERP systems?

Not applicable

Re: Performance Issue I can't explain

Interestingly, turning to a composite key model solved the performance issues.

This is what I have done:

qualify *
unqualify A&B, A&C, A&D

// Transactional data
[Dataset1]:
SQL SELECT A&B, A&C, A&D, <...>

qualify *
unqualify A&B

// 'dimension'
[MasterData1]
SQL SELECT A&B, <...>

qualify *
unqualify A&C

// 'dimension'
[MasterData2]
SQL SELECT A&C, <...>

qualify *

unqualify A&D

// 'dimension'
[MasterData3]
SQL SELECT A&D, <...>

Not applicable

Re: Performance Issue I can't explain

Now, how is that possible to delegate the composite key creation to QV instead of the ODBC driver? This is a concern as the QV document may connect to ODBC to various RDBMS.

Not all support convert(varchar, A) + '|' + B as CompositeKey.

Employee
Employee

Re: Performance Issue I can't explain

Oh, yes, I have worked with many ERP systems...

My point is that you should not let the ERP system dictate which fields you should use in QlikView. You are in charge and decides what the fields should look like. If the ERP system has two fields that are used as keys in many tables, then you have to create your own QlikView key, by concatenating the two.

Tip1: use <ctrl>-T to see what you have done.

Tip2: use preceding Load to create the keys: e.g.

Load

     x & '|' & y as Key,

     Amount;

SQL SELECT

     x,

     y,

     Amount

FROM ...

The result from the SELECT will then be piped into the Load. The SELECT is evaluated by the ODBC and the Load is evaluated by QlikView.

HIC

sujeetsingh
Honored Contributor III

Re: Performance Issue I can't explain

Guill ,

Composite keys are good when the data mode is simple..

As the structure will gwt complex they will really create problem.

Even sometimes it make reload to fail

Not applicable

Re: Performance Issue I can't explain

Is there some theory to support this?

Highlighted
sujeetsingh
Honored Contributor III

Re: Performance Issue I can't explain

Community Browser