4 Replies Latest reply on Jan 22, 2018 5:00 PM by thehanibal

    Dynamic "WHERE" for every query

    thehanibal

      Hello,

       

      I'm new around here and using teiid so I hope is the correct place to ask this.

      I'm using:

      • Teiid 9.0.6 with Wildfly (Which I guess is WildFly 9.0.2)
      • Red Hat JBoss Developer Studio 11.0.0 GA
      • Teiid Designer 11.1.0
      • Windows 2012 R2

       

      I have a project where I'm making a VDB with 5 tables, each one getting information from several tables from 2 diferent sources (MSSQL/PostgreSQL).

       

      Now my main problem is that the server I have (and can't replace) has 8 cores and 16GB RAM that Teiid can use, and this seems not be enough for all the data I'm querying, most of my view's queries are like:

      SELECT name, lastname FROM Source1.Table1
      UNION
      SELECT name, lastname FROM Source1.Table2
      ...

       

      I can't use limit because that just "cuts" the data and somehow makes Teiid incredible slow to answer so what I'm guessing and asking how can I make the queries I'm using in the views "dynamics" so I can turn the above query in something like:

      SELECT name, lastname FROM Source1.Table1 WHERE name = 'Variable'
      UNION
      SELECT name, lastname FROM Source1.Table2 WHERE name = 'Variable'
      ...

       

      In where "Variable" is a value I send from the query I make to the VDB, so then if I query the VDB with

      SELECT * FROM Persons WHERE name = 'Anibal'

       

      I get the queries in the views turn:

      SELECT name, lastname FROM Source1.Table1 WHERE name = 'Anibal'
      UNION
      SELECT name, lastname FROM Source1.Table2 WHERE name = 'Anibal'
      ...

       

      And then return the data, is that even possible? How can I achive this?

       

      Thank you.

        • 1. Re: Dynamic "WHERE" for every query
          rareddy

          First of all Welcome to Teiid. We are thrilled to have using Teiid. Absolutely this is the right place to ask. It is little slow as most of us vacation during holidays.

           

          What you have system wise is enough. Are seeing slowness. BTW, Teiid already behaves exactly as mentioned in your question already.

           

          Use the union without the where as transformation query for your view and build the VDB. Then when you issue the user query then use the where clause then Teiid will rewrite query as second form you showed automatically. You can also see a query plan on this gets done. Check the documentation for details. Ask any questions you may have.

           

          Again Welcome to Teiid forums.

           

          Ramesh..

          • 2. Re: Dynamic "WHERE" for every query
            thehanibal

            Ok, somehow I cloud show the logs where the planning query is and now I see that it is rebuild as I intend (just as you said), now is there a way I can see the performance for each query I run to my VDB?

            • 3. Re: Dynamic "WHERE" for every query
              rareddy

              Programmatically you can do this SET Statement · Teiid Documentation  to get query plan

               

              Or you can Designer and it has menu options to see the query plan.

               

              or using web console you can you can enable trace logging

               

              or you execute "SET SHOWPLAN DEBUG" on using SQL Client like Squirrel and that will spit out the query plan to log file.

              • 4. Re: Dynamic "WHERE" for every query
                thehanibal

                Hi Ramesh,

                 

                I found that the main problem that is causing the excess RAM usage was that the connection to one of the data sources was lost and the query was restarted again and again.

                This data source is after a VPN connection, for now solve it by bringing the data through a direct connection and not by VPN.

                 

                Thanks a lot.