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

[johanlindell]

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;