topic Re: 6 inner joins in QlikView

6 inner joins

pacoli2013 — Tue, 28 Mar 2017 06:54:58 GMT

Hallo,

My colleague has included in his Qlikviewscript a SQL Select statement with 6 Inner Joins.

I have rewritten the script with 6 Mappings.

Loading of my Script is faster and I have after loading still 300,000 records. Of course do I have 2 Files, related/connected by a key.

Loading the script of my colleague is going slower and has after loading more than 7 million records. Of course he has only one file

We have 2 servers: a sql server with the Data WareHouse and a Qlikview server with Qlikview and N-Printing.

I have added both scripts as a worddocument.

My questions are:

1) where are the inner joins executed: my college thinks on the SQL server, I think directly on the Qlikview server.

2) which method is the best if one wants to work with joins.

I have to say: my colleague is a real SQL-developer, working with Qlikview sometimes. I’m a Qlikview designer/developer, working with SQL when needed.

Regards

Court

Re: 6 inner joins

marcus_sommer — Tue, 28 Mar 2017 11:44:56 GMT

Only with these informations it couldn't be said that one approach is better than the other. At first is to check if both methods return the same and correct results by all objects/calculations which are needed - there might be some adjustments necessary to them to reflect the differences within the datamodel.

If both approaches are working in general your decision which one to prefer might consider which approach is more suitable to your further requirements (probably there are more tables included within the datamodel) and which is more handy to you - and of course if the load-runtimes or the UI performance is significantly different and also a real issue you had found your better solution.

Beside them the SQL statement will be executed from the database - qlikview only triggers the execution and received the results. Therefore the speed of the database (especially in considering to it current workload) and also the performance of the odbc-driver and the network-speed will determine how long it will take to return a result. You might get faster results if you only queries your needed fields and not used the "*" wildcard by your mapping-loads.

Another point is the inner joining which not only adds some fields to the other table else it is also a strong filter because only the matchings from both sides remain within the dataset - often this is fully intended (I use this often with extra created tables as a convenient way by complex filtering-requirements to avoid heavy where-conditions). From this point of view I would expect that the join-approach returned lesser records than your mapping-method.

- Marcus

Re: 6 inner joins

pacoli2013 — Tue, 28 Mar 2017 12:34:29 GMT

Hello Marcus,

Thank you for your clear answer. I will discuss it with my colleague what we can do best.

We will check the inner joins

In total our datamodel has 12 files, so one file more should make no difference

regards

Court