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: 
Tos
Contributor II
Contributor II

Qlik Performance - Database or Script

Hi everyone!

I will explain my situation shortly. We have MariaDB Database with a size about 45 GB. The data is an export of our very old E-commers system, which does not allow a direct connection to qlik sense. Therefore, exported data has the same structure as the E-commers system and is not structured very well. In addition, amount of two tables is very heavy (34GB and 8GB). This means when we extract and load it in qlik, the whole process takes a lot of time. Also about 75% of the data must be processed by mappings, aggregations, joins and the creation of new columns, which also takes a lot of time.

Currently we experiment with column orientated databases, due to the reason that our database data is stored without any relations.

 

And here's the point. I wonder if a good database structure (with already aggregated data tables or extremly fast aggregate queries) can perfom a faster data  processing than extracting to qlik and processing a qvd. My expection would be to load (almost complete processed) data nearly direct into an qliksense app in a fast way.

Does anyone has experience with this approach or does know if it could work (or not work)?

 

Edit:

If i get some time, will create a big view on our MariaDB to load it in qliksense. This shall be done to make a little benchmark and provide it here.

Labels (5)
1 Solution

Accepted Solutions
marcus_sommer

Depending on the cardinality of the fieldvalues the data-size might be in Qlik much smaller especially if you could optimize them, like here descriped: The-Importance-Of-Being-Distinct.

If you mean with "not well structured data" that the data are not (much) normalized that's not a disadvantage else rather a benefit in Qlik.

In regard of a further transforming of the data the most of the them are quite fast especially mappings and only aggregation-loads are executed in single-threading and therefore rather slow (but you may don't need them - just try it without it). As far as you avoids (nested) if-loops and aggr-functions in the UI and used mostly simple expressions like: sum(value) the calculations will be even with large datasets quite fast.

Therefore just try it out.

- Marcus

View solution in original post

4 Replies
tresesco
MVP
MVP

There is no reason why this approach would not work. If you can get the transformation get done in another way (using your db) faster - that's always welcome and that is the way one should follow; performance is what ultimately matters. I have been given a product with some capabilities (read transformation) but that doesn't mean that I can't use other tool that performs better or specialized for. Qlik gives you the capability; use it if you don't have a better performant tool to do it for you.

marcus_sommer

Depending on the cardinality of the fieldvalues the data-size might be in Qlik much smaller especially if you could optimize them, like here descriped: The-Importance-Of-Being-Distinct.

If you mean with "not well structured data" that the data are not (much) normalized that's not a disadvantage else rather a benefit in Qlik.

In regard of a further transforming of the data the most of the them are quite fast especially mappings and only aggregation-loads are executed in single-threading and therefore rather slow (but you may don't need them - just try it without it). As far as you avoids (nested) if-loops and aggr-functions in the UI and used mostly simple expressions like: sum(value) the calculations will be even with large datasets quite fast.

Therefore just try it out.

- Marcus

Tos
Contributor II
Contributor II
Author

Since last week we work on a table construct, which needs to read and connect about 700 million rows. A view on our mysql server needs about 1min 30s to load. 

But, Qlik Sense couldn't read/load the view. I stopped the load script after 6h running. So next i loaded the raw data into qlik and created the table construct within. This approach took about 45min to load.

To this point i would say that working on a database will be faster, but if it comes to qlik it will be faster to load raw data and connect them using QVDs.

 

However, i don't understand why qlik couln't load the already created view in mysql.

marcus_sommer

Qlik doesn't execute any SQL else it just transferred the statement per the specified driver to the database and received then the result. This means it depends on the performance of the database (external queries may have a lower priority and/or running faster into any timeout - if the db provides no error for whatever might happens Qlik will wait forever), the network-performance (there may in the end multiple proxies, security tools/rules between the db and Qlik) and of course the driver (you should try different ones - AFAIK oledb should be more performant as odbc - and maybe this driver has also more capabilities to access also the view).

Beside this I suggest to consider to implement incremental approaches and just to load new/changed records from the db and loading the historical data from the qvd.

- Marcus