Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
johanlindell
Partner - Creator II
Partner - Creator II

Tip of the week: Enhance performance on mapping, group by and where

Working for a customer with large qvd-tables, 5-15 million rows and "wide" tables (many fields), I have found these performance improvements recently.

If you do a mapping load from a large qvd-table this is slow.

Instead if you load the two mapping fields into a temporary table and then do this mapping load from the temporary table you will see great performance improvements. First the load into the temporary table is optimized and secondly the load from a small (few fields many rows) resident table is way faster than loading from the qvd-file. In my case 10 minutes to under 10 seconds per mapping table.

I have found the same strategy to be working fine if you need to use a "where" and "group by" statement and only want a few fields from the lagre qvd-table.

Instead of doing:

MyMap:

Mapping

LOAD Field1,

           Field2

From [Some large qvd table.qvd] (qvd);

Try out this:

MyTempMappingTable:

LOAD Field1,

           Field2

From [Some large qvd table.qvd] (qvd);

MyMap:

Mapping

LOAD Field1,

           Field2

Resident MyTempMappingTable;

DROP Table MyTempMappingTable;


Instead of doing:

MyMap:

Mapping

LOAD Field1,

           Field2

From [Some large qvd table.qvd] (qvd)

Where Date >= '2018-01-01';

Try out this:

MyTempMappingTable:

LOAD Field1,

           Field2

From [Some large qvd table.qvd] (qvd);

MyMap:

Mapping

LOAD Field1,

           Field2

Resident MyTempMappingTable

Where Date >= '2018-01-01';

DROP Table MyTempMappingTable;


Instead of doing:

MyTable:

LOAD Field1,

           Field2,

           Sum ([Field 3]) as [Sum field3]

From [Some large qvd table.qvd] (qvd)

Group By Field1,

           Field2

;

Try out this:

MyTempTable:

LOAD Field1,

           Field2,

           Field3

From [Some large qvd table.qvd] (qvd);

MyTable:

LOAD Field1,

           Field2,

           Sum ([Field 3]) as [Sum field3]

Resident MyTempTable

Group By Field1,

           Field2

;

DROP Table MyTempTable;


0 Replies