4 Replies Latest reply: May 22, 2014 10:57 AM by Torsten Rehder RSS

    Sql from Direct Discovery

      Hello,

      I started to check the DirectDiscovery Function and have a question to improve the by Direct Discovery generated Sql.

       

      Example with two Tables:

       

      Calendar:

      Date,

      Month,

      Year

       

      DirectDiscoveryTable:

      Date

      Implicit

      Value

       

      The generated Sql looks like ' Select ... from ... where Date in (...,..)'

      If the user select three years there are more then 1000 entries in the in-part. It's obvious why the Sql is generated like this, but it's not the fastest query.

       

      I'm looking for Ideas to improve the QlikView Application, so that the Direct Discovery function is able to build better queries.

       

      Thanks for your comments.

        • Re: Sql from Direct Discovery

          Torsten - great question, I have seen apps where the IN clause approaches 10000 entries which is really ugly. I will check with our dev folks, but in terms of general rdbms best practice, what would you suggest as a better mechanism?

          • Re: Sql from Direct Discovery
            Nagaian Krishnamoorthy

            Is there any field other than Date that can be used in IN-clause?

            • Re: Sql from Direct Discovery
              Massimo Grossi

              I think you have a table (fact table) in dbms and one or more tables in qlikview, in your case 2 tables

              can you join the 2 tables in dbms? no, because calendar is a qlikview table; so you can only send a where clause to the dbms for selecting data from fact table; where Date in (.....) is the simpler solution. Perhaps a between would be better but it is more difficult to generate.

               

              Don't know if using a single table (Calendar + DirectDiscoveryTable) in dbms can be useful or if using two tables in dbms  and a view for querying them in Qlikview can be useful.

               

              IMHO if you can stay on Qlikview side (I mean time to reload Qlik doc, hardware to support your big fact table, etc....) don't use direct query.

               

              Another thing that could be usefut (from online help)

              Native data-source syntax

              By design, the DIRECT QUERY statement is data-source neutral for data sources that support SQL. For that reason, the same DIRECT QUERY statement can be used for different SQL databases without change. Direct Discovery generates database-appropriate queries as needed.

              Native data-source syntax can be used when the user knows the database to be queried and wants to exploit database-specific extensions to SQL. Native data-source syntax is supported:

              • As field expressions in DIMENSION and MEASURE clauses
              • As the content of the WHERE clause

              Examples:

              DIRECT QUERY

              DIMENSION Dim1, Dim2

              MEASURE

              NATIVE ('X % Y') AS X_MOD_Y

              FROM TableName

              DIRECT QUERY

              DIMENSION Dim1, Dim2

              MEASURE X, Y

              FROM TableName

              WHERE NATIVE ('EMAIL MATCHES "\*.EDU"')