Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thkarner
Partner - Creator III
Partner - Creator III

28 minutes to store a QVD with 98mio records?

Hi,

I´m currently optimizing my apps and found out that it took very long to execute the STORE command in case of big data.

I loaded ~98 million records in 2,5 minutes but it took me more than 27 minutes to store the data on the disk.

The QVD file has a size of nearly 12 GB afterwards. Not that big.

See also the log:

  2017-02-15 01:43:51 0098 [COPA]:

  2017-02-15 01:43:51 0099     First 1000000000

  2017-02-15 01:43:51 0100     LOAD

  2017-02-15 01:43:51 0101 *

  2017-02-15 01:43:51 0102 FROM [lib://Folder_DataModel/Corporate\COPA\COPA.qvd] (qvd)

  2017-02-15 01:43:51 0103     Where exists ([COPA.0COMP_CODE])

  2017-02-15 01:46:19       81 fields found: %ZWBSOLDTO, INFOPROVIDER, %0SOLD_TO, %0MATERIAL, %0MAT_SALES, %ZWBSENDTO, %DATE, COPA.0COMP_CODE, Currency, Base Unit, Square meter (unit), Statistic unit 1 (unit), Statistic unit 2 (unit), Thousand norm-format (unit), Ton  (unit), Thousand waalformat (unit), COPA.0SALESORG, COPA.0VTYPE, Corp. Unit 1, Corp. Unit 2, Corp. Unit 3, Standard discount (4) LC, Special discount (5) LC, Sales quantity, Square meter, Statistic quantity 1, Statistic quantity 2, Thousand norm-format, Ton, Thousand waalformat, Invoiced Sales (10) LC, Gross sales (3) LC, COS (18) LC, Corporate quantity 1, Corporate quantity 2, Corporate quantity 3, Gross margin 1 (19) LC, Net Sales 1 (13) LC, Net sales 2 (15) LC, NVGS (6) LC, Conversion Rate EUR, Standard discount (4) EUR, Special discount (5) EUR, Invoiced Sales (10) EUR, Gross sales (3) EUR, COS (18) EUR, Gross margin 1 (19) EUR, Net Sales 1 (13) EUR, Net sales 2 (15) EUR, NVGS (6) EUR, %ZOBJEKT, %ZPROMOTER, Sales order, Cubic meter (unit), Meter (unit), Sack (unit), Meter of elevation (unit), Waalformat (unit), Invoice, Position, Cubic meter, Meter of elevation, Sack, COPA.ZWBSOLDTO.SALES_OFF, COPA.ZWBSOLDTO.SALES_GRP, Bill type, Company code, Productgroup, Sales order type, Plant, Sales group, Sales office, Sales organisation, Value type, Market, %SA_KEY_COPA, Actual, Budget, BFC03, BFC06, BFC09,

  2017-02-15 01:46:19      98 837 458 lines fetched

  2017-02-15 01:46:20 0109 STORE [COPA] INTO [lib://Folder_DataModel/CBME_Corporate\COPA\COPA.qvd] (qvd)

  2017-02-15 02:13:52 0111 DROP Table _TMP_SPLIT

Is this something which is a bottleneck on the disk I/O of the server? Or is this a normal behaviour of Qlik Sense Server (3.1. SR4)?


Let you share your experience with me 🙂

BR Thomas

PS: Storing the original QVD with 160 mio records takes 47 minutes ...!?

4 Replies
Anonymous
Not applicable

I would look at the whole i/o path.

Especially checking if the disc is local to the server or the i/o's go over some network connection.

vlad_komarov
Partner - Specialist III
Partner - Specialist III

Thomas,

You probably need some data model optimisation as well.

98 Mil records is not too much, but 12 GB file probably is. I do store QVD files with 15 - 20 MIl of records and it usually takes ~3-5 seconds....

"2/15/2017 7:55:51 PM| Perimeter_ Table load done

"2/15/2017 7:55:56 PM| [ALL_Perimeter.qvd] File is closed. QVDNoOfRecords = 14,931,529 "


I would suggest to check if you really need to store all these GBs in one file.

Or if you really do, try to split it into multiple QVDs and do the Incremental load, if applicable.


Regards,

Vladimir

thkarner
Partner - Creator III
Partner - Creator III
Author

It´s a virtual machine and I´ll try to find some performance monitoring information regarding disk I/O. In case you have any comparable results when storing big QVDs it would be really intersting if you could share them.

Thanks Thomas

thkarner
Partner - Creator III
Partner - Creator III
Author

Hi Vladimir,

my data model is already optimized (perfect star scheme without duplicate data). Incremental store is not really an option due to some reasons related to the business case. I have to deliver local datamodels each days with full data available in QVDs. Storing ~15 Mio records is also quite quick, but the problems come with the higher data volumes. In case case you have experiences with ~100 Mio records it would be great to share this.

Thanks Thomas