SQL View or sql script with etl

[amirmohamed]

Hi all, 

I have two Qlik sense Apps, 1 is for Sales and 2 one is Orders.

app 1 is loading over 15million records , data is  from SQL view = within the Qlik app I have 1 table with all sales data. 

when I open the app on the Hub and view this table it is very fast to load the data. 

app 2 is loading around 14million records, data is from Sql table and within the Qlik data editor I am doing all sort of etls ; joins (left,right,inner, outer), case, sub query = : and I have 1 table in the app that has around 14 columns, but by filtering the data to 200thousand rows , the table takes over 2 minutes to present the data. 

Would you please advise why? 

Is sql query quicker, better perfomance than doing the etls within the app data query?

Many thanks, 

[Somasundaram]

HI,

Try to create the 3 tiers for your applications,

1. Extraction layer- here don't do any ETL  (only Select * from DB) and convert to .QVD file

2. Transformation layer- Here do all the ETL, Business Logic ,Etc,

3. Presentation layer-- Here Using Binay load of Layer 2, create Presentations.

Hope it will help 

[amirmohamed]

Hi ,

Thanks for your reply and suggestion
Would using 3 tiers architecture improve performance?

[Somasundaram]

Yes. And you need to optimize your code as well.. Use QVDs

[jonathandienst]

When you say '1 table' are you referring to the front end or the back end data model? Perhaps either the data model or the expressions in App2 are more complex and compute intensive than App1 - its really hard to say with so little information.

It also makes no difference to Qlik whether you use a SQL view or a complex SQL script. That is more of a database administration question.

Multiple SQL scripts with joins and transformations in Qlik may take longer than doing all the work in SQL, but this would happen in the load script and has nothing whatsoever to do with the front end performance.

[amirmohamed]

1 table i mean in the front end , even thought both app1 and app2 have millions of data but app2 in the front end table takes a very long time to load