Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ...!?
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.
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
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
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