7 Replies Latest reply on Aug 31, 2016 11:54 AM by Steven Hawkins

    Teiid embedded query result set supporting upto Integer.max value ?

    Durgadatta Sahu Newbie

      Hi,

       

      When I am querying huge result set in TEIID embedded ,  it is throwing following error,

       

      Error: TEIID31174 Result set or count exceeds the maximum integer value.

      SQLState:  50000

      ErrorCode: 31174

       

      Here I used count(*) query to retrieve all record count.

       

      I am using Teiid embedded version 9.0.1 .

       

      Thanks,

      Durga

        • 1. Re: Teiid embedded query result set supporting upto Integer.max value ?
          Steven Hawkins Master

          Unfortunately count is required to return an int value.  Rather than cap to the max or overflow, an exception is thrown.  Like other databases, a variation on count would be needed, such as COUNT_BIG (Transact-SQL)

           

          Can you log an enhancement request if you need exact counts beyond max integer?

          • 2. Re: Teiid embedded query result set supporting upto Integer.max value ?
            Durgadatta Sahu Newbie

            Thanks Steven,  Here the TEIID performance is very slow, When I am retrieving 2 billion records  (doing some aggregation), its taking time around 2 to 3 hr. 

            Is there any way we can optimize TEIID performance ?

            • 3. Re: Teiid embedded query result set supporting upto Integer.max value ?
              Steven Hawkins Master

              The first thing to look at is the query plan.  Even just fetching the records may be taking a significant amount of time.  Can you post the query plan and how much time does it take to just retrieve that number of records?

              • 4. Re: Teiid embedded query result set supporting upto Integer.max value ?
                Durgadatta Sahu Newbie

                I am using following type query ,

                 

                Select count(emp_name) from EMP_TABLE where  timestamps BETWEEN {timestamps '2016-08-19 10:51:00.0'} AND {timestamps '2016-08-19 22:51:00.0'}

                 

                It is returning 705254568 records ,in  4,429.367 seconds .i.e.73 minutes

                • 5. Re: Teiid embedded query result set supporting upto Integer.max value ?
                  Steven Hawkins Master

                  I'm asking what is the time without the count?  The query plan will help clarify what processing is going on.  For example is EMP_TABLE a view?  What source(s) are you accessing, etc.?

                  • 6. Re: Teiid embedded query result set supporting upto Integer.max value ?
                    Durgadatta Sahu Newbie

                    Hi Steven Sorry for late response , here is the generated query plan for "select * ..." query, and its taking arround 5 to 6hour to retrive 2billions record .

                     

                    For example is EMP_TABLE a view?

                     

                     

                    Its temporary table created using ,

                    metadataFactory

                      .addTable("employee");

                    What source(s) are you accessing, etc.?

                    Accessing Rest source and internally parsing  the json data and populating the record by overriding "public List<?> next() throws TranslatorException, DataNotAvailableException "

                     

                    What is the max limit for number of record we can retrieve using select * query in Teiid embedded-9.0.1 version ?

                     

                     

                     

                    AccessNode

                      + Relational Node ID:0

                      + Output Columns:

                        0: emp_type (long)

                        1: emp_name (string)

                        2: emp_last_name (string)

                        3: emp_loc_add (string)

                        4: dept_name (string)

                        5: emp_address (string)

                        6: emp_address2 (string)

                        7: emp_address3 (string)

                        8: value (long)

                        9: reg_date (timestamp)

                        10: value1 (long)

                        11: value2 (long)

                        12: value3 (long)

                        13: value4 (long)

                      + Statistics:

                        0: Node Output Rows: 256

                        1: Node Next Batch Process Time: 0

                        2: Node Cumulative Next Batch Process Time: 0

                        3: Node Cumulative Process Time: 0

                        4: Node Next Batch Calls: 2

                        5: Node Blocks: 1

                      + Cost Estimates:Estimated Node Cardinality: 20596.4

                      + Query:SELECT testmodel.employee.emp_type, testmodel.employee.emp_name, testmodel.employee.emp_last_name, testmodel.employee.emp_loc_add, testmodel.employee.dept_name, testmodel.employee.emp_address, testmodel.employee.emp_address2, testmodel.employee.emp_address3, testmodel.employee.value, testmodel.employee.reg_date, testmodel.employee.value1, testmodel.employee.value2, testmodel.employee.value3, testmodel.employee.value4 FROM testmodel.employee WHERE (testmodel.employee.emp_name = 'test emp') AND (testmodel.employee.value = 2500) AND (testmodel.employee.reg_date >= {ts'2016-08-17 00:00:00.0'}) AND (testmodel.employee.reg_date <= {ts'2016-08-20 23:59:59.0'})

                      + Model Name:testmodel

                      + Data Bytes Sent:30059

                      + Planning Time:8

                     

                     

                    Thanks,

                    Durga

                    • 7. Re: Teiid embedded query result set supporting upto Integer.max value ?
                      Steven Hawkins Master

                      > Accessing Rest source and internally parsing  the json data and populating the record by overriding "public List<?> next() throws TranslatorException, DataNotAvailableException

                       

                      I'm still not completely clear.  Is EMP_TABLE a Teiid temporary table, or is is it backed by a custom rest source, such as shown with employee above?  In general have you validated the custom translator is behaving as expected - in terms of the number of remote calls it needs to make and the performance of the parsing etc.?  Also is there any transaction that has been started in this scenario?

                       

                      > What is the max limit for number of record we can retrieve using select * query in Teiid embedded-9.0.1 version ?

                       

                      The limit for retrieval is still max int.  Internally we can handle up to max long - [TEIID-3601] support larger row counts - JBoss Issue Tracker