2 Replies Latest reply: Mar 13, 2012 11:35 AM by Kris Balow RSS

    QlikView concatenate vs SQL unions: Which is faster?

    Kris Balow

      The database I am pulling data from has 50 back-end "temp" tables of the same structure of which I need the data from all 50 tables. It's not the most ideal design, but it's what we have to live with!  I would like to understand the load efficiencies and differences between using the QlikView concatenate function to load all 50 tables versus pulling the data in one SQL query by unioning all tables.  I'm trying to squeeze as much time as I can out of the load process, so every second counts. Has anyone completed this analysis or knows which method is more appropriate from the technical point of view? Thanks!

       

        • QlikView concatenate vs SQL unions: Which is faster?
          Henric Cronström

          You just have to test. There is no way of knowing in advance.

          If the database is well inexed and correctly set up, it ought to be a lot faster than QlikView. However, I have also seen UNION statements that are extremely slow...

            • QlikView concatenate vs SQL unions: Which is faster?
              Ralf Becher

              Hi,

               

              the result of your test would be interesting.

               

              I presume the database union could be faster because the SQL select statement is prepared and parsed once and there is no further iteration on the QV site.

               

              On the other hand, it could be dependend from the database behavior. In Oracle for instance the whole query resultset gets cached in TEMP tablespace before passed to the client. This could lead into trouble (full table space) or bad performance in case of big amount of records. So, it could be better to "chunk" the data in single queries.

               

              - Ralf