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

[durgadatta]

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

[shawkins]

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?

[durgadatta]

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 ?

[shawkins]

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?

[durgadatta]

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

[shawkins]

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.?

[durgadatta]

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

[shawkins]

> 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