SQL vs. QlikView for Complex Loads

[Nicole-Smith]

Hi Folks,

I've just started a new job, and the load script is written in a completely different manner than it was at my old job.

At my old job, we would have very simple SQL select statements, and would allow QV to do any transformations, joins, calculations, etc.:

Table:

SQL Select * FROM SQLTable;

Left JOIN (Table)

SQL Select * FROM SQLTable2;

At my new job, they have very complex SQL select statements, and do everything in SQL.  QlikView is stictly used just for displaying the data (not for doing any transformations, joins, calculations, etc. on the data itself):

Table:

SQL Select * FROM SQLTable s

LEFT JOIN SQLTable2 s2 on s.Id = s2.Id;

My examples above are much simpler than anything that I've worked with, but it should at least give you an idea of what I'm talking about.

Is one way better performance wise than the other?  Are there any concerns with doing it either way?  I'd just like to get the community's thoughts.

Thank you!

[Anonymous · Accepted solution]

Nicole,

First - congratulations with new job!

Next, I expect that join in SQL is faster than join in QV.  You can easily test this to prove me wrong.  At the same time, no matter how complex SQL's I use, there is always a lot of work left for transformations in the QV script itself...

Regards,

Michael

[maxgro]

I think  that you can do almost everything in the sql (query or procedural language for very complex needs) or in qlikview; just 2 suggestions 

- knowledge of the tool (I remember your answers in the forum, you know very well qlik)

- relation between data processed (number of record) and number of records needed in qlikview; an example: processing on sql 1000000 of records and group by to get 10 records transferred in qlikview is very different from transfer all these records to qlik and group by in qlik

-and of course, try not to mix

[jaimeaguilar]

Hi,

I agree that transformations may run faster in SQL than in QV, however I think that the answer of which approach is better is It depends. If you already have, for example, an environment with a datawarehouse and a DBA who can

create queries, store procedures, custom tables, views etc, then take that into advantage and focus QV in visualization. On the other hand, if you're working with plenty XLS docs, several descentralized databases, then probably the best way would be to use QlikView for the whole ETL process, and just like Massimo Grossi said, whatever approach you use, try no to mix them,

regards

[Nicole-Smith]

Thank you all!